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.
FileMaker, Inc. FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.
Script triggers often solve problems that otherwise couldn't be overcome. While script triggers are incredibly useful, don't abuse this feature or you may encounter script conflicts. Alternatives to script triggers are auto-enter calculations, manually run scripts and many other FileMaker features. Become familiar with every tool on your belt so you can choose the right feature for the job at hand. Hesitate to use triggers but when they solve the problem better than any other approach, use their power to design a top notch solution!
A Little History I remember back when I worked at Claris technical support, the development team passed around a questionnaire asking what kind of script triggers would we like to see in FileMaker. That was back when version 3.0 was released! As you all know, we had to wait till version 10.0 for script triggers to see the light of day. But, it was worth the wait. What I'd like to share with you today is a few of my favorite scripts that initiate via triggers.
FYI: Claris Corporation produced FileMaker, MacDraw, MacPaint, ClarisWorks, MacWrite and many other favorites. in 1998, Claris decided to focus on FileMaker, dropping all other products but gaining the new company name, FileMaker, Inc.
Before I start sharing, let me help you understand how difficult it was to add triggers to FileMaker. Let's take the OnObjectExit trigger when attached to a field as an example. There are many ways to exit a field. You can tab to the next field, click to the next field, commit the record, switch layouts and switch records. Let's focus on the last option of switching records. When do you run the script? Does it happen before switching records so it operates on the current record or after exiting the field so it operates on the next record? If FileMaker chooses after then this differs from the behavior when simply tabbing to the next field. In other words, a trigger should behave in the same manner all the time.
Itchy Trigger Finger Before you reach for that script trigger, consider alternatives. It's often too easy to rely solely on the power of script triggers to solve every problem. This can often lead to script conflict, something I talk about with a specific example in my Window Naming article. The basic premise is, the more scripts you have firing in the background, the more likely they can conflict with each other or button driven scripts. Yes, they are supposed to fire in a specific predetermined order but that doesn't mean they can't cause havoc with each other.
One of the most common alternatives to script triggers are auto-enter calculations. The great thing about field based calculations, is they are schema level. That means they work system wide. In the case of script triggers, you are marrying a script to a layout or object. If you create another view for the same table or place a field on another layout, you need to remember to attach the script trigger. I'm not saying auto-enter calculations replace script triggers. Auto-enter calculations are just one of the many tools on your tool belt that you should consider for a job. Pick the right tool for the job and you'll produce a better solution.
FYI: Script triggers existed as far back as FileMaker Pro 3.0 as Open and Close scripts. They weren't called script triggers back then but they did run a script based on an event and later were incorporated into the script trigger family.
Popover Initialization OK, let's get started! I absolutely love popovers! They make my programming life so much easier. They're easy to implement and hide information I don't need to see all the time. I often use them for reports. I like to walk my clients through a find before producing a report so I use global fields to ask questions in a popover. Those global fields often need to be initialized before displaying the popover so the OnObjectEnter object script trigger is vital to this operation.
NOTE: The best popover alternative, with similar capabilities, is a window card. Since they require a layout, window cards are more time consuming to create and clutter up the layout manager. Think of popovers as a miniature layout within your current layout and use them whenever you don't need the power of window cards.
The most common global field in my popovers are for report titles. I usually initialize a global field using Set Field.
Set Field [MYTABLE::xreport_title; ""]
You don't have to clear the field you are initializing. Instead, you can place a default report title in the global field and let the user override it if they want. Titling a report can be done more easily with a Show Custom Dialog so consider it an alternative if all you need to do is offer a custom title for a report.
Initializing a global field can also act as a default initial value. The most common case is a popup menu. In a single-user scenario, the initial value is whatever was last placed in the global field. In a multi-user scenario, the default for a global field is the last value in the global field before it was uploaded to FileMaker Server. In either scenario, it's best to set the default or most common choice for the popup menu.
Set Field [MYTABLE::xpopup_menu; "Sort by Category"]
FYI: In the example file that comes with this article, the global fields are generically referred to as "xtext1", "xtext2", "xdate1" and "xdate2".
In the case of dates, the formulas can get more complex. These two steps initialize date fields to the first and last day of the current month to facilitate the most common find for a report. However, the user may want to override the date values in certain situations so the fields should left editable.
Set Field [MYTABLE::xdate_report1; Let(@Date = Get(CurrentDate); Date(Month(@Date); 1; Year(@Date)))]
Set Field [MYTABLE::xdate_report1; Let(@Date = Get(CurrentDate); Date(Month(@Date) + 1; 1; Year(@Date)) - 1)]
These are pretty common formulas that can be found easily on the internet. Here's a couple for the beginning and ending of the current work week, which may not be as easy to find.
Set Field [MYTABLE::xdate_report1; Let(@Date = Get(CurrentDate); @Date - DayOfWeek(@Date) + 2)]
Set Field [MYTABLE::xdate_report1; Let(@Date = Get(CurrentDate); @Date + 6 - DayOfWeek(@Date))]
Make sure you attach the script trigger to the popover pane and not the button.
You will likely see a flash of the existing data as the popover opens and is replaced with the initialized data. I've thought long and hard about how to avoid this scenario by initializing the global fields OnObjectExit. While it does solve the problem, it also introduces a new one. The first time the popover is opened, the values will still be there since it hasn't been closed yet. One solution is to initialize them on opening of the solution and that works great but clutters up the open script. A better solution is to have the script trigger run on open and close of the popover. While the script runs twice as many times as needed, it's a short script that runs very quickly so it shouldn't cause any performance issues.
List Filter There are lots of tricks up my FileMaker sleeve but I prefer to choose the simple ones more often than the complicated. This is one of those techniques that's so easy but offers the user a tremendous amount of satisfaction. The idea is distance the user from find mode because most finds are straightforward and don't require you to see every searchable field. Every time a user wants to do a find, they have to go to find mode, enter their find criteria in the proper field(s) and then perform the find. With this technique, everything happens instantly in browse mode as the user types. Sure, it's not going to handle complicated finds but most finds really aren't that complicated.
Start by creating a global text field titled "xfilter". This technique is adaptive so it doesn't matter what table contains the global field. Place the global field in the header of a list view layout or above a FileMaker 17 master-detail layout portal, or, as I like to call them, found set portals. Then, attach a short script to the global field using the OnObjectModify script trigger.
When I first created this script years ago, I considered both OnObjectModify and OnObjectKeystroke as candidates. Both seem to be viable candidates but only OnObjectModify will work since it's a post script trigger. In other words, it runs the script after the event that initiated the script. On the other hand, OnObjectKeystroke runs the script before the event. This allows the trigger to do neat things like "eat" unwanted keystrokes but causes this script to not search properly since the keyboard value just typed isn't available for searching when the script runs. For more information on preventing keyboard entry, see the article Trigger Tightrope.
IMPORTANT: Knowing the difference between pre and post script triggers, as well as the order of execution, is important. While I don't recommend memorizing these details, I would bookmark this link to Setting Up Script Triggers so you can easily look up the information when needed.
I always try to avoid adding unnecessary schema to a database. In the script above, I opted to search the first and last name fields instead of creating a calculation field that combined the two. While this created a slightly more verbose script, I avoided storing and indexing another field. Even if you already have a full name calculation field, is it being indexed? In other words, if you have a large table, why increase the file size and decrease performance by indexing an unnecessary field.
I chose to only check for "no find criteria" (error 400) and not for "no records found" (error 401) because I didn't want to do anything when no records were found. When no criteria is entered, I want all records to be shown. When no records are found, all I want is for no error message to appear and Set Error Capture does that for me. However, I will change the test for error 400 to a test for empty for reason mentioned previously. If I don't make the change, some other script can introduce a different error as discussed in the Window Naming article mentioned above.
The final step could have been a Go to Field step but I chose Go to Object instead. This created an extra step of naming the field via the Inspector but it has proven a more reliable approach. For example, if there are two copies of the field on the layout, the one furthest back in the layering will be selected. No such confusion can occur with Go to Object. I can even switch the Go to Object step to use a script parameter so I can use a different filter field, making it more adaptive.
TIP: The first object placed on a layout is furthest back in the layering. The Bring to Front, Bring Forward, Send to Back and Send Backward menu items under the Arrange menu while in layout mode can alter the layering order, if needed. These four features are also available in the Inspector in the Arrange & Align section.
Tab Control Restoration I'm not a big fan of global variables but sometimes you can't get around using them. If you abuse their use, they clutter up the Data Viewer and makes it more difficult to debug scripts. Yet, when this tool fits the job perfectly, it solves the problem more elegantly than any other FileMaker feature available. And, restoring tab control panes couldn't be a better example of when to take advantage of a global variable. The idea is to store the current tab control panel in a global variable each time a new panel is clicked, in preparation for the time when it needs to be restored.
Global variables ($$) differ from local variables ($) in that they persist beyond the script where they are declared. Local variables are initialized once the script is done running.
So, when does a tab control panel need to be restored? The answer is easy when you know what actions reset the tab control back to the default panel. Any time you switch to a different layout in the same window, the default tab control panel is restored. The same thing happens to portals and fields. Portals rows are deselected and scrolled to the top and since the record is committed, no field is selected. Tab controls, portals and fields are also reset when exiting Manage Database but we are only concerned with actions that might occur while someone is using the solution, not programming it.
At this point, you might be thinking, I'll just open a new window, perform all the scripting operations and then close the window. Since the original window didn't have a layout change, the context of the portals, fields and tab controls are still in place. In most scenarios, this is the easiest solution but sometimes you can't open a new window. There are a variety of situations where this is necessary but I don't want to get off topic so let's just accept this premise that sometimes you need to preserve the tab control pane.
Now that you are convinced of the approach, let's dig into the solution. The first thing you need to look at are the Get(TriggerTargetPanel) and Get(TriggerCurrentPanel). The first returns the panel the user is clicking on and the other the panel that is currently selected. Both seem like possible solutions at this point but what method to choose to obtain their results. A calculation field isn't going to do the trick because it will keep refreshing. We need a snapshot of the tab control panel which is why a global variable is required.
The next question is how to get the tab control panel information into the global variable each time a tab is clicked. This is a great job for a script trigger, unless you want to try an overlay buttons on the tabs of the tab control. Obviously, buttons are not a good choice as they would slow down any changes to the tab control dimensions or addition/subtraction of tabs. In fact, the Get functions discussed above were designed to work with the OnPanelSwitch script trigger so there's really no other choice. You might think the OnObjectEnter script trigger is a possibility but it only triggers when a tab panel is tabbed into, unfortunately.
Now that the trigger is identified, which of the Get functions should be used? The answer lies in whether the OnPanelSwitch trigger is a pre or post script trigger. Post script triggers run the associated script after the event that triggers them while pre triggers run before the event. Pre triggers seem a little strange if you haven't used them before but the idea is to allow the script to perform an action prior to the event that triggered them. For example, the OnObjectKeystroke is a pre trigger and allows you to prevent characters from being entered on the keyboard. This can only happens if the event that triggers a script trigger occurs after the script that's attached to the trigger.
So, what type of trigger is OnPanelSwitch? The best way to find out is to keep the FileMaker 17 help entry bookmarked so you can reference it whenever needed.
If you look at the matrix that shows the pre and post triggers, you'll see that OnPanleSwitch runs the script before processing the event that triggered the script. If the Get(TriggerCurrentPanel) is employed, the previous panel will be stored in the global variable, not the one that was clicked. That means your script will always be one step behind all the user actions. There's very good reason for this behavior, allowing you to perform scripted actions on the current tab before leaving. It also means the Get(TriggerTargetPanel) is the only choice.
Whenever using a new function, it's always a good idea to take a look at the FileMaker help.
There's a lot of important information to note about this function but the most pertinent for the job at hand is the fact that it stores two values in a return-separated list. It returns the index and the object name. The index is simply the relative tab number which is numbered sequentially from left to right. You might think this is the easiest value to use but what script step can restore the index? Unfortunately, you'll need to name every tab using the Inspector and grab the second value from the Get function.
Set Variable [$$TabPanel; GetValue(Get(TriggerTargetPanel); 2)]
Now all you have to do is write a script that navigates to a different layout and the returns. Then all you need at the end of your script is the simple addition of a single Go to Object step.
Go to Object [Object Name: $$TabPanel]
No, really, I love Script Triggers! I often speak disparagingly about script triggers but I actually love them. I just don't want budding developers to think script triggers solve all problems. I see it all too often in solutions I adopt. The previous developer has employed so many script triggers, it's almost impossible to debug other script with all that interference. That doesn't even consider the possible conflicts that can occur when scripts are being triggered all over the place. The point is, use script triggers when needed, in lieu of other features that are often disregarded but more suited to the job at hand.