Lightning Fast Picker

Who needs a record picker? I do!

When designing a custom Claris FileMaker application, we are often faced with the task of finding a specific record, and doing something with it. We might accomplish this task with a simple find: enter find mode, enter your find criteria, and hit enter. This technique works great if we’re simply looking up some information, or making a record edit, but falls short when we’re trying to do something more complicated like link one record to another. In this Codence blog post, we’ll dive into a record picker system that’s intuitive, easy to implement, flexible, and performant. We’ve included a demo file that you can use as a source for your own implementation.

Setting the Stage – A Use Case

Record pickers are a common pattern in FileMaker application UX design, and they take many forms. At the core of a record picker system, we want to return the primary key of the selected record so we can pass that key to a function.

As an example, let’s say we want to create an invoice for a customer; our invoice creation script will need some key bits of information including the primary key of the customer that this invoice is for. If we’re on the relevant customer record, grabbing the primary key is easy, but perhaps we want to implement a global “New Invoice” routine that is accessible anywhere in the solution. This is where a record picker comes in.

We want to present an interface to the user that allows them to quickly locate the customer record of interest using relevant search criteria. If our user is searching for a customer, relevant search criteria might be a business name, a contact name, a phone number, an email address, or even a customer number if it’s known. And we need to be quick about it.

Our interaction workflow for picking a customer might be something like this:

1. click “new invoice”button
2. start typing some search criteria and execute our search
3. see a (short) list of relevant records and select one 
4. fill out the rest of the invoice 

This is remarkably similar to searching for something on the web:

1. create a new browser tab or window
2. start typing your search criteria (in the address bar)
3. see a list of relevant matchers and select one
4. proceed with your task

The User Experience

If you’d like to see this in action, check out this quick video.

Here is the step-by-step process. We’re on an Invoice record and we need to associate this invoice with a customer, we select the search icon to initiate our customer search function:

Next, we enter our search criteria, hit enter, and are presented with a list of results:

We select the item we were looking for and we’re back where we started, and now we have associated this invoice with a customer:

This whole process took just a few seconds, and the time between hitting enter with our search criteria, and seeing the search results was about 250 ms (nearly instantaneous). Granted, we were searching on indexed fields (more on that later), but that’s still pretty quick considering we’re searching in a hosted environment from a table of 10,000 customer records.

Performing the Search

Our implementation involves a layout for the search card window, and a couple scripts. The layout is associated with our Globals table, we have a search field (global, text), and we’re displaying our search results in a portal showing us related customer records.

Our Globals table is related to the Customers table using a global text field (g_idList) that we’re populating with a list of customer IDs from records that match our search criteria.

So what happens when the user submits their search criteria? We snag the contents of that search field (g_SearchField), send it off to the server with a Perform Script on Server script step where we perform a find on the Customers table in the relevant fields for this search, and (if we get some results), we capture the primary keys of the found records and send them back to the client where we put them in the g_idList field. Simple! Let’s look at some of the details of that script.

This script, “Return id_customerList with search criteria”, does a little work on the search string, escaping any “@” that we find with “\@” in case the user is entering an email address as their search criteria (using the unescaped @ symbol when performing a find in a field containing an email address won’t return any matches).

Next we navigate to a Customers layout and perform our find. In this case, we are performing a four-request find, searching the complete address, business name, email address, and customer name fields individually. If we find some results, we package up our idList in a JSON object using a summary field that points at the primary key field in the Customers table and employs the “List of” option.

When we exit this script, which has been running on the server, we pass back that JSON object to the calling script, unpack the idList and set our global field “g_idList” and display the results. The global variable referenced on line 44 ($$searchStatus) is used on our layout to display some stats on the set of records that we have found. It’s important to give the user feedback on the number of records found (especially if it’s zero!). Note also that we need to refresh that layout object on line 46 to get our global variable to display on the layout.

It’s always good practice to be surgical with refresh operations. In the case above, we could perform a Refresh Window on line 46 instead of Refresh Object, and it would certainly cause our global variable to display properly. That said, depending on the number of records returned, a Refresh Window would add significant time to our total execution time, perhaps as much as a second or more.

Making the Selection

Once we have our search results displayed for the user, they can make a selection. All we need to do is capture the primary key of the record that they click on in the portal, dismiss the card window, and then perform an operation on the record/window where we began this selection process. That’s part of why we like card windows so much: they allow us to pop over to some other context, perform an operation, and return to where we started – the correct layout, the correct record, and the same found set – without dealing with off-screen windows, or any other trickery.

In the “select” section of our Select Customer script, we’re doing a little housekeeping, cleaning up the globals that we used for searching and displaying our results (line 53, 54). We dismiss the card window, and then do what we need to do depending on where we initiated the whole select customer process. In this example, we came from an Invoice layout ($$sourceSelectCustomer = “invoice”) so we set our foreign key with our new customer id, ($id_customer, set with a script parameter near the top of this script), commit the record, and we’re done.

Initiating the Search/Select Process

Way back on our Invoices layout, our user clicked the search icon to initiate this search/select process, and that action is what triggered this whole sequence of events. Setting up for the search is a matter of showing the card window, putting the cursor in the search field, and waiting for keystrokes. When the user clicks the search icon, we run the Select Customer script and pass a JSON object containing an action (“launch” in this case), and a source (“invoice” in this case).

Lines 5-8 capture this parameter and parse it out into the variables we’ll need. Line 13 begins our “launch” sequence where we set a global variable with our source so we can use it later during a subsequent “select” action. Then we just set up our card window with the Customer Search layout, clear out the global fields and put the cursor in the search field. And yes, these global fields were cleaned up when we last dismissed this card window, we’re just being super careful!

Once our card window is open, there are really only a few things that a user can do: they can cancel (“X” icon at the top right), search/select, or search/cancel. If you look at the Customer Search layout carefully, you’ll notice that all of these available actions are handled by the Select Customer script, each with a different value for the “action” attribute of the passed parameter. Notice that each section of the Select Customer script does setup or tear-down as appropriate.

Reusing Customer Search in Other Application Contexts

In this example file, we are using this customer search system in two different contexts: we have been describing the invoice context, but we’re also employing this search system on the customer layout. If you were paying attention to the “select” action above, you probably noticed the small section handing the situation where $$sourceSelectCustomer = “invoice”. That small bit of code is all we need to adjust when we use our picker in a new context. For the customer context, our “select” action is to simply find the customer record selected.

The only other thing we need to do is pass the proper value for source with our layout button. If you look at the button setup for our Search for Customer button on the Customers layout, you’ll see a slightly different parameter than we have on the Invoice layout for the search button. When we pass this to our Select Customer script with action = “launch”, one of the first things we’ll do is set the global variable with our source so that when (if?) we get to the select action, we’ll know what to do (perform the find in this case).

Adding This to Your Custom Application

If you download the demo file, it should be pretty easy to implement this in your own solution if you follow these steps in order.

1. Copy the custom function “isCanHasPSOS” to your solution.
2. Copy/Paste the Globals table into your solution (or surgically modify your existing Globals table, adding the gSearchField and g_idList fields).
3. Add a table occurrence to your graph for the table you want to search in and connect it to your Globals table linking g_idList to your primary key in your search table.
4. Add a summary field to your search table pointing to your primary key and using the “List of” option.
5. Create a search layout similar to the one in the demo file and either copy/paste the layout elements from the demo file or create them by hand.
6. Copy the “Customer Picker” folder of scripts to your solution. Rename the “Customer Picker” script to something more appropriate for the table you’re searching.

With all of these elements in your solution, you’ll need to do some post-copy/paste cleanup. Start with the search layout and ensure that the portal is configured to show records from your globals/search table occurrence that you configured above in step 3. Also check that the buttons on the layout are all pointed to the “Select Customer” (or whatever you renamed it to above) script. Ensure that you’re passing the appropriate primary key in the parameter for the select button. Note that this button in the demo file is transparent, it fills the entire portal row, you may need to locate it with the Objects list on the left of your layout.

Next, edit your Select script and ensure that you’re referencing the correct search layout in the “launch” section, and that the Set Field and Go To Field script steps are pointing to the proper fields in your globals table. Use the demo file as a reference if needed. Do the same in the “cancel” section. In the “search” section, you may need to fix the reference to the global search field in the script parameter for the Perform Script step, and the reference to the g_idList field in the set field step. In the “select” section, fix your Set Fields, and then configure your source specific sections to act appropriately based on context (or save this for later; just remember to come back to this section or all your searching and selecting will be for naught!).

Edit the “Return id_Customer…” script, and edit the Go to Layout script step, pointing it to an appropriate layout that is associated with the table you’d like to search. Modify the Perform Find script step to perform a find that’s relevant to your situation. Note that you can add multiple requests, use omit requests, or even hard code find requests to search a subset of your records (e.g., always omit “archived” records). Modify the Set Variable step to point to your summary field that you configured in step 4 above.

The script “Trigger: OnObjectKeystroke” should not need modification. Just be sure to configure your search field on your search layout to run this script (Format > Set Script Triggers… > OnObjectKeystroke). Though we didn’t mention it above, we’re “listening” for the enter, return, or tab key in this field so that the user can initiate the search by typing one of these keys when entering search criteria. Alternately, the user can click the search icon.

A Note about isCanHasPSOS and Perform Script On Server

Part of what makes this technique so performant is the fact that we’re performing the find on the server. It’s a subject for another blog post, but in short, performing the find on the server and returning the list of primary keys to the client is on the order of 2-3 times faster than performing the find client side. This holds true if the fields that we are searching are indexed. If we’re searching on unstored fields, the situation can get more complicated, but if we limit ourselves to searching on indexed fields, server side is the way to go.

The custom function “isCanHasPSOS” is one we have been using for a while at Codence, and it’s a quick way to ensure that we’re in a situation where we can perform a script on the server. The function isn’t too complicated, and in fact, we don’t really need a custom function at all. That said, we use a custom function in this case for two reasons: (1) we can turn it off in a single location if we want to force all PSOS operations to operate client side, and (2) the logic is just obtuse enough to warrant using a custom function so we don’t need to type it out every time!

PatternCount ( Get ( HostApplicationVersion ); "Server" ) and PatternCount ( Get ( ApplicationVersion ); "Server" ) = 0

In short, this translates to “is the current script running in a FileMaker client and am I connected to a FileMaker Server?”

We use this custom function to branch our code using a method that merits explanation. We use a technique that allows us to write a single script that will execute client side when a server isn’t available, but executes server side when possible.

On line 15, we’re using our custom function to see if we can perform the script on the server and if we can, we perform the current script with a Perform Script on Server script step, passing the $parameter accordingly. When we execute this script on the server, we’re back at the top of the script at line 5. Once we get down to our test on line 15, isCanHasPSOS evaluates to False and we skip down to line 23 where we execute the real meat of the script. Once our (server side) script exits, we’re back client side at line 17 where we capture the result, and exit the script (line 19).

This technique is a little bit of a head scratcher, but allows us to perform any script server side by adding a few lines of code, and without having to create a wrapper script of the form:

If [ isCanHasPSOS ]
  Perform Script on Server [ Script ]
Else
  Perform Script [ Script ]
End If

Summary

Our Lightning Fast Picker is a great way to pick records in your custom Claris FileMaker solution. It’s fast, flexible, and relatively easy to implement. Once you get this intergrated into your custom solution, it’s easy to duplicate your search layout, attach it to a new table, add a new table occurrence off your Globals table, duplicate your picker scripts, and you’ve got a new picker for a new domain. We hope you find this useful, and as always, if you have questions or comments, we’d love to hear from you.