Scheduling AppleScript Scripts - File Maker

As you develop more sophisticated FileMaker Pro solutions, you may find you need to add the ability to schedule the execution of scripts. Most commonly, this is for backing up files, but there are many other uses. You can schedule “housekeeping” scripts to run overnight, recalculate the Today function, or refresh other stored fields with current information. You could also dedicate a computer to be a task-specific server. For instance, one computer could act as a FileMaker Pro–driven fax server. A script could be scheduled to execute every 15 minutes to send any queued records to a fax modem. You are limited only by your imagination (and perhaps your budget).

So how do you schedule the execution of a script? FileMaker Pro alone can only execute scripts in response to some user-triggered event (clicking a button, choosing a script from the Scripts menu, or opening a database). FileMaker Server (covered in a later section) can schedule scripts, and it’s your best choice for backups and some housekeeping scripts. But many of your script scheduling needs will be on the client side, not the server side.

From the client side, there are many ways of scheduling scripts. All involve some third-party application, extension, or plug-in. The three principal tools are:

  • oAzium Events (formerly Script Scheduler), a FileMaker Pro plug-in from Waves in Motion
  • QuicKeys, an application from CE Software
  • iDo Script Scheduler, a Mac OS extension from Sophisticated Circuits

All three of these tools are available for both Mac OS 9 and Mac OS X. The most powerful of these tools is the oAzium Events FileMaker Pro plug-in from Waves in Motion. Not only can it execute ScriptMaker scripts from a schedule, it can also execute a script when the user tabs out of a field. One possible limitation is that FileMaker Pro must be running in order for the scheduled script to execute.

The oAzium Events plug-in was covered in the plug-ins section, so you won’t need to examine it again. For the scheduling of AppleScript scripts, you might consider using iDo Script Scheduler. The iDo Script Scheduler control panel (shown below) allows you to set up a schedule of repeating events based on a variety of triggers. You simply choose which script is to be called from this event, and it takes care of the rest.

Controlling File Maker Prowith Apple Script

In this example, the script is called “Recalc Aged Receivables.” It simply calls a ScriptMaker script of the same name in the sample Invoices.fp5 database.

As you develop large databases with many thousands of records, some of your summary reports will encounter performance problems. Consider an Invoices database with 30,000 records. How would you print a report of Aged Receivables with “buckets” for Current, 31-60 Days Old, 61-90 Days Old, and Over 90 Days Old? First, you’d probably try creating a calculation field called Invoice_Age, defined as =(Status(CurrentDate)—Invoice_Date). Then you’d create additional calculation fields for each aging bucket, e.g. Current_Period = (Case (Invoice_Age _ 30, Invoice_Amount, 0), and so on. Problem: All of these aging calculations are unstored, because Status(CurrentDate) is unstored. That means the report must calculate these values on the fly and then add all of the unstored values in each column together to yield sub-summaries by sales rep and grand totals. This could take many minutes to process, even on a fast computer. One remedy is to use the Today function, and let FileMaker Server recalculate Today each night. But that’s overkill. The Today function is recalculated for every record in the database. You only need to update the unpaid invoices (those are your receivables), and there might only be 500 of those records. The most elegant solution is to create a new date field in your database called Current_Date. Then change your Invoice_Age calculation to = (Current_Date— Invoice_Date). Make Invoice_Age and all of the aging bucket calculations into stored calculations. Then create a script that finds all unpaid invoices and replace the value in the Current_Date field with Status(CurrentDate) for the found set. Call this script “Recalc Aged Receivables” and then create an external AppleScript script that calls it, as in the above example. Finally, create a schedule with iDo Script Scheduler that runs this script every night from a client machine. Don’t forget to leave that computer on every night for the script to run!

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics