Part 1 ( Search, the "FileMaker Way" )
The Problem: Attack of the (search) Clones
Ever get tired of writing multiple FileMaker scripts over and over again whose only purpose is to automate different searches on different layouts, when the only difference between one search and another (besides your search terms of course) is the context?
In other words, do you find yourself duplicating and modifying search scripts, or duplicating search functions (script steps) within your larger scripts, each and every time you want to run a query on a different layout, or on a different set of fields, or using different search terms? Don't you wish there were an easier way?
Wouldn't it be great if there were a "reusable" search script, where all you would need to do is dynamically tell it which layout to search, which fields to search on, and what search terms to use and it would just work? Wouldn't it be great if it worked no matter which FileMaker file you pasted it into?
Well, just like you, we also grew tired of the endless search scripts, so we think we've found a great way around that. We're calling it "Universal Search". It's designed to be a modular search script, and we think it is the search script to end all search scripts (well, at least most of them).
When your database is hosted on FileMaker Server ( version 13 or later") Universal Search" even works as a Remote Procedure Call (using "Perform Script On Server") . When executed on the server, it runs like lightning, there is no progress bar for users to see, and it will bring back to you whatever search results you're interested in.
The Solution: "Use the (parameter) Force"
So the solution turned out to be fairly simple. No ExecuteSQL required (but you can apply it if you want to, more on that in Part 2 ). It turns out that we were able to construct a highly flexible, resusable search script through a combination of script parameters, local script variables, and the "Set Field By Name" script step. And when we added the "Evaluate" calculation and the "Get ( ScriptResult )" script step, we found we had assembled some incredibly versatile search code. To reuse it in a different context, simply feed it different information. Let's take a look.
Example:
Say you're looking for all the Female members of a company called "Fermentum" in your contacts database. Simply type your request in "Universal Search" format:
"LayoutName" = name of the layout to search from (context).
"PSoS" = Perform Script On Server ( True or False )
"bounceBack" = return to the original layout or remain on the search layout ( True or False )
"Request" = List of field names and search terms for each separate search, including whether the request is to "Omit" certain records ( "omitFlag") or include them in your search.
"Scriptresult" = Calculation to be evaluated by "Universal Script" which will return whatever information you're interested in from your search results.
Here's an example of the search we're talking about:
#Array ( "LayoutName" ; 1 ; "Contacts" ) &
#Array ( "PSoS" ; 1 ; False ) &
#Array ( "bounceBack" ; 1 ; False ) &
#Array ( "Request" ; 1 ;
"fieldnamesList: " &
GetFieldName ( Contacts::Company ) & ¶ &
GetFieldName ( Contacts::Gender ) & "; " &
"fieldcontentList: ==Fermentum¶Female; " ) &
#Array ( "Request" ; 2 ;
"omitFlag: True; " &
"fieldnamesList: " &
GetFieldName ( Contacts::PrimaryLanguage ) & ¶ &
GetFieldName ( Contacts::Address_State ) &
"; " &
"fieldcontentList: Japanese¶AR; " ) &
#Array ( "Scriptresult" ; 1 ; "GetFieldList ( Contacts::Name_Full_c ; 1 ; Get ( FoundCount ) )" )
Once again, but this time in English
1. Search the "Contacts" table by navigating to the layout "Contacts"
2. Run the search locally on my device ( do not offload my search to the server )
3. Don't revert back to the original screen that I started from (stay in "Contacts")
4. "Find me all the records from "Contacts" table where "Company" matches "Fermentum" (exactly) and where the contact's gender is "Female."
5. But, omit (exclude) any contact whose primary language is "Japanese" and whose "Address_State" is in "AR"
That's it, it's that simple, the best part is you can use the same script over and over again and all you have to change is the search rules.
Use cases
"Universal Search" can be used both for user-facing searches, where you as a developer wish to send the user to a dynamically specified set of records, or for back-end searches which return results either as a found set of records in a hidden layout that you use for your developer operations, or as "Script Result" data (text) for you to use elsewhere.
"Universal Search" is particularly useful for offloading search and update operations to your FileMaker Server (using "Perform Script on Server").
In this blog post, we covered how to use "Universal Search" to create "FileMaker Style" searches. On our next Blog post: "Universal Search Part 2, (the "SeQueL") we will cover how to use this search module to search "the SQL way".
DOWNLOAD
You can download "Universal Search" from modularfilemaker.org by clicking here.
The geeky fine print
Universal Search relies on a few custom functions (5 to be exact), all but one from filemakerstandards.org. It uses the "#Array"custom function to define a dictionary of name value pairs.
Then "#ArrayGet" and "DeclareVariables" parse the dictionary into locally-scoped {$} script variables.
"#Array" and "#ArrayGet" have dependencies on two additional custom functions: "#" and "#Get" which also need to be loaded.
The array dictionary may be supplied to "Universal Search" using either a script parameter or a field that the script "Universal_Search_Handler" can capture upon execution.
Use the syntax from "#Array" Custom Function "{#Array ( name ; index ; value }" to declare all top level variables.
Required nested arrays are:
"fieldNamesList" = List of field names in format ["TableName::Field_Name"]
"fieldContentList" = List of search terms as strings, including any search operators
"omitFlag" = Optional variable, to specify if the request is an Omit (any value will do)
For all nested arrays, follow NightWing's syntax {"Parameter_Name: " & Parameter_Value(s) & "; "}.