Experiences of a FileMaker Pro Database Developer

FileMaker Server, Please Abort Cancelled Finds!

I’m usually not one that likes to rant or get on a soapbox. I find this unproductive and I’m not really sure it does much to contribute, plus I feel that most readers don’t like a complainer. However, there is one topic that I’m in a uproar over.

Whenever a user cancels a find using FileMaker Pro Client in a FileMaker Server hosted environment, FileMaker Server does not cancel and abort the find. No matter how long it takes, FileMaker Server is going to complete the find. Even if the client force quits or gets disconnected from the FileMaker Server. You cannot even use the FileMaker Server Admin Tool to “force” close the offending client. If you do, or if the client has force quitted FileMaker Pro client, they will still be visible under the Admin Tool under clients until the find is complete.

What makes this worse is how FileMaker Server works with multiprocessors, especially in a Windows Server environment (note: I have not tested this much on a Mac Server). FileMaker Server hands off specific tasks within a table to a free core. For example, if you have single processor dual core machine and you perform a find in table A, FileMaker Server is going to utilize one of the cores until it finishes the task. And, any other find request that comes in on Table A will be queued until the first Find is complete. It will not pass over the second request to the other core, even if it’s free. However, a different task or a Find taking place in a different table will utilize the second core.

So, if a user performs an unindexed find in Table A, they are going to bog down that entire table until the find is complete. When you are talking millions of records and Table A is a busy table with all users working in it and performing searches, this can have drastic results. Daily, I use a high-end server with 4 dual-core processors (8 cores), and I’ve flatlined (maxed out) one processor core with an unindexed find on a heavily used table and I brought the system to a grinding halt. I was using less than 5% of the processing power and the other 7 cores were free and ready to work.

I was doing a search in order to perform some maintenance routines on a set of records. Once I hit find, I knew I was in trouble. It was a mistake and I wanted to constrain the find on a smaller set of records, but I hit perform find instead. There was no way to cancel. I hung up the entire company. Two hours later, we were back up. Not fun!

Post to Twitter

Helping Our Soldiers Quit Smoking, Part 2

Recommendations for Resources: Where to start with FileMaker Pro

This is part 2 of a case study that will follow the development of a FileMaker database by a military psychiatrist to assist with his smoking cessation program for his Marines & Sailors in Afghanistan.

FileMaker Inc. has some great resources directly within FileMaker Pro and online at FileMaker.com. Before you get started, it’s important to have an understanding of some basic database and relational concepts. Start here and read all 3 parts.

This will give you basic concepts of relational databases. You’ll need to understand this before we can dive into FileMaker. After you read about relational databases, open up FileMaker Pro 11 and click on Help from the menu, then on Product Documentation and select Tutorial. This will provide a good read on how to use FileMaker Pro as well as hands on lessons. You can also access this from the Quick Start Screen or online directly here:

Also from the Quick Start Screen under Video Tutorials, click “Visit the Resource Center” or click this link:

There are some videos there that will help explain some of the features of FileMaker Pro as well as other video tutorials. You can also review starter solutions and access the tutorial and other resources under the Learning Library tab.

FileMaker offers intensive training and you can choose self-paced or instructor-led training. Information about FileMaker’s Training Series (FTS), finding a course that is right for you and how to order the manual that is used for training, can be found here:

Once you get through the above resources, check my Favorites for third party FileMaker Pro books and other resources. I will be updating my favorites in the next few days and listing some additional getting started resources and books, so please check often for updates.

Another link that I find helpful, is FileMaker’s Webinars. These are Web Seminars conducted by FileMaker Experts, other developers like myself as well as engineers and presenters from FileMaker, Inc. Click here for a current list:

Update

After posting part 1, I was contacted by Kevin Mallon, FileMaker’s Sr. Public Relations Manager:

Kevin Mallon: We caught your blog about the military psychiatrist creating a database to assist with smoking cessation among the marines in Afghanistan and I wanted to know what you think we could do to help.

We could provide our FTS along with third party FileMaker Pro “how-to” books.

I was also wondering if he could use FileMaker Go running on an iPad?

Look forward to hearing from you.

I forwarded the email to Commander Baker and also asked him what computer equipment he was currently using. FileMaker Go would also be a great addition, especially if going paperless (more on this later). Here’s Commander Baker’s reply:

Commander Baker: Gentlemen:

Greetings from Afghanistan. Wow, I am overwhelmed from the great support. In terms of equipment, I have been an Apple user for the last 2 years. I have a Mac Book Pro and I used the iPad for my daily operational, administrative and medical practice. By having the information and database in the iPad definetely sounds like a superb idea.

Kevin, I would love to have a book or training material so I can learn FileMaker. I am kind of a visual/kinesthetic learning so a lot of pictures help my learning. I have FileMaker Go on my iPad but I have not used it yet as I didn’t have any database at this time.

Matt & Kevin, you both have been heaven sent angels and I greatly appreciate your support. As my mother always says, there are indeed angels among us.

Thanks guys

Part 3, Designing the Database

Back to Part 1

Post to Twitter

Helping our Soldiers Quit Smoking, Part 1

*This is part 1 of a case study that will follow how we get organized and setup a relational database with FileMaker Pro.

Today was an interesting day and one that makes me a bit more proud to be an American. Although I have relatives and friends that serve in our armed forces, at times I, and we all do, tend to take them for granted. We forget that they are defending our great country.

I started my day as usual by reading email. I received an inquiry asking for help with FileMaker and how to get started setting up a database. This request was from a Commander in our Navy, CDR Alfred “Devil Doc” Baker, MD, MC, USN. Below is our email transcript. I have asked CDR Baker to share this as a case study in order that it might help others when they begin a database. To me, this is more than just a case study, it reminds me how I first started but more, it enforces why I love developing and why I use FileMaker Pro. And, maybe in some small way, I can do something to help our soldiers.

CDR Baker: Greetings from Afghanistan.  My name is CDR Alfredo Baker.  I am presently deployed to combat operations to Afghanistan with the Regimental Combat Team 2 as a psychiatrist.  I recently purchased FileMaker Pro 11 because I wanted to learn how to use a database for first time and this software was highly recommended especially to those using a Mac.  I have a question.  I recently started a Tobacco Smoking Cessation Program for my Marines and Sailors and I would like to gather not only their information (no personal ID) but their amount of tobacco used, type, years, quitting times, etc., so I follow their progress and probably gather enough data to publish my findings later on.  Where do I start?  Could you be kind in guiding me on the basics steps in building a database and relational tables? Thanks.

 

Matt:  I’d be honored to help anyway I can. Let me formulate an outline along with a few questions and I will email you again later today or by tomorrow. I will even put together a starter file for you in the next few days in order to help get you going.

Would you mind if I make this a case study on my blog, FMPro Database? It might help others like you that are beginning a database for the first time as well. However, I understand the privacy if you’d prefer not to.

One question before I get started, if you are not going to store a personal ID, how are you going to keep track of your Marines and Sailors? Just by their name? If you can send me a list of things (fields) that you want to track both for the personnel data (First Name, Last Name, DOB, etc.) and the tobacco data (type, amount, year started, total years using, how many times try quitting, etc.), I will use these fields in my starter file.

Thank you for your duty and service to our country. All that you and your men do is appreciated beyond what words can express.

 

CDR Baker:  Greatly appreciate you words of support.  The attached document have the list of the fields I am using as part of the registration to the tobacco cessation program but also as to the determine their specific treatment based on the amount of tobacco consumed.  I can use their last 4 of their social as ID or I can assign a random number as their ID that I will give each of them.  These kids are smoking so much and some of them are dipping as well (40%).  In addition, their spouses are also smokers with little kids at home.  This is a way for them to deal with the every day danger they are exposed to. The good news is that I am having over 90% success rate although it is still in early phase but these Marines and Sailors are determined to stop.  These young men are indeed working hard to preserve freedom and they do it with a smile int their face.  That is one of the many reasons I enjoy taking care of them besides that I am serving this wonderful country of ours.  That is the reason we deploy overseas so nobody will come to our country and tell us how to live our lives or take our freedoms away, I will be fighting to death before that happens.

Again, thanks for your kindness.  I downloaded their user manual and it was not as helpful in explaining how to set it up.  Do you have any recommendation in terms of something that I can read to better understand this software?  I don’t mind at all if you use my project as to teach others how to set up a database in this situation. Thanks.

Very respectfully,

CDR Alfredo “Devil Doc” Baker, MD, MC, USN
(FS/FMFQO/SWMDO)
Operational Stress Control and Readiness Team Psychiatrist
Regimental Combat Team 8
Delaram, Afghanistan
“With My Marines and Sailors, to the Gates of Hell and back, anytime, anywhere…” Iraq, circa March 2003

In my next post, Part 2, I will answer CDR Baker’s request for recommendations that will help better understand how to use FileMaker Pro. I will also share my outline and questions.

Part 2, Recommendations for Resources: Where to start with FileMaker Pro

Post to Twitter

Import Fails on FileMaker Server

Yep, it got me last night. I wrote a script to import data into a reporting system (post coming soon on optimizing reporting). It worked fine on FileMaker Pro client, but failed when I implemented the script to run server-side. I thought I would add the routine to the nightly updates so any changed or new entries would automatically get updated into the Report System overnight. It’s all part of optimizing reporting so the user doesn’t have to wait for large updates to run when generating a report, only changed or new data.

So, why did it fail? FileMaker Server can’t import from one table to another when the files are hosted. Bummer and I forgot this. I’ll be re-writing the script to follow an export and import routine. Steven Blackwell and Wim Decorte wrote a great post on Server Side Import and Exports that I’ll be sure to follow. It helps clarify what can and can’t be accomplished. For the knowledge base article on the subject, check out ID 7035.

 

Post to Twitter

Know Who Your Users Are

Learn from the end users

I can’t stress how important it is to know who your users are and what they do. When developing custom apps, regardless what the underlying programming is, it’s so important to know how the user is going to use what you build. It’s even more important to clearly understand what the user does and how they do their job BEFORE you start planning your project.

I’m starting my last 2 module re-writes of a long term project and I really enjoy the information gathering and initial interface design aspect of programming. Gathering information allows me to get down into the trenches and observe and interact with the end users. How are they doing their job? Let me try? I get to ask a bunch of questions and let them complain to me about their day-to-day tasks. In a way it’s like I’m a therapist. I’m here to make their job easier and to help eliminate those annoyances.

At the same time, I get to think about how they might use a new feature or do their job a little different, more efficient. This is when I begin exploring the interface design and how the user will interact with the input tools. Is it clean, clear and easy to use? Can they figure out how to use it with little or no documentation?

With this comes change. I don’t know about you, but I don’t even like change. Just keep this in mind when you go to implement the new solution. Learn how to manage the resistance to change. When you are in the trenches, let the users know that change is coming. If they are part of this change, there is less resistance. Above all, make sure to train. If you’ve done your job right, once the user is shown how to use the new solution, they will see the improvements. Sometimes it takes them time to get used to the newness and repeated training sessions may also be necessary.

Post to Twitter

Loopy Windows

This might be obvious to many, but sometimes the obvious just isn’t so obvious especially if you are deep in your project.
I have a list of records that have 12 fields that I needed to split into 12 separate records within the same table. I wrote a script to loop through the original records and create 12 new records for each one of the originals.
I spawned a new window to create and dump the new records into, so I could loop through the original set of records. This worked fine in my testing, but for some reason I must have “found” the records for my test. I just didn’t realize I did.
If the original window is showing all records, any records you create in the newly spawned window will update the original window. I guess this makes sense since you are showing all. It just took me a while to figure out that this was the problem. The result, in my case, was an infinite loop.
Lesson learned: If you want to run a looping script on all records in a table, and this looping script creates new records in same table, MAKE SURE YOU DO A FIND FIRST. Even if that find is suppose to find all. Don’t use Show All.

Post to Twitter

Preparing to Plant – Part 2

Organic Gardening

Organic Gardens by Gordon (my father's strawberries)

After the initial gathering, the first thing I do is jot down all the pieces to what I think will be in the system. For a project like organic farming, and learning what Donna wants to track, these are the pieces that I came up with, in no specific order. Some of these will be the modules or primary sections of the database.

  1. Contacts – this is where every person and company is maintained. Anyone that has anything to do with the farm will be entered here, from customers to employees to vendors, etc. All the pertinent information needed to contact the person or company as well as describing who they and what they do and offer is recorded here.
  2. Costing – this is where all costs are tracked, everything from seeds to labor. There will be a hard goods section as well as a labor/hourly section.
  3. Fields – this will identify the field and will track preparation, crops, rotation, weed/pest control, fertilization, harvest and yield. This section will be linked to costing and sales in order to determine pricing, costs and profits for each field. This section will also be linked to Plants/Crops in order to pull in information about the plants being grown in specific fields. The plant information will be used to determining growing season, fertilizer, weed/pest control, etc.
  4. Plants/Crops – this is were all the information for each of your plants is stored. Here you will define the plant, where to purchase, how to grow, how to maintain, etc. This will also be tied into costing and sales to determine the cost and profitability of each type of plant/crop. To be used in sales, the market and use options will be stored here as well.
  5. Sales – this is where all sales activity is tracked, everything from who to sell to, how to sell and where to sell. This section will keep track of your orders, invoicing and delivery.
  6. Budgeting – this is where all the planning, forecasting and pricing takes place. We will develop something similar to the Excel examples you provided here.

Those are the main sections as I see them at this time. I know I’ve been delayed in getting this off the ground, but hope this information is helpful. Donna, let me know if I am missing any of the main sections.

Next, I’ll dive deeper into each section and define some the items and fields that I think are important.

This is part 2 of a multipart series, see Getting Down and Dirty – Part 1 for the previous post.

Post to Twitter

Creating QR Codes with FileMaker

FMP QR Code

QR Code

This QR Code generator developed in FileMaker Pro is free and unlocked so it can be used in your own solution. The QR Code is still popular today, especially when used with smart phones to quickly launch a website for more information. The website could be anything from more information about the product you are looking at, the daily lunch specials at the restaurant you just passed or more information on a house for sale that you saw driving by.

Using Google’s Chart API for QR Codes, Custom Functions and the ScriptMaster plug-in by 360Works, I created the FileMaker Pro QR Code Creator. There are 2 options for displaying and creating the QR Code:

  1. Create QR Codes in Web Viewer
  2. Store a QR Code as a PNG file in a container field

Each option uses a different custom function. I was hoping to generate the QR Codes with a single CF, but the Web Viewer’s automatic encoding makes it basically impossible.

UPDATE 4/30/2011: I just updated the file so the plug-in function is now loaded within the custom function instead of having to call a separate script. All you need to do to make this work in your own solution is to install the ScriptMaker plug-in and to import my “QR_Code_Container” Custom Function from my example file below.

UPDATE 5/16/2011: Thanks to Daniel, he discovered that it’s required that my sample file is open before you import the Custom Function. If my sample file is not open when you import, the Custom Function will be commented out and missing the ScriptMaster GetURLAsContainerpart2 function.

UPDATE 1/21/2013: Google seems to be changing the API for QR Codes. I updated the link above, but it looks like QR Code generation may no longer be supported after 2015. The file still works as expected, but I am not sure how Google’s future changes may impact the function of the file. I will try to dig into this and if anyone else has further information, please let me know.

UPDATE 3/7/2013: Thanks to Bill, he helped me discover that I needed to clarify that not only is it required to open my sample file, but you MUST create a QR Code in my sample file first, before you import the Custom Functions. Creating a QR Code, or just changing one of the fields that is used to generate the code, causes the Custom Functions to load. If they do not evoke the Custom Function first, you will get  ( $url & $dim & $data & $encoding & $error & $margin ), instead of GetURLAsContainerpart2( $url & $dim & $data & $encoding & $error & $margin ).

UPDATE 3/16/2015: You can also try the QR Code Generator using the KAYWA API if you are having trouble with the Google API or want to see another solution. Check it out here.

You can download the fully opened, unlocked example file here:

QR_Code_v1.1

If you have any comments, questions, suggestions or problems with the file, please post a comment. Although I have done the best I can to create a file that works, is bug free and generates a code that is expected, use the file at your own risk and there is no implied warranty protecting data loss or the accuracy of the code generated.

Post to Twitter

Getting Down and Dirty – Part 1

Tractor

The old work horse of the farm

Donna Long from the Ponca Hills area just north of Omaha, Nebraska contacted me by posting a comment on my company website about using FileMaker Pro to help run their small organic farm operation. I thought this project would be a great case study on what’s involved in developing software using FileMaker Pro.

I plan to provide guidance and help Donna get started building her database. Together we are going to follow the steps necessary to build a database system. This is the first post of a series that will be published under Case Studies. Here you can follow the information gathering process, planning the database, initial builds, feedback, questions and answers and final deployment. My goal is to guide Donna as she does the work, but who knows where this will go and evolve. I welcome comments, feedback and suggestions as we take this journey. Let’s get started!

The first step is gathering information. In my initial interview with Donna you’ll see it’s important to take a genuine interest which helps to understand the project and, more important, the people behind the project. To me, the best part of any new project is getting to learn about a new business.

MATT: If you can tell me more about your farm and exactly what you need, that will be the first step. Are you using anything right now, Excel, Word, other? What kind of timeline are you on? I’ll do my best to help you get started, but it may take me a little while to carve out some time.
Where are you located? I love gardening and grew up with a father that always gardened and spent his last job as the Horticulturist for Winter Park, FL. I live in Connecticut and can’t wait for Spring and for the feet of snow and ice to melt!

DONNA: We’re in Nebraska, a little north of Omaha in the Ponca Hills area.
No, I have merely used pen and notebook so far.  We planted our first crop last fall….2000 garlic cloves….asleep under the snow for now, I’m hoping.  I will start some seeds indoors soon, and when Spring arrives will plant more asparagus, blackberries, blueberries, raspberries, strawberries, and annual vegetables.
We’ve established an early relationship with some restaurant owners and hope to entice Whole Foods and Trader Joe’s to sell our products.  And of course we’ll make the rounds of the Farmers Markets.  Buying “local” has just begun to take off here, and the same is true for the response to organic produce.  Our intention is to work toward certification, so we’ll be using only natural pest and weed control, and fertilization.
I’d like to track our costs, field preparation, planting information(including location to facilitate crop rotation), maintenance of plants(including weed/pest control), and harvest/yield information. Then on to sales information, including where sold and price.
All through this I think we should try to track our hours of labor, as well as ‘diary’ what that labor entailed.  So, maybe that’s leaning towards a “calendar approach”….I don’t know, just kind of free-thinking as I go here.  There may be something I left out…it’s late and I’ve been studying FMP11 all day….probably have a little ‘brain freeze’….
I’ve lived on this 10 acre plot for 19 years and have spent most of that time flower gardening with minimal amounts of vegetable and fruit efforts.  I’ve worked as a nurse anesthetist for over 40 years and would like to spend more time soaking up the smell of dirt!  So this will be a retirement pursuit for me and I’m as excited as when I saw my first radish seed sprout in Plant City Fla. 63 years ago.
Are you a native Floridian?  Winter Park is my favorite city in Florida….not least because of all the beautiful landscapes there.  I was born in Lakeland and my 91 year old mom still lives there.  My grandparents had a flower nursery in Plant City and some of my grandfather’s hardscapes are still being enjoyed around the town.

MATT: Pen and notebook is great. This means you already have a lot of thought and a system in place. The computer is just another tool to help with this system. Can you scan or fax me some pages from your notebook so I can see some actual data?
I’m originally from California and moved to the deep south when I was a teenager. My Dad’s sister married an Alabama southern family. We ended up moving across country, but I really enjoyed the freedom and hunting and fishing. I still love to fish, but haven’t hunted in years. I spent my summers with my uncle robbing bees. He was a school teacher and had about 10 bee yards, so we had all summer to get the honey.
I then ended up in Mississippi and finally in Orlando Florida through a Disney College Program. My parents later followed me to Florida and my Mom ran an Estate Sale Business and my Dad worked for the town of Winter Park.
I met my wife in Florida who was a native New Yorker and that’s how I ended up in New York and settled in Connecticut.

DONNA: Sorry I haven’t gotten back to you sooner.  Have been at a sustainable farming conference, working on logo and website, and planting seeds indoors.  Also work has been unusually busy so that gets first priority.
Thanks for hanging in with me.  I’m at work now but will write more later and I do have some additional info from the conference.
Question, can Filemaker take the place of excel for tracking expenses and income?

MATT: I look forward to more information…
Explain what you mean by tracking expenses and income? I need examples. Have you looked at Quickbooks for this?

DONNA: These are two resources from Roger Wilson at UNL that are put together using excel to track costs and income.  I would like to be able to use FileMaker for this information as well as other data we’ve discussed.
Agricultural Economics
Crop Budgets
Guess my question is basically can filemaker work sort of like a spreadsheet as well?
Thanks for the timely response today.  I really appreciate your interest.  By expenses, I’m talking about the costs of the crop, and income of course means from the sales of the crops.  This Mr. Wilson has put it together so you can actually determine what price to set for your produce.

MATT: Great information and yes FileMaker Pro can work similar to a spreadsheet even with a spreadsheet view.
Are you looking for FileMaker to actually determine what price to set for your produce? If so, I see that more in a phase 2 or 3 of the project. We want to get the basics in place and working before tackling those kinds of calculations.

I have enough information now to put together an outline and rough schema of the database modules/areas. I’m sure I’ll have more questions as I put that together. This will be the subject of my next post.

Post to Twitter

Annoying Windows Flash

Ever notice when a record is refreshed there is an annoying flash when using FileMaker in Windows? The Refresh Window command only refreshes data. It does not redraw the screen. However, under Windows, whenever the data is refreshed an annoying flash occurs, especially when the window is not maximized. There are many situations where we need the data refreshed and updated, but there has been no way to prevent the flashing.

Good news, with FileMaker 11 the Freeze Window script step updates the screen without the flashing. Just add a Freeze Window script step whenever you need data refreshed within your scripting. As long as you do not need to Flush cached join results or Flush cached SQL data, then use Freeze Window instead of Refresh Window.

When a script ends that has a Freeze Window step in it, and a Refresh Window step was not used after the Freeze Window, FileMaker Pro automatically refreshes the screen. I find this quite interesting that this particular refresh window will not cause the window flashing. Here’s hoping that FileMaker will fix the flashing caused by the Refresh Window command in the near future.

Special thanks to Darren Terry, Jason DeLooze and Howard Schlossberg for helping me clarify, test and sort this out.

Post to Twitter