An interesting problem occurred to me the other day while I was working on a client’s system, and although I narrowed down the issue right away, an idea for this blog came to mind. What are the dangers of using script triggers in a database system with FileMaker Pro 10 (and now version 11), with regards to automation and interface design? Interesting issues seem to occur due to the nature of triggers and their given behavior.

Using object based triggers seems to be a popular method of delivering automation. Whether you are auditing user interactions, or you are automating tasks based on user entered data, the proper use of script triggers can affect the user’s overall database experience. Imagine we setup a series of triggers to track when users modify data in the Company name field. We attach a script to the field as an OnObjectEnter trigger and we say in the script to set $$value to the contents of the field. Next we create another script for the OnObjectExit trigger, and in this script we evaluate the contents of the field against the global variable $$value, and if they are not the same, go and add to the audit trail. In this script, we may also grab the User Account, Timestamp, etc… An issue can arise when a script accesses the Company Name field. Due to the triggers selected, the script that is running will trigger the $$value and potentially the Audit script to run. Depending on processes in the script, you can see where this could cause problems with the functionality of that particular script, and the overall database integrity. If there are deletes involved, you may see the wrong sets of data being deleted. In this case, using conditions in your scripts can help prevent triggers from running. Another option for this is to use a layout which does not have triggers on the objects, and therefore can process as needed (a Company_BasicInfo layout for example).

Another popular problem with script triggers is the issue of using the Exit Script function, resulting in a false statement. Let’s say we setup a field called Notes, and on the Notes Field we attach an OnObjectExit script trigger. In the script we have conditional statements for what can and cannot be put into the field, and one condition exits the script with a false statement. This could result in a user effectively being stuck in the field, due to the nature of Exit Script [False] function. When we exit as a False, we are effectively telling FileMaker to ignore whatever triggered the script, in this case exiting the field. It’s a good rule to have an Exit Script [True] statement if you ever plan on using an Exit Script [False] to prevent complete lockup. Also in this case, using the Script debugger would help immensely (requires FileMaker Pro Advanced).

Here is an interesting one that happened to me personally, using the layout based OnRecordLoad trigger. I have a metal resource company that tracks batches of items and using ICAP machines and XRAY guns, scan for metal percentages of items they receive for recycling. Based on the scans, the clients are paid out using the current metal pricing to weight ratio. On the Settlement screen we have a series of calculated metal prices based on the percentage and weights of a scanned metal, which are updated as the record is loaded. Some issues to overcome:

1. Stop the auto calculations from performing after the metal has been purchased from the client.
2. Stop triggers from firing when a script is parsing or updating records from conversion (or user driven imports etc…).

The first issue was easiest to overcome, and simply required a condition at the start of the triggered script which checked for the completion status of the settlement, then exit the script if the settlement was complete. The second was a bit more complex to resolve. Obviously the easiest way to correct the issue would be to create a secondary layout, which had no triggers attached to it, and use that layout for the imports and conversions. Another method for correcting this triggering process was merely theory to prevent myself from having to create a separate layout. The idea was to set a global value somewhere to any value (active, scriptname, etc…) and then in the trigger script, add a check for whether that value is set and if it is exit the triggered script. In essence this should work, however I went with the other method since it required no testing/debugging of the scripts involved.

These are just some examples of triggers and how they can cause problems with the structure, integrity, and the overall user experience of a database. If you would like to learn more, please feel free to contact the Anvil Dataworks office. We offer training in FileMaker, as well the various levels of consultation.