Business rules inevitably evolve over time. When they do, it’s important that FileMaker users are able to adjust their application quickly and easily to keep up. The best way to do this is to allow users to modify and define what information FileMaker is presenting them with and how it should be presented using business logic. Let’s demonstrate how to enable users to define business rules.
When To Use User-Defined Logic
One use case for user-defined business logic is to give users a way to specify what information they want to be synced between a local copy of a file and a hosted version of the same file. In a file with a lot of tables, it may not be necessary to sync data between the two for every table. However, as the system grows – either through the addition of new tables or the necessity to sync data from previously excluded tables – it becomes difficult to add logic to scripts to account for such changes.
Application
To demo this functionality, we will use a FileMaker application for entering order information. You can download the demo file (located at the bottom of this article) and follow along.
Click on the plus sign in the top left corner of the tab control. This brings up a card window that will allow users to select which layouts to include.
In the card window, click the refresh icon in the top right to initialize the layout selector with the layouts currently found in the file. Click on the layout you want to include. In this case, we want to use “order”. It should already be selected and will appear in the Sync Layouts tab.
The next step is to select the primary key for that layout. This field is a dropdown list that works off a value list. The value list is a calculation field that is set to a global variable.
The value for this global variable is defined in a script parameter that is a script trigger on the primary key selector field. The script itself does nothing, but the script parameter is a let statement that defines the global variable we use for the value list, so it will update dynamically as we click into each portal row.
Clicking into the limit criteria box brings up a card window allowing the user to define the logic that the system will use to limit the return data during syncs or, in the case of this demo, finds.
**Important notes about the limit criteria: Users are only able to select fields that exist on the layout. They can also type into the box instead of using the insert drop-downs if they prefer.
The two drop-downs to the left of the edit box contain a list of operators that users can add to modify the query and the list of fields they can select from. After adding their logic, users can view what the JSON data will look like for their query by pressing the preview query button. The run query button is for demonstration purposes and simply runs a find with the given parameters.
How the Logic Works
This solution uses escape characters to denote items as fields or functions and uses Evaluate to determine the function’s value at runtime. To parse the users specified criteria, there is a calculation that builds the JSON data block using a while loop. “[ ]” are used to encapsulate a field name if it has spaces or underscores, “#{ }” is used to wrap a function that needs to be evaluated, “” is used to perform OR requests. “Omit” marks that section of the query as an omit section.
We then use a custom function to evaluate any functions before performing the query.
Conclusion
User-defined logic is a powerful tool that enables end users to adapt quickly to ever-changing markets and rules. This functionality also allows your system to be much more flexible and will lead to less time spent updating scripts to add in new criteria, enabling you to manage and view these settings from one layout. These settings can be used to send sync queries to a hosted app with the data API or, as we demonstrated in this article, to perform dynamic finds.
