Posts

Clean Data in FileMaker

Achieving clean data in a FileMaker database is important

This is something I’ve bee dealing with for years. It hit me like a large hammer in the chest when one of my users replaced the contents of one field in about 5000 records with HTML she copied from the web. This was done accidentally, and clearly she felt so bad that she didn’t even tell me. This happened about 9 years ago.

I realized I had to put in some measures so this won’t happen EVER again. Now, you can think of every possible measure you can take and users can still surprise you. It’s similar to spam online. We keep getting smarter at how to deflect spam but spammers are always a step ahead.

Below are some things you can put in place to make sure your (their) data is as clean as it possibly can be.

Custom Menus

  1. Create a custom menu (or more) for certain situations (Exhibit A)
    1. One menu for everyday use (and different menus for different levels of users);
    2. Another menu for when printing;
    3. Another menu for a layout that handles sensitive data.
  2. On every custom menu
    1. Remove “Replace Field Contents” so users cannot accidentally replace records in fields
    2. Replace “Paste” with a script you write. The script should use Paste with remove style checked. (Exhibit B)

When you create a custom menu name it appropriately so you know what it is for.

Clean Your Field

  1. If you’re dealing with phone numbers you might want to format the phone number. You might even want to reject data that is not entered properly. So, e.g. phone numbers are 10 digits in the US, zip codes are always 5. So, the phone number field you can format to accept numbers and dashes only. The Filter function is great for this. The zip codes you need to lock down to allow 5 digits only (if the country is US) and no other characters but numbers.
  2. Use a custom function (or write it in a script but the custom function (Exhibit C) is easier) to strip off anything you don’t want, such as additional space,  carriage returns and formatting (Exhibit D). Users have the tendency to hit enter after filling out a field. This is probably just a habit they picked up when working with Excel.
  3. When you’re generating reports, badly formatted data can drop off or look really strange (e.g. large letters in red). So whether you format your field nicely to Helvetica 10 on a report, if the data is messed up in the fields you’re not getting the proper result. The best thing? You’ll only know about this months down the line when the user complains that the report looks messed up.

 

03-27-2015-exhibit-a

Exhibit A

03-27-2015-exhibit-b

Exhibit B

03-27-2015-exhibit-c

Exhibit C

03-27-2015-exhibit-d

Exhibit D

 

 

 

 

 

 

 

 

 

Exhibit D shows Ray Cologon’s Trim 4 Custom function.

Just to recap, clean data in FileMaker is as important as having a nice structure or a slick interface, if not more important, since we actually build the database for the users, not ourselves.

FileMaker Server-side Scripting

Running scripts from FileMaker Server can dramatically reduce execution time. I have been an avid fan of server-side scripting since it was introduced in FileMaker 9. Now, with the introduction of FileMaker Go it is more important to take the burden away from the client and do as much processing on the server as possible. There’s no need to bog down a client when the server can take care of the task in the fraction of the time it takes to run from client. You can use server side scripting to automate record updates from external sources, prepare those pesky Monday morning reports so they are printed by the time everyone comes in or simply use it to update stored data instead of using calculations.

You can run FileMaker scripts or System Level Scripts or even combine them to run a script sequence now! This article deals with FileMaker scripting.

We tend to think, now that we have this feature, we can just build our scripts, run them from server and be done. This isn’t quite true…we have to follow up with the all-important troubleshooting period. At least in my experience, there is no server-proof script that can be installed on a server without further testing and digging and modifications.

So, I collected some pointers from my–and fellow FileMaker developers’–experience that might help you speed up your development when running scripts from server. Don’t be discouraged. You will be happy with the results. Jump in and put some of those tedious scripts on the server, so you and your users can forget about them.

Things to know:

  • Make sure all your script steps are server compatible (choose “Server” under “Show Compatibility”–bottom left corner of “Manage Scripts”).
  • The server runs the opening and closing routines, so you might want to revise your “onOpen” and “onClose” script to bypass some steps that are not relevant to the server.*
  • Globals are set. This may not be an issue, but might be worth paying attention to.
  • No need to script opening a new window, because the server does not execute the scripts from physical windows, but rather a virtual space.
  • If you are referring to an external file in Manage/External Data Sources, folder names have to be hard-coded in the string (such as file:/folder name/folder name/file name.fp7 or filewin:/ if you are on Windows Server).
  • Import locations can only be either:
    • the Documents folder within the Data folder under the FileMaker Server root on your server machine (you can use Get ( DocumentsPath) to get you the right path; Windows users, don’t forget to use the “filewin” prefix);
    • the Temp folder (you can use Get ( TemporaryPath) and see the notes above).
  • Watch the log after you set your script to run, it can only help you!
  • Set up notification emails so you can be aware if something went wrong (you can turn these off after awhile).
  • Errors reported are FileMaker errors, not server errors.

Some caveats:

  • Lack of instant feedback. Even though you test until death locally, new problems can arise when running from server. Put in error checking after every step. I would also highly recommend logging script times.
  • You have to disconnect the client that runs the server script if a script hangs.
  • The script will hang if you forget to put in the “Perform Find” script step (possibly other actions or lack of them can hang the script, as well).

That’s it for now, but I will be adding to this article, so feel free to check back on occasion.

* I usually set up an account called “fmserver” to run the scheduled scripts with. Then I edit my onOpen script to bypass the opening routine if the account is fmserver. That can cut down on time and avoid unnecessary script steps.

Credit: Steven Blackwell, Todd Geist