If you are repeating an action, automate. This is the programmer's mantra, and if you use it in your business, you will certainly save time, money, and sanity. Suppose you need to find how many sales John handled in December above $10,000, but you want to omit the customers ABC LLC. and XYZ Inc.  

In FileMaker Pro, you can accomplish this in Find Mode with three requests. For a one-off search, this is easily spun up. But now John wants to see his metrics. He enters Find Mode and repeats your search. And Susan needs the same search, but on her name. Whoops, she really needs sales over $15,000 and under $30,000! Now you, Susan, and John have collectively entered Find Mode four times with a total of twelve search requests. Your company is repeating an action, so what do you do?

Automate Finds with Saved Searches

The end goal of this feature is to provide your users with a method to create reusable searches so they can execute complex, multi-parameter finds with a single click. As implemented in the demo file (by the Query Builder layout), a user can: 

• Create a new search on any table in the file's database.

• Create any number of requests, omit or not.

• Populate those requests with any number of fields to search by. 

• Hit the search button at any time and be brought to the specified layout with the specified saved search.

Query Builder Design

As with any new feature, your implementation can range from quick-dirty-simple to decked out in every whistle and bell. The demo file is relatively fancy, so this article only lightly covers its design. For the nitty-gritty details, I suggest downloading and playing with the demo file yourself!

Saved Search with JSON

JSON is the powerhouse behind this feature. Regardless of how intricate you make the user interface, any implementation will end by saving the query in a JSON field and parsing that JSON to execute the search. For example, here is a simple search from the demo file:

This search will retrieve any invoice over 100 and omit any invoices from 2025. How you decide to structure this JSON is up to you. The demo file uses FileMaker's Data API format, which is simple, forward-compatible, and well-suited for iteration.

You will be performing a lot of iteration. When the user clicks the search button, the script "Query Builder - Search With Query" navigates to the user-defined layout, enters Find Mode, and iterates through the search JSON. The keys of each 'query,' or request, correspond to a field name. The associated JSON value is what we will search in that field. Our script populates said fields accordingly and performs the find. Like any good automation, this script does exactly what a user would do by hand, but far faster.

Building the Search with JSON

How you construct the search JSON is also a matter of preference. The demo file utilizes a relatively complex value list system to limit user input while accounting for database changes. Your system can follow the demo file or be as simple as a hardcoded value list of tables, layouts, and fields, or even a text input. What matters is that users can specify the table/layout they want to search in and the fields they want to search on.

Another design decision is how the search will be saved. The demo file uses three tables, QUERY_BUILDER, REQUEST, and SEARCH_FIELD. A record in Query Builder corresponds to an overall search, a record in Request corresponds to a single Find Mode request, and a record in Search Field corresponds to a single field to search on. A query builder can have many requests, and a request can have many fields. As we add requests and search fields, field calculations run to convert our FileMaker data into the search JSON. This implementation has the benefit of a clean UI and sensible abstraction, at the cost of small database clutter.

For a more lightweight solution, you could have the user build the find with global input variables, and then generate and store the search JSON by pressing a button. This solution could potentially require no new tables, but it would get complicated if a user wished to edit an existing search JSON.

Other Considerations

The actual scripting logic behind the generation and execution of a search JSON is simple. The find loop in the demo file is only 40 lines long:

The far greater difficulty comes when making the feature more robust. For instance, the demo file allows the user to specify the desired layout for a particular find. However, the demo file has both the dev layout "INVOICES" and the user layout "Invoices." The script step Go to Layout by Name is not case sensitive, so we must use Go to Layout by ID instead.

Another dilemma can arise when considering data encapsulation. When building a search on a specific table, should your user only be able to choose fields from that table? Ideally, yes. But making the feature more robust also adds more work for your developers. The demo file demonstrates a possible implementation of this encapsulation, but it also shows how tricky said implementation can be.

There is no free lunch, so to speak. The easier you make things for the users, the more complex the feature becomes. However, in a large system with many users, layouts, fields, and searches, this time investment will certainly be worth it.

Conclusion

Good development takes your irksome, repetitive tasks and simplifies them down into a single button. This feature does that for complex finds. Hopefully this article and the accompanying demo file have given you some ideas on implementing dynamic query building and why it can be a time-saver.

If you have any questions about implementing this query builder into your FileMaker solution, please reach out to us at DB Services and we would be happy to help.