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.
Claris Commercial Few people know that Claris produced a commercial and it aired on national television. It was one of those favor things so it only played once. I'm not saying it's anywhere close to the impact of the Apple Super Bowl commercial but it's fun to have a look.
Download the Claris commercial
Let me start by saying, this is the most complex technique in the series of five articles covering the restoration of found sets. The sequence started with an explanation of why it's important to save found sets, followed by simple solutions like creating new windows and collecting serial numbers. I even covered a variety of methods for collecting serial numbers to give readers different tools because every technique has it's time and place. The previous article took a different approach and used the Snapshot Link feature to save a found set. Today I'm going to cover a technique I've never actually implemented in a client hired solution simply because it's complex.
Read this First! Maybe you're an expert and can skip the first four articles in the series. Even so, there's stuff in these articles I'll be referencing that might leave you scratching your head if you haven't reviewed them. You never know, you might find some nuggets of truth where you weren't expecting it.
Why Should I Learn this Technique? I've mentioned there are easier ways to restore found sets than storing find criteria, so why should you learn this technique? What you'll get out of this article is a better understanding of how FileMaker scripting, calculations and relationships work. Think of it as a brain teaser or a workout for your FileMaker muscle. There's no particular goal in mind in exercising your brain or body but they end up keeping you clear minded and fit. And, you just might find yourself using a variation of this technique for another purpose.
Gathering Find Criteria Adaptively The script for gathering the find criteria is the easiest part of the solution. Let's start with the table for storing the find criteria. You might be thinking of structuring the database with a table for found sets and another for criteria in fields. In other words, one record per saved found set and multiple related record for every field that contains find criteria. Seems like a relationally sound structure, right?
I always tell my students, you need to know when to break the rules. In this scenario, there are no reports being generated on the find criteria so why go through the complexity of storing data according to relational rules. It's just going to make the database more complicated without any additional capability. It would make restoring the find criteria a bit easier but not much if you are comfortable parsing data. I'm going to opt for a simpler storage method that will make the relational structure and scripting the saving of the criteria easier.
Define a table called CRITERIA with a field for the name of the found set and the find criteria. The find criteria will be stored in a return-separated format so only a single field will be needed.
1) name (text) 2) criteria (text)
FYI: You should already have a table for which you want to save found sets. In the included example file, a table called CONTACTS will be used.
In addition to a table for which you want to save found sets, you should also have a find script. That's right! The saving of find criteria occurs after a find has been performed. The found set saving script will work with manually performed searches but you are probably designing a client solution so you'll need a find script like the one you find in the Ultimate Find.
Now we can write the script that saves the find criteria. The basic idea is to loop through all the fields on the find layout and store them in the CRITERIA table. I typically use the same layout for data entry and finding. Any field that shouldn't be searched is hidden using the Hide Objects feature and the Get(WindowMode) function. Examples include global fields (which can't be clicked into in find mode anyhow), unstored calculation fields and related fields. Unstored calculations are slow to search just like related fields but I also exclude related fields since they often return results that befuddle users.
BTW: Searching a related field will search the data that is seen and possibly data that is not visible unless a portal is employed. This often results in found sets that confuse users who don't understand relational design concepts.
It's pretty simple to hide objects in find mode. Just add a calculation to one or more objects using the Get(WindowMode) function and compare it to the value of one (1). Make sure to click the option to "apply in find mode" or you'll kick yourself trying to figure out why it's not working. You can also show objects in find mode by comparing the window mode function to zero (0).
Get(WindowMode) = 1 //Hide in Find Mode
Get(WindowMode) = 0 //Hide in Browse Mode
I apply these formulas to buttons that are designed for data entry only (e.g. Sort Records) as well as buttons that are designed for find mode only (e.g. Cancel Find). Taking the extra time to apply mode conditions really helps a user understand when they are in find mode versus browse mode. In fact, entering record data into find mode is the number one reason for data entry loss.
Once a find has been performed, the following script will save the find criteria as a return-separated list:
A lot of the steps in this script are the same as the previous article in the series titled Snapshot Saves so they won't be explained. So, we'll skip steps 1, 2, 3, 4, 5 and 6. Step 7 is just a comment telling you about how to remove duplicates which was also covered in the Snapshot Saves article. The duplicate checking code is not included in this script to keep us focused on the technique.
The script really starts at step 8 with the Commit Records/Requests step. This seems somewhat innocuous at the beginning of the script but it's quite important IMHO. No, there aren't any relationships to refresh or global values to commit. I just like to be neat and clean when gathering my find criteria. When entering find mode (or, modify last find in step 9), the cursor remains in the selected browse mode field. If the record isn't committed then the find criteria for different saves will be gathered in different orders. While this won't affect the script for restoring find criteria since it is dynamic, it will make the find criteria hard to read if you ever want to look at it in the CRITERIA table. Small but important point.
The Modify Last Find script simply takes the script into find mode with the last used find criteria. That means the script needs to be performed on the same layout as the original find was performed or some of the fields might not be available. Setting context correctly is all important in the FileMaker world of programming. I've kept this script purposely simple so if you need to place the button for saving find criteria on multiple layouts, make sure you perform a Go to Layout script so all the fields performed in the find are available. Otherwise, the field loop that follows will skip find criteria in fields that aren't present on the current layout.
The loop construct simply cycles through all the fields on the layout, placing the find criteria in a return-separated list. I choose a return-separated list because it's a common format for function results and very easy to parse. You'll also notice I gather fields that don't have find criteria. Again, this is for readability. I could easily add a condition that eliminates empty field criteria but it's easier for me to read the data in the same format every time. The choice is yours.
FYI: If a FileMaker function returns multiple values (e.g. List), it almost always returns the individual values separated by returns.
By the way, a variable is set to the starting field before entering the loop so it can be exited using the Exit Loop If script step. Unfortunately, there is no script step for exiting a field loop like Go to Record/Request/Page for record loops. There really should be but it's easy enough to construct using variables and conditions. It's also worth noting that the Set Variable step inside the loop uses the Append technique to build the find criteria as the script repeats itself. The complete formula can't be seen in the screen shot above so it is provided below:
FYI: The Append technique uses a reference to the target inside the calculation formula to build upon itself. See the Set Field Skills article for more information about Append, Prepend, Push, Pull, Extract and Substitute.
Notice the formula above concatenates a colon and a space to make a clean looking return-separated list. It's the same format you see when receiving an email from a CGI web form. I covered how to parse CGI generated emails in an article named Parsing A Web Form. Therefore, it was natural to use this same format for storing and parsing find requests. The data should look like the following once the loop is done:
With the find criteria in a nice tidy return-separated package, the process of moving it from a variable to a field in the CRITERIA table can begin. A technique from the previous Snapshot Saves article is employed so I won't cover it again. It's not too tough so you can probably figure it out without reading the previous article. All it does is change layouts, create a new record and set the appropriate values into the CRITERIA table. There's a little sleight of hand but nothing too tricky.
It's important to check your work up to this point thoroughly. It's very easy to have the wrong context, miss a field reference in a formula or an entire script step. It's better to find the error now, before the more complicated restoring script is implemented. Just try running the script and make sure the data is stored in a new record and it looks similar to the format show above.
Restoring the Criteria Restoring find criteria requires a little more effort than saving it. I would recommend reviewing an article I wrote on Data Parsing if you haven't worked on extracting data from a string. It's also not a bad idea to read another article titled Parsing A Web Form which basically uses the same technique... just to have another perspective.
Without further ado, let's get a general idea how this parsing works. When parsing, you need to look for patterns in the string being parsed. These patterns allow FileMaker text functions to grab the text you want. Since we have a return-separated list containing field names, colons and find criteria, the pattern is returns and field names. If we can locate the field name we want and the return following that field, the data between those two points can be extracted. Sounds simple, right?
Here's the script for restoring find criteria. Peruse it for a few minutes and then read the blow-by-blow below:
FYI: Included in the downloadable example file is a script that initializes the xname field using a script trigger upon entering the Popover. This was covered in the Snapshot Saves article so it won't be rehashed.
The script starts off innocently enough with the Set Error Capture step. Why, you ask? Well, it's possible the find criteria saved years ago doesn't find anymore valid records. We need to capture those errors, just in case, as you will see below in the If statement checking for no records found. Why I don't check for error 401 is a whole other article which you can read up on in the Trigger Tightrope.
I'm not worried about the loss of focus since we are performing a find which inherently loses focus of tabs, fields and portals. Therefore, there's no need to open a new window as was done in the save script. Just go to a layout based on the CRITERIA table and perform a find for the found set to be restored. The found set should have been selected from a popup menu according to the same steps covered in the Snapshot Saves article so there's no need to cover it again. Suffice it to say, the found set name is preserved in a global field called "xname".
We know we are going to find a matching record so there's no need to check for no records found. I also know there won't be more than one record found since I add "==" to the beginning of the find criteria to match the entire field contents. Once the record is found, the return-separated criteria is placed in a variable and now the fun begins. Context is changed back to the found set where the find will be restored, the mode is changed to find and the script enters a loop construct. The loop is designed to loop through all the fields on the layout, much like was performed when the find criteria was saved. The big difference is the formula attached to the Set Field step within the loop:
NOTE: No target field is specified for the Set Field script step so Set Field returns the calculation result to the currently selected field. Insert Calculated Result could have been used instead but I think this is a neat quirk of the Set Field step that some people might not know about.
This formula is the hardest part and the brains of this technique. Luckily, the Let function makes it easy to read in pieces. Before we deconstruct this formula, remember this formula is contained within a loop marching through all the fields on the layout in find mode. With that said, the first declaration in the Let function is @Field. @Field is set the active field name. This portion of the formula allows it to adapt to the currently selected field as the loop reiterates. Very cool stuff!
FYI: I use the at sign (@) to differentiate my declarations from the rest of my code. Since the at sign isn't used anywhere else, it really stands out.
The next declaration is @Criteria. It's seems like a pointless step but it's oh so important. In order to guarantee the functions find what you are looking for, extra returns need to be added onto the return-separated list. If a return (¶) isn't added to the end, there will be no return to search for after the last field, preventing the criteria from the last field from being restored. A return is also added to the beginning of the list but isn't as important. It can be helpful in allowing for more unique searches such as occurs when searching for the field "city". If a return is not included in the search criteria, it could locate the find criteria next to the "name_first" field containing the name "Felicity". I haven't employed the extra return for the search criteria in the formula above. I wanted to keep it simple so consider it your homework to modify the formula to have a more unique data search.
Declaring @Start is where the fun stuff starts. The Position function locates the currently selected field in the data string. Since the Position function returns the number of characters from the beginning of the string, it's necessary to add the length of the field name and two more characters for the colon and space to specify the beginning of the find criteria.
Now it's time to reach back to your grade school math and pull out the formulas for determining distance for the @Size value. The Middle function extracts data from a string using a starting point and a number of characters from that starting point (rather than an end point). Therefore, it's necessary to convert the distance between the starting point and the ending point into distance. While the concept isn't too tricky, getting the end point can be. Since there are many returns in the return-separated data string, it's necessary to search for the first return after the currently selected field. This is done by telling the Position function to start searching from the @Start location rather than the beginning of the field (as is typically specified by a value of 1).
The result of the Let function is mostly a reference to the declared variables, wrapped inside the Middle function. I usually create this formula inside the Data Viewer, finding it easier to debug since I don't have to run the script. Just enter find mode, place some sample data in the @Field declaration and refresh the formula each time you make a change. Once it's perfected then you can place it in the script.
There You Have It! I love this technique because it teaches you so much about how FileMaker works. I don't think I've ever deployed it in a customer solution, preferring to use one of the other methods discussed in the previous articles in this series. However, I have deployed the related CGI email parsing routine from the Parsing A Web Form many times. But, you never know when this knowledge will help you solve a problem so keep this technique on your tool belt.