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.
When I first started teaching FileMaker scripting, I went through the entire list of script steps and provided a simple example of each one. By the end of the third day, we had covered every script step but with no depth. More questions remained than were answered. That’s when I decided to focus on the most important steps. Instead of spending valuable time teaching scripts steps most people would never use in real life scenarios, I decided to concentrate on steps that are used over and over and have tons of depth. That’s how I developed my "Ultimate Find" script. It’s a great way to start your journey into advanced scripting, demonstrating important concepts and sophisticated techniques with key script steps, all in one little script. Besides, every good solution needs a well designed find script.
Why a Script for Find? Find mode seems to work pretty well so why script it? Just let the user go into find mode and search for stuff. It couldn’t be easier, right? There are a plethora of reasons to control the user experience with scripts, especially in find mode. Let’s start with the biggie! Try entering find mode and create eleven new find requests. On the eleventh request, a warning message appears asking the user if they know they are in find mode.
This is a technical support requested dialog. I worked in Claris technical support for five years and the number one reason for people losing data was entering data in find mode. Browse and find just look too much alike. But, try explaining that to a customer, "Sir, is there a chance you entered your data in find mode?" The customer response was usually, "do you think I'm an idiot"? Hence, the warning message.
Over the years, other find mode indicators have been added to prevent this tragedy like those little magnifying glasses in the fields. But, the issue still occurs to this day. People just ignore the warnings and end up paying the consequences later. It’s your job to make it impossible, or nearly impossible, for a user to make a mistake. One way is to lock down find mode with a script.
Other reasons exist for scripting find mode. Let’s start with one of my personal pet peeves... find mode and browse mode are not the same and should be separated via layout. Just like print and browse mode differ in their goals, requiring different fonts and dimensions, so do browse and find mode. I'll talk more about how this is accomplished later, but for now, there are three basic layout types in my mind: data entry, locating records and outputting data.
So, what’s wrong with browse mode stuff in find mode? First, many scripts work the same in browse versus find. That sounds good but it really isn’t. It furthers the confusion for a user that they are still in browse mode, potentially causing loss of data. For example, a script that creates a new record in browse mode creates a new request in find mode, hence the script name New Record/Request. When you think about it, this is the same reason why an unscripted find mode causes problems.
Other script steps take the user back to browse mode before running such as Sort, Replace Field Contents and many others. Not only is this disconcerting for the user, it could split a script in half by having part of it run in find mode and the other half running in browse mode.
Some script steps don’t even function in find mode or vice versa. In other words, your button might not do anything in the wrong mode. Examples include Modify Last Find, Refresh Window and Enter Find Mode, to name a few.
And, I’m just getting started! It’s not just script steps that aren’t cross-mode compatible. Certain browse mode objects don’t work or just don’t make sense in find mode. Global fields are great in browse mode for user interface but can’t be searched. In fact, global fields can’t even be clicked in find mode, much to the chagrin of users. It doesn't matter that there is no point to searching a global field, it still generates a phone call. The same is true for summary fields but what would a summary field be doing on a browse mode layout?!? The likely outcome is another call to your phone asking "why"? Your job is to prevent these calls so you can spend more time programming.
Other fields can cause slow or unexpected results such as related fields or unstored calculations when searched. Users may not understand searching a related field actually searches all the records related to the parent record, producing found sets that sometimes don’t make sense to an inexperienced FileMaker user. Unstored calculations and related fields can also make searching unbearably slow with their unindexed crawl through records, especially on remote connections.
Hopefully loss of data and the differences between browse and find mode make it clear a find script is absolutely necessary. Don’t worry, the script we design here will work across all tables and all files without modification. Once you are done with this script, you’ll never have to touch it again.
The Basics A find script can be as basic as Enter Find Mode followed by Perform Find. A lot can be learned from this simple script. I’ve seen a lot of developers separate the two basic halves of a find script into two scripts. Separating any script into two and excluding the pause is almost always a bad idea since you lose control of the user. When a script is paused, most actions are dimmed from the menus and other windows cannot be activated. Essentially, you have locked the user into completing the script.
Enter Find Mode [Pause] Perform Find
Hide Object Back in the old days, I used to create two different layouts for browse and find. While it solved the problem of user confusion, it created a lot of extra work. Even though the two layouts were designed specifically for each mode, most of the fields were still available in both locations. If a change was made to the browse layout, the same change might need to be made to the find version of the layout. The likelihood of the two layouts getting out of synch is great even with due diligence.
Since FileMaker 13, a conditional formula with a boolean result can be applied to any object so browse and find layouts can be the same. True hides the object and false keeps it showing. With a simple formula, any object can be hidden from find mode or browse mode. The Get(WindowMode) function returns a “1” for find mode so apply a simple comparison formula to each object you wish to hide. Don’t forget to check the option to apply in find mode.
Objects that should be considered for hiding include buttons, global fields, unstored calculations, unindexed fields, related fields and portals and any other item you feel doesn't make sense in find mode.
Hide Object can also make objects appear in find mode. I like to have a “Continue” and “Cancel” button appear only in find mode.
Get(WindowMode) = 0
You also might want to show a New Request, Delete Request or Omit button in find mode. Sometimes these choices confuse users, and since most finds are straightforward single requests, I usually don't include anything but the Continue and Cancel buttons. If a user needs something more complicated, it's usually for a specific scenario and I walk them through the process with a separate script.
It’s really that simple! The Hide Object feature can transform what used to take two layouts into single layout compliant with browse and find mode requirements. You can also take the opportunity to enter some instructions for find mode and possibly a different title to help further differentiate find and browse mode. I usually stay away from changing the background color but it's really up to you!
Continue & Cancel Since the Status Toolbar will be hidden for users upon opening of the file, it’s a good idea to add a “Continue” and “Cancel” button, as mentioned above. The “Continue” button is quite easy. It just runs the Resume Script step as a single step. The “Cancel” script is a little more complicated. Not only does the script need to return to browse mode, it also needs to cancel the current script. If the find script is not canceled, it will remain paused in the background and could cause all kinds of trouble, like continuing somewhere down the line when the user types the Enter key on the keypad.
Some might consider writing the script with two steps like so:
Enter Browse Mode [ ] Halt Script
The problem with this script is it always halts the script. Maybe you want to repurpose this script for use in another area but don’t want it to stop all other scripts from running. It’s better to always consider how scripts might be used in another capacity. While this script may never be used elsewhere, let’s not shoot ourselves in the foot, especially since there is an easy way out. In the Button Setup dialog, reveal the options at the bottom and change the default “Pause Current Script” to “Halt Current Script”. Now the fate of the currently running script can be determined at the button level.
For the record, I rarely use the Halt Script step. I can't remember the last time I needed it. Exit Script is a different story but the subject of another blog entry.
Capturing Errors Try performing a manual find but enter find criteria you know doesn’t exist in the database. You will get an error message from FileMaker that no records were found.
Users should never see this message! What is a “record”, I’m looking at invoices? Do I click the Cancel or Modify Find button? Use the same find criteria in a scripted find and you get a similar but different error dialog.
The Continue button makes sense to you as the FileMaker developer in order to continue the paused script, but just confuses the user. Throw in the Allow User Abort [Off] step and the Cancel button disappears!
Add the Set Error Capture step and the entire message disappears. Now we are getting somewhere! Let’s replace the FileMaker dialog with a friendlier message with terminology the user understands.
But, before we start, what happened to the dialog? Did it really just get suppressed? It’s important to understand that the FileMaker dialog was hidden by the Set Error Capture step by clicking the Continue button. While the Continue button is not really clicked by FileMaker, I like to think of this way because it’s exactly the same outcome as manually clicking the Continue button.
In order to display a custom message when no records are found, you need to check for errors. You could look at the online help but there are hundreds of errors. Good luck reading all those descriptions. You’re much better off using the Script Debugger to identify the error. Just turn on the debugger and walk through the script till you get to the Perform Find step. Once this step is run, an error will appear in red at the bottom of the floating palette. Now all you need is an IF statement with the Get(LastError) function.
SIDE TIP: You can also pull up the Script Debugger after a script has run and it will show you the last error it returned.
The Allow User Abort and Set Error Capture sub-scripts are a standard programming technique for all client work done at Database Pros. These modular scripts set the Allow user Abort and Set Error Capture steps to the correct mode for the developer and users:
Allow User Abort:
If [Get(AccountPrivilegeSetName) = "[Full Access]"] Allow User Abort [On] Else Allow User Abort [Off] End If
Set Error Capture:
If [Get(AccountPrivilegeSetName) = "[Full Access]"] Set Error Capture [Off] Else Set Error Capture [On] End If
It’s important to remember that errors are stored for one script step only. In other words, each script step generates an error. That error is usually zero, meaning no error, but it replaces the previous error in memory. That means you can test for an error until the next step is completed. Conditional constructs using the IF and Else If steps are considered one step so multiple errors can be tested throughout the statement.
Paying attention to the state of FileMaker at all times is the hallmark of a good developer. When no records are found, FileMaker doesn’t automatically show all the records. This can be very alarming for a user. You might get a frantic call exclaiming all the invoices are gone! Therefore, it is important to employ the Show All Records step to alleviate the potential crisis.
Notice that the custom dialog comes first, followed by the Show All Records step. The steps could have easily been switched without any performance issues. The reason for the order is so no records show behind the custom dialog. With the Show All Records step first, the user thinks there actually were records found and there is something wrong. I know this to be true because every time I don’t arrange the steps in this manner, a student point it out in my class. Details are important and separate your solution from the rest.
Now is the time for my Spider-Man speech. When error capture is turned on, you have a great responsibility to know all the errors that could occur. In this example, there is another error to be trapped. Trying manually running a find but don’t enter any find criteria. Just perform the find immediately after entering find mode. The following message will appear.
Now try the same steps with your find script. No message appears. No big deal, right? FileMaker just goes back to browse mode. Well, you should almost always alert the user to what’s occurring. They might have been distracted, thought they entered find criteria and get fooled into thinking that is their found set. While this is not a critical error to capture, I bet you can imagine a situation where an error caused all records to be shown rather than a found set and your script deletes all the records in the database. The lesson here is to be as careful with the Set Error Capture step as you are with a sharp knife.
Adding a second test to the conditional construct is easy using the Else If step. Just test for an error of 400. There is no need to show all records since no find has been performed. FileMaker will automatically restore the previous found set.
While this script works perfectly fine, I have found it to fail under certain circumstances when a script trigger interrupts the script. I had a script trigger changing the window name to the contents of the current record upon record load. While the OnRecordLoad trigger runs as you are navigating records in browse mode, it also fires on mode change when moving back to browse. The trigger interrupts the find script at just the point when the find error is being generated and ends up replacing it. Therefore, I have adopted a different approach using the Get(FoundCount) function to prevent this issue.
Get(FoundCount) = 0
Script triggers often conflict so it’s a good idea not to abuse this feature. I find amateur developers often become one-trick ponies once they learn about script triggers. Instead of learning all the other methods for accomplishing a task, they rely too heavily on script triggers. Make sure you learn how to use all the tools in FileMaker so you can apply the right one for the right job.
While we are on the subject of the IF statement, let’s complete the functionality to accommodate records being found. It’s a nice touch to show list view when more than one record is found and an easy task for FileMaker scripting using the Get(FoundCount) function.
Refind FileMaker includes the ability to refind or modify the find whenever no records are found. By turning on Set Error Capture, this ability has been removed. The method for returning this functionality is not as straightforward as it could be but I wouldn’t say it is complex. I have seen some developers employ the Perform Script step, calling the same find script again from within itself whenever a modify find is needed. While this does work, it is unsupported recursion that will fill the memory stack with multiple copies of the same script. If the user modifies the find enough times, it could crash FileMaker.
A better solution is to use a looping construct. If you are familiar with a record loop, this kind of loop is very similar but loops through script steps. All you need to do is determine what script steps need to be looped and how to exit the loop when needed. Interestingly enough, the Enter Find Mode step does not reside in the loop since only the first run through the script should start with a blank find. Subsequent loops will all be refinds, utilizing the Modify Last Find script step.
In order to exit the loop, you need to consider all the methods for exiting to determine which is the best. There are eight script steps which can exit a loop:
Exit Loop If
Go to Record/Request/Page
Go to Portal Row
Close Window (if it’s the last window)
Now it’s just a process of elimination to determine which script step is best suited for the job. This is not a record or portal loop so those two steps can be quickly discarded. Exit Script and Halt Script are almost never a good way to exit a loop since they also exit the entire script, skipping additional steps following the End Loop step. I have used Close Window, Close File and Exit Application to exit a loop before but not very often. That leaves us with Exit Loop If as the only choice. Sometimes this process will reveal two or more candidates for which you then need to apply further analysis to determine the best choice.
The Exit Loop If statement will be used inside the test for zero records once the message has been modified and a second button has been added. Actually, quite a few things have been added or changed in the script so they have been highlighted. Most importantly, notice which steps are inside the loop and which are not. Only steps that need to be repeated are inside the loop. The Enter Find Mode step wasn’t just excluded from the loop but the Pause option was removed. We can’t pause outside the loop or the user will never be able to redefine their criteria. The Pause/Resume Script step also follows the Modify Last Find step.
It’s interesting to note that the Modify Last Find step does absolutely nothing the first time the script loops. Modify Last Find is only available in browse mode so it is basically skipped. In previous versions of FileMaker, an error was generated but now FileMaker just acts like it’s not there. However, each time the user decided to modify the find, FileMaker will be in browse mode and the Modify Last Find will function as intended.
Instead of just a single Exit Loop If step, four of them were inserted. However, only the one inside the no records found portion has any logic. The other steps simply result in true every single time. There is no need to do further testing in the other Exit Loop If steps since the Else If statement controls whether the Exit Loop If line is reached. A simple true result is all that is needed.
In order to best understand this script, I find it best to turn on the Script Debugger and walk through every possible user interaction including one record found, more than one record found, no records found while clicking the No button, no records found while clicking the Yes button and no find criteria entered. This process will also help you weed out any bugs if a mistake was made.
Dynamic Scripting Dynamic scripting is writing code that can adapt to different scenarios without modification like different layouts, fields, tables or files. A dynamic script should just work as soon as a button is moved to a different layout or a script is imported into another file. No changes required. It’s always a good idea to script dynamically when it will save you time or effort. In this case, we are going to apply dynamic navigation to our find script.
I like to use dynamic layout navigation via good naming conventions. The following two scripts navigate from form to list and list to form using a layout calculated by name. This requires layouts to be named with their table name and function such as "Form" or "List". For example, I name a form layout for the Invoices tables "INVOICES_Form".
Form to List:
Go to Layout [LeftWords(Get(LayoutName); 1) & "_List"]
List to Form:
Go to Layout [LeftWords(Get(LayoutName); 1) & "_Form"]
I use these two scripts as sub-scripts in the find script to replace the hard coded layout navigation so the find script works across any table.
However, this is not the only place that needs adaptive scripting. The message that displays when no records are found mentions the word “invoices”. If we want to use this script in the customers and products layouts without modification then this needs to be changed. Applying the same technique with the LeftWords function allows for a custom message. Just a little change is required to the formula. In the title of the Show Custom dialog, surround the LeftWords function with the Proper function so only the first word is capitalized:
"No " & Proper(LeftWords(Get(LayoutName); 1)) & "!"
In the message, use the Lower function since the table designation is in the middle of a sentence:
"No " & Lower(LeftWords(Get(LayoutName); 1)) & " were found! Would you like to modify your find criteria?"
The message when no criteria is entered does not need to be modified since it doesn’t specify any specific table locations. Now you can copy and paste all the buttons including the find, continue and cancel to any layout where you want to perform a find. In fact, I usually create the first layout and use it as a template for the rest.
As you are copying and pasting find buttons, don’t forget to set layout unique objects to disappear in find mode using the Get(WindowMode) function discussed earlier. If you want to hide a portal when there is no point in searching it, such as the case with a Filtered Portal, there is no need to hide every object inside the portal. Just hide the portal itself and the objects within it will disappear as well.
Restoring the Found Set No matter how many times you modify find criteria during a manually initiated find, FileMaker always restores the original found set if you decide to cancel. Unfortunately, the same is not true for our scripted find. Disable the Show All Records step from the script and perform a find that doesn’t locate any records. As soon as any find is performed, the previous found set is lost. Of course, if you start a find but then cancel before the script actually performs the find, your previous found set will be restored. Even if you enter no find criteria, the previous found set will be restored. Wouldn’t it be nice if no records are ever found that our script restores the previous found set?
In order to accomplish this task, we are going to need to lay a little groundwork. Start by creating a new table occurrence for one of your tables. There’s no need to relate it to any other tables, although you could if needed, as relationships will not be necessary for this exercise. Create a new form or list view layout based on the new table occurrence. Add some fields if you want but they won’t be necessary. The layout can be completely blank if you like. Now create a simple found set by omitting records. In the same window, switch to a layout based on the original table occurrence and create a different found set.
If you switch between layouts based on the same table occurrence, the same found set, sort order and current record are retained. If you switch to the new layout based on a different table occurrence, the found set is different. This is a very important concept to understand outside this technique. Even though the layout are based on the same source table and contain the same records, layouts based on different table occurrences in the same window act independently.
The next piece of foundational knowledge is called island hopping. This knowledge is especially critical when working with the anchor-buoy relational scheme. Since not all table occurrences are connected in the anchor-buoy system, it is often helpful to transfer a found set from one table occurrence to another. For example, let’s say you want to show the related records showing in the portal based on a table occurrence from one segment of your anchor-buoy system in another segment. You may not have realized this before but you can specify a relationship in a Go to Related Record step that doesn’t have anything in common with the layout except for the source table.
If we take this knowledge and tweak it a little, we can obtain some very cool results. Navigate to the layout base on the original table occurrence and create a script called “Save Found Set” with the following Go to Related Record definition. A table named Invoices has been used for this example:
The original table occurrence, named "INVOICES", is attached to the "INVOICES_Form" layout where this script will be initiated and the new, unrelated, table occurrence is attached to the "INVOICES_Save" layout. At first glance, this doesn’t seem to make sense. How can you go to a related record that isn’t related? You aren’t even using a relationship For whatever reason, this technique has worked since FileMaker 7 and will continue to work in the prescribed manner. Whatever you need to do to wrap your head around this behavior, do it and move on. I can’t say it makes logical sense to me but the outcome is undeniable and provides immeasurable functionality.
When the script is run, what happens is, the found set from the INVOICES_Form layout based on the original table occurrence is transferred to the INVOICES_Save layout based on the new table occurrence. Even the sort order and current record or moved.
Throw in a return to the original layout and you have yourself a script that preserves the found set.
The screen doesn’t even flicker. And, since you will be following the saving of the found set with a find script, there is no need to preserve the location of the cursor, active portal row or current tab panel. All that is lost when changing modes anyhow.
In order to restore the found set, just reverse the script:
Name the above script “Restore Found Set” and place Perform Script step inside the Ultimate Find script. It’s important to place the saving script outside the loop and before any layout change is made. The restoring script is only needed inside the no records found portion of the IF statement since only this portion of the script destroys a found set unnecessarily.
While this technique is really cool, it does require the existence or addition of an extra table occurrence for every table you want save and restore found sets. It also cannot be made completely adaptive due to limitations in the Go to Related Record step. While the layout can be specified with a calculation, the table occurrence cannot. Therefore, an IF statement inside the save and restore scripts is required.
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
Just came across this article.. wow!
The Restore Found Set trick is just great. I wish I'd seen it a few weeks ago, before I added my own version, which involves a 2nd TO, but then dissolves into a delirious back and forth of 1,2,1,2 number switching in a Replace script. There's always a better way.
Thank you so much for the effort.
I'm using Master-Detail, which wasn't available when this article was written, but which, I think, makes this Restore found Set technique, even more relevant today.
I can also add, that a rather nice surprise, is that the record which is active, when the Found Set is stored, is once again the active record after the Found Set has been restored. Very cool.
Thanks so much for leaving a comment! Great points about the Master-Detail layout (or, found set portals). Happy FileMaking!
Awesome article. Thanks. It answered several questions for me I have had for a long time - and I have employed the TO method described to restore found sets. Soon I will employ the entire script in my solution to prevent errors caused by user mistakes using Find.
At some point could you expand on using Command Period to stop script aborts. I read that in another of your posts and did not really understand that - how or when it would be used.
Glad you enjoyed the article! I place Allow User Abort [Off] at the beginning of almost every script to stop people from canceling a script in the middle of processing. Some scripts run so quickly that it would be impossible to cancel them. Since Allow User Abort defaults back to "on" at the end of every script, it needs to be added to every script where you don't want a user to cancel it. The link below references an article which my help you to understand why I modularize the Allow User Abort step: Modular Scripting>