Has FileMaker’s dropdown list functionality left you wanting more? While adding a drop down has the benefit of being quick to implement, often it’s behavior can leave a lot to be desired. Not being able to filter the list by any word in a string (typing out the second word instead of the first for example) or being able to style the look of your drop down can be huge drawbacks.
Here’s a technique that is both powerful, speedy, and quick to implement. By Creating a “text tree” in any table (as pictured below) we can utilize FileMaker’s relationships which supports multiple values (separated by carriage returns) to find records by our own specified amount of letters, words, or even data from multiple fields. This technique doesn’t involve finds, and given how versatile it is, we can place it anywhere in our solution — allowing the user to find data without leaving the layout. For example, finding a name only knowing the last name.
Setting this up is fairly straight forward. The first thing we’ll want to do is prep the data by adding a text field to our target table. In this demonstration we will be adding a full_name calc field to the People table. Our new text field will be an auto-enter calc breaking down each name into a text tree, like above. This code can easily be copied & pasted into any solution. You just need to modify the input field. It can also be modified to support longer word lengths, more words, and multiple fields within the local table.
Note: If you’re adding this to existing data, be sure to “refresh” the data, perhaps with a ‘Replace Field Contents’
Now that our data has a satisfactory text tree, let’s create two global fields in the table of the Layout we intend to use. The first field g_SearchField, will be the field on the layout as the field the user will be typing into. The second field g_Search_forRelationship will be used in a relationship to our Text Tree, displayed below:
Now, all that’s left is to place a portal on your layout. It’s context should be the table where your text tree is. Then you put a script trigger (onObjectModify) on your search field, which will move what the user types into your secondary global. It will then refresh the relationship live while the user is typing without having to commit the data entry global.
Your trigger only needs to be a simple set field like so:
There you have it, please feel free to download the file and test it out yourself!
[wpforms id=”59476″]