Semi-Sorted A database can either be sorted, unsorted or semi-sorted. The first two are pretty obvious, but it is less clear how a database becomes semi-sorted. A database becomes semi-sorted when a new record is added to a currently sorted database and the option to keep records sorted is unchecked. It can also become semi-sorted if a record is edited -- but only if one of the fields used in the sort is edited. Deleting a record will not make a database semi-sorted.
The Wedge Claris Corporation and the FileMaker, Inc. have been housed in a building called the wedge in Santa Clara, California since they spun off from Apple, Inc. The building is nicknamed the wedge because it looks like a wedge. For some years they had an adjacent building called the interstitial but it was let go when the company downsized to focus on FileMaker products.
Script Triggers are one of the most fascinating features in FileMaker. Triggers augment existing features like auto-enter and validation as well as introduce completely new functionality not available through button driven scripting alone. The ability to define a script to execute based on a defined event fills so many gaps, their power can never be underestimated.
Harnessed Power There is no doubt amazing features can be created using Script Triggers. With the power of FileMaker scripting firing behind the scenes based on events, often in combination with the calculation engine, a whole new world of programming flexibility not possible with single purpose features alone is open. The abuse of this power of Script Triggers is my concern.
Triggers are not a substitute for good programming skills. I always use an Auto-Enter, Validation calculation or other feature before trying a Script Trigger. Manage Database features are schema level while triggers are layout level. Anywhere you want a Script Trigger to fire, it has to be purposely attached to that layout or an object on that layout. Script Triggers also don’t fire in all scenarios, such as during an event in a web driven solution, while Manage Database functionality occurs regardless of the client.
With diligence, Script Triggers can be managed properly so they function when and where intended. However, triggers should still be considered as a last resort. Too many triggers firing on a single layout can conflict. I know this first hand, having gone crazy with Script Triggers when they were first introduced. As I loaded down layouts with Script Triggers to accomplish every task imaginable, I started noticing issues.
Some trigger problems can be solved with additional programming to prevent one trigger from interrupting another trigger. Even another non-script feature like an Auto-Enter can be interrupted and programmed around. Some conflicts, however, just can’t be solved with any amount of programming, leading me to the decision to implement Script Triggers only when absolutely necessary.
Script Trigger conflicts are the very reason I check for a found count of zero, instead of error 401, when running a find script. Too many times a script has triggered in the middle of a find script and interrupted it. The triggered script can introduce a different error, making the find script not trap for 401 errors. Get(FoundCount) cannot be interrupted and is therefore safer.
Don’t get me wrong. I love Script Triggers and use them all the time. I just use them only when another feature cannot provide the necessary functionality. I remember when Script Triggers, other than the Open and Close triggers, were considered for addition to the FileMaker platform. The amount of thought the FileMaker development team put into how each trigger behaves and the order in which they fire was substantial. Few people know Script Triggers have been in development since FileMaker Pro 3.0 and finally got released in version 10 (with the exception of Open and Close Script Triggers which were around since FileMaker Pro 2.0). I know cause I remember the survey they sent around asking what technical support would do with triggers. The FileMaker Development team didn't approach Script Triggers lightly so neither should you!
Consider a trigger that runs upon exiting a field. A field can be exited in so many ways but compare the difference between tabbing to the next field and clicking on another record. Both exit a field but in different ways. It’s not a big deal when tabbing but what happens when clicking to another record. Does the script fire before or after the event that initiates it? If it fires after the event then the script acts on the next record and not the record where the user started. This is the very reason for the birth of pre and post event script triggers. The number of different methods by which a script can be triggered is mind boggling and should make you consider how delicate the whole script triggering system is.
The point of this story is the myriad of events triggering scripts mixed with the unlimited array of possible script combinations creates a complex soup of outcomes. Every trigger added to a layout or an object on a layout increases the possibility of conflict. FileMaker creators have thought through every combination of every non script trigger feature in their testing labs through so many versions of FileMaker, proving the solid nature of the platform. However, Script Triggers, with their ability to fire in the background, just don’t fit as well into the linear equation as well as a single script run from a button. My FileMaker coexistence equation is to keep things as simple as possible or possibly pay the consequences down the line with conflicts.
The conclusion of this soapbox speech is simply to use the right feature for the right job. Don’t be a script-aholic and solve every problem with a Script Trigger. Learn the variety of features in FileMaker and their purpose. Almost every feature exists for a reason. Find out that reason and apply the feature to the appropriate task. Compare and contrast different candidate features for the job at hand and more times than not you will find a script trigger is not necessary. I'll end with a quote from a FileMaker developer who will remain anonymous before I cover specific examples of triggers, "I don't do finds, don't do sorts, don't create windows, don't need extra fields and find a hole if available". Don't be that developer! Learn to embrace the diversity of features in FileMaker in order to design a sound solution.
Trigger Types There are four types of Script Triggers: Object, Layout, File and Timer.
Object Script Triggers are one of the most popular, firing when an action occurs to a particular object like a field, Tab Control or Portal. It is important to examine exactly when each object trigger fires since so many can theoretically accomplish the same task. For example, the difference between exiting a field and saving a field is subtle but different. The OnObjectExit trigger fires whenever a field is exited, whether that is by tabbing, clicking or closing the window. OnObjectSave executes in the same situations but only if the field has been modified. Knowing which trigger to choose is about studying the nature of each trigger and ultimately understanding their differences. And, as you will find out later, some script triggers run after the event that triggered them and others run prior to the event that triggered them.
SIDE TIP: Attaching a Script Trigger to an object is as simple as selecting a layout object and choosing Set Script Triggers... from the Format menu. A shortcut to the Object Script Trigger dialog on the Macintosh involves Command-Double Clicking the desired object. Windows users Ctrl-Double Click.
Layout Script Triggers can be found in Layout Setup dialog under the last tab. These triggers have a wider scope than Object Script Triggers, acting upon record, layout, keystroke, modes and gesture events. Not to say that Object Script Triggers aren't complex since there are so many different object types that can have a trigger attached. Layout Script Triggers just have a wider scope of actions that initiate them. Regardless, you'll spend hours learning both types.
File Script Triggers have been around prior to FileMaker going relational in the form of Open and Close scripts. File Script Triggers have been expanded to include OnFirstWindowOpen, OnWindowOpen, OnWindowClose, OnLastWindowClose and OnFileAVPlayerChange. It is important to note that the OnWindowOpen trigger will delay execution when a file is opened by a relationship or Perform Script step. When a file is opened hidden, an open script will wait till the window displays in order to improve performance. Imagine a related field accessing data in another file in order to display data on a layout and having to wait while a lengthy open script completes. If open scripts are required, it is best to anticipate the need for an external file and use the Open File script step to ensure the open scripts runs as intended.
Timer scripts are comprised of a single step called InstallOnTimer. It allows specification of a timed interval at which a script will run. This type of Script Trigger is good for appointment reminders and updating dashboards. The timer for this script step runs in the background so it doesn't interrupt your work flow. However, this script step is rarely used, at least for me.
A few things to know about the Install OnTimer script step is that it runs repeatedly with the specified number of seconds but only one timer can be installed at a time. Running a new Install OnTimer Script will replace the current one. The Install OnTimer Script can be canceled by running it without a specified time interval. Closing the last window of a file halts an Install OnTimer Script. New windows from the same file adopt the Install OnTimer Script and continue to run even if the window is hidden. Timer scripts also wait till currently running scripts are done executing. That's about all I'll say about the InstallOnTimer script step until I cover a blog posting on reminders.
Pre vs Post and Order of Execution Script Triggers fall into two execution categories: pre and post. Pre Script Triggers perform the script before the action that initiated them, essentially pausing the event and allowing the cancellation of the event by the triggered script if desired. Post Script Triggers run the script after the initiating action, a seemingly more realistic sequence of events. The tables below also show the order of execution for scripts firing at the same time. The InstallOnTimer script is the exception, waiting for any running script to finish before it executes, regardless if it was initiated by trigger or click.
* Yes, OnRecordCommit and OnRecordRevert are both listed as 20 in the order of execution. That's because both can't occur at the same time. Either the record is committed or reverted, not both.
I reluctantly post the third column showing execution order. While the information is interesting, it is negligible during the development process IMHO. If you are testing your solution, as any good developer should, incorrect order of execution will be evident either by running the Script Debugger or simply by observation. Memorizing the order seems pointless, especially when you can just look it up when necessary. Besides, the FileMaker development team has spent a lot of cycles determining the best order for triggers to execute and I have rarely seen a conflict due to order of execution. If your layouts are overridden with triggers, I can see how the order of operation might be helpful if a conflict occurs, but as I have said before, moderation is the best policy with Script Triggers.
On the other hand, pre versus post is all important when selecting a trigger type. When a script executes prior to the action that initiated it, it is possible to interrupt that action and halt it completely (see the Preventing Carriage Returns example below). I still don't recommend memorizing this aspect of triggers, just realize it exists and consider when choosing a trigger. A good example of the difference between pre and post triggers is that an action, such as typing a character into a field, will not be present when a pre script runs, but will on a post. Know a feature intimately before you start using it so you aren’t surprised or confused at the outcome.
The rest of this blog is devoted to valid examples of Script Triggers that wouldn't likely be possible with any other feature or combination of features.
List View Filter Narrowing down the records being viewing is one of the key differences between a database and a spreadsheet. One of the most intriguing methods for creating a found set is providing a filter as you type feature. There is no need to type in the entire find criteria and then perform the find. Instead, a script trigger performs a find each time a character is entered. Finds on indexed fields are so quick in FileMaker, the user never notices a search script running in between each character, no matter how fast the user can type.
The first step is to create a global field for entering the search criteria. We'll use a customers table as the example for this technique from here on in. Create a new global text field titled “xfilter” in the CUSTOMERS table and place it in the Header part on the CUSTOMERS List layout. Define a script to perform the find and attach it as an OnObjectModify Script Trigger to the global field.
The first step prevents FileMaker errors from appearing, such as when no records are found or no find criteria is entered. Then the scripts enters find mode and places the current find criteria from the global field into the search field. The “name_full_last” calculation field (first and last names concatenated) was selected to simplify the search process rather than using new requests (although, new requests could be used if desired). If no records are found, the script gives no error message and displays a found set of zero records. If the user deletes all the find criteria in the global field, an error of 400 is captured and all records are shown. At the end, the global field is seamlessly re-selected, leaving the user with no idea a script just performed. All the users sees is the list of records gradually reducing or increasing.
While it may seem there is more than one Script Trigger that can be chosen for this solution, OnObjectModify is the only choice. For example, OnObjectKeystroke might be considered. Since it is a pre Script Trigger, running the script before the character is typed into the global field, the find criteria will be one character behind typing of the user. In other words, the first letter is not searched until the second letter is typed, creating an unacceptable latency. Choosing OnObjectExit will require the user to exit the global field to perform the search, making the feature clunky and making OnObjectModify the only choice. When you first start working with Script Triggers, it’s a good idea to test alternatives trigger types to make sure you have the best one for the job.
Preventing Carriage Returns One of the most common data entry errors is typing a carriage return into a single line field. This behavior likely stems from users entering data into a spreadsheet where a carriage return goes to the next cell. What typically happens is the person types a return, remembers they need to tab and just leaves the carriage return character in the field, messing up reports, labels, merge letters and more. If training does not solve the problem, a common solution is to utilize an auto-enter calculation formula to remove all return characters upon exiting of the field:
Substitute(name_first; “¶”; “”)
This version of the formula removes all returns from the “name_first” field upon exiting the field, never teaching the user the error of their ways. It is important to note that the Auto-Enter calculation has the option to “do not replace existing value of field (if any)” unchecked. Unfortunately, this approach requires the calculation formula to be attached to every field that might have an errant return entered. While the Self function could be employed, it still requires a lot of copying and pasting from field option to field option.
A validation could accomplish the same task with the following formula:
The limitation of a validation starts with the inability to warn the user when the erroneous character is being typed. Validation needs to wait till the field is exited. In addition, validation messages cannot be customized as completely as a Show Custom Dialog script step. For example, there is no way to rid a validation message of the revert button, a common confusion for users. In addition, the validation needs to be applied to every field just like the auto-enter mentioned above.
A simple solution is to use the Inspector to allow the Return character to go to the next field ("Go to Next Object using" under Data tab). Yes, this will solve the problem but also prevent the entry of carriage returns into a field like a notes field. Not only that, it needs to be applied to every field on the layout. Yikes!
A better solution might be a Script Trigger since it can fire as soon as a carriage return character is typed and even prevent the return from being entered. All you need is a little understanding of how the OnLayoutKeystroke trigger functions (not to be confused with OnObjectKeystroke). As mentioned previously, the OnLayoutKeystroke is a pre trigger. A disadvantage in one solution turns out to be an advantage in this solution. Because the script fires before the character is typed, the character can be ignored by the script using the Exit Script step. All that is needed for the script to eat the character is a Boolean result of false. In this example, a simple result of zero (0) is provided.
The surrounding If statement controls whether the warning message and Exit Script step are performed. Since a carriage return is invisible, it’s necessary to wrap the Get(TriggerKeystroke) function in the Code function to translate the value to a number for easier testing. Codes can also be translated into characters using the Char function. Another big advantage is the code is layout level so any field that is added to the layout is auto-magically incorporated into the code.
Saving a Record When the option in Layout Setup for "saving records changes automatically" is checked, editing a record is as easy as typing. There is no warning message asking if you want to save the record. I personally find this message annoying. Backups will allow for the repair of any unauthorized changes. Still, some customers insist on having a warning message. Rather than unchecking the option to “save record changes” automatically in the Layout Setup dialog, I prefer to use a Script Trigger so I can customize the message and actions.
Begin by creating a script with the Show Custom Dialog step that allows for three choices when committing a record: Save, Revert or Edit. If the Save button is clicked, no steps run since you simply want the action that initiated the trigger to run. If the Revert button is selected, the record is simply reverted using the Revert Record/Request step. If the user decides to continue editing, the action that triggered the script is discarded using the Exit Script step with a false result.
Since the commit action occurs at the record level, attach the script to the layout using the Layout Setup dialog from the Layouts menu while in layout mode. The obvious trigger choice is OnRecordCommit since it fires when committing a record. It is also a pre trigger, allowing the Exit Script to eat the commit record initiating the script, as mentioned previously. Note that a badge will appear in the lower right corner of the layout indicating a trigger is attached to the layout.
This simple script has the possibility of interrupting other actions. For example, if another script needs to create or edit a record in another table, leaving the current layout will trigger the Save Record script to run, requiring extra work for the developer to ignore the commit. A new window could be opened to avoid leaving the current layout but this presents other issues. If the Go to Related Record step spawns a new window from a portal while the portal row is being edited, the record will not be editable in the new window. Sounds like a tricky sequence of events but it happens all the time. These are just a few of the reasons I prefer the simplicity of saving a record with no warning message.
While these and other problems can most likely be worked around with additional programming, many basic programming techniques are invalidated. Convoluted programming to get around the conflict can cause more conflicts, creating a never ending loop that can never be solved. Simpler is almost always better so you can use advanced techniques when they are really needed. In other words, be selective about what triggers you run and what benefits they add to the overall system. If your client insists on a feature, be ready to explain the downside of using a particular feature as well as alternative solutions. In this particular example, I would explain that it is unlikely many accidental changes will be made and when they are, a backup can be used to restore the old data. Besides, how likely is it for a warning message to stop an accidental change anyhow? Users just ignore prolific messages anyhow.
Conclusion Many more examples of Script Triggers will be examined in this blog. The examples here are designed to lay a foundation for understanding more complex examples. While Script Triggers are an incredible feature, don’t abuse them. Consider all possible alternatives before employing a Script Trigger in order to avoid conflicts. Trust me, you will be a more successful developer if you restrain yourself.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window. Thanks so much!
Than you John Mark for pointing out the obvious and simpler way to accomplish the same result!!
For many years now I've used Key Remap (AKA Karabiner) to map the RH Option key on my laptop to Enter. It has served me well but on upgrading to Sierra the remapping results in the Return character, being key (Code (13)). The Karabiner app is till under development for Sierra, so they may yet overcome the current deficiency.
I've experimented with your example of the OnLayoutEnter Script Trigger to see if it can help prevent the entry of CR's in a text field. It certainly can when you add the Go To Next Field step as result of the If statement.
If, however, the CR is permitted in one or more text fields of a layout, then you have to revert to the OnObjectKeystroke trigger applying it to all the fields where the CR is not allowed.
Is there any way to combine the two triggers so that the OnObject one, permitting a CR, can override the OnLayout one?
Your idea with the Script Trigger might be possible but in your case I would use the other technique mentioned in the article. Just select all the fields on your layout (or most of the fields), open the Inspector and under the Data tab in the Behavior section, you will see an option to "Go to Next Object Using".
I love your blog and this article.
I like the type ahead w/find, but shouldn't you check for error 401, instead of 400?
Glad you enjoyed the article!
If no records are found (401) then a found set of zero is displayed. I prefer this over an error message in order keep productivity higher by not interrupting the user. A blank list view seems to tell the story well enough. Feel free to change the script to suit your needs though. Those are just my thoughts for how I would program it.