FileMaker Fever:

Icon_script_triggers_lg

If you are using FileMaker, upgrade to FileMaker 10 if you haven't already. Now, what are all the things you've always thought you couldn't do and had to live with? Time to start removing obstacles and adding power to your database with Script Triggers.

Keep reading, there's a nice unlocked sample file at the end this story.

The genie is out of the bottle. Now you don't have to make your users do all the work to make things happen in your database. For example, if you need a script to assemble the perfect up-to-date value list, you can have it. If users have training issues and tend to enter the wrong data sometimes, you can trap those things on-the-fly and then train your users and clean up after them.

Maybe we are lucky that we didn't get Script Triggers earlier. FileMaker databases tend to be built with strong fundamentals. Developers haven't been enraptured making the database do fancy back flips - because it hasn't been possible till now. What's cool is that script triggers are so full-featured that there is a lot you can do with them without writing long scripts. Short scripts work wonders.

Here's where gold mining comes in. We just recently had the FileMaker Developer's Conference. Hundreds of FileMaker developers got religion at the conference and what we learned is that script triggers don't have to be difficult. They can be easy and make our lives and our users' lives easier.

Everyone is out there learning tricks and shortcuts that weren't possible till now. Gold mining is what you do to see what your fellow developers are telling and selling. There's more blogging than ever. Twitter has gotten an active group of FileMaker developers tweeting now. If you are lucky some of our top FileMaker developers will start selling us their tricks piecemeal.

Example files are being shared all over the net. My example is simple and easy. Maybe it will be the one that either fixes a problem you have with your database or gets you excited about learning more about Script Triggers. So here goes.

Timesheet Date Script Trigger. I have a Timesheet Entry Screen in my Studio Manager product. There is a timesheet for each person for each day. When you create a new timesheet, it fills in your employee number and today's date. Then it is up to you to enter line items for the work you've done entering job, billing category and time expended. It does the job well and quickly.

Except, every now and then a user will realize a little late that he wants the timesheet date to be yesterday not today. So he or she changes the date. Since every line item copies the timesheet date into itself automatically as it is created, if my user has entered 3 line items for 9/15 and then changes the timesheet date to 9/14, we've got an inconsistency that will show up in weirdness in time reports. I've used calculated field messages that show up on the line items to tell my user that he's screwed things up. But it is clunky. Script triggers give me a better and simple option.

Put a Script Trigger on the Timesheet Date field. Select the field. Choose Format/Set Script Triggers... (or Cmd-double-click the object). Check the OnObjectExit trigger and leave the Browse mode checkbox checked. Select a script that is near where you would like to put your new script for the Timesheet Date field.

Right now since I don't know how many of these script trigger scripts I'm going to have, I create a script folder called something like "TS Script Triggers". The TS is the Table abbreviation for Timesheets.

Here's my script. I'll explain it below. View it full size by clicking it.

What the script runs when my user exits the timesheet date field: Are there any line items? If so, does the timesheet date just exitted match the date in the first line item? No? Then throw up a dialog for the user and ask him how he wants to resolve the discrepancy. Default to changing the line item dates for him. Cancel changes the timesheet date back to the original value in case there was a data entry error. All done, no error messages to create and calculate --nothing.

Look at the script above. Yes, it does involve an extremely simple loop but I could have used a Replace command just as well. It is dead simple. I will never get confused trying to figure out this script.

Here's the unlocked sample file for your learning pleasure. There is a similar script trigger for Employee Number because you could have similar needs there.

Resources: I'll be adding to this list as quickly as I can.

Using Script Triggers in FileMaker Pro FMI Tech Brief

Six Fried Rice blog. From FileMaker Pro 10 Missing Manual author, Geoff Coffey. Lots of good posts on Script Triggers among other things.

Dwayne Wright's Example Files blog.

Nightwing's 10 Demos for FileMaker Pro 10. Ray Cologon is author of the FileMaker Pro 10 Bible. The Active Highlight demo that highlights the current record in a list screen is a good place to start.