If you have ever wanted to search a large amount of records either by Name or Category, or other fields, there is a somewhat simple solution which uses an auto-calculated text field, which is used as a foreign key multi-value list and global fields to perform a relationship match.
You only need 1 table to perform this type of operation.
1. Define the fields
Let us say we have a table of companies which has the following fields:
To this, we add a fourth field which is set to auto-enter a calculation as follows:
"_all" & If( not IsEmpty( category ) ; ¶ & "_cat_" & category ) & If( not IsEmpty( name ) ; ¶ & Left( name ; 1 ) & ¶ & Left( name ; 2 ) & ¶ & Left( name ; 3 ) & ¶ & Left( name ; 4 ) & ¶ & Left( name ; 5 ) )
What this gives us as a result when the name is “ABC Co.” and the category is “Phone Companies” is this:
_all _cat_Phone Companies A AB ABC ABC ABC C
In other words, we use the keyword “_all” with an underscore before it to avoid confusing it with any company beginning with “all”. That way, if we want to match any company, we would match against “_all” as the keyword.
The same idea applies with “_cat”.
And then, we enter up to the first 5 characters of any name so that we can search for up to 5 characters.
This field needs to be indexed with minimal indexing for it to work in our relationship we will create.
Next, we create some globals:
We use capitals to make them stand out as globals. Be sure to set them as globals. Naming them with capitals is not enough. We are following the standards as provided at FileMaker Coding Standards.
We then add an auto-enter calculation to the SEARCHFILTER field as follows:
Case( SEARCHOPTION = "all" ; "_all" ; SEARCHOPTION = "category" ; "_cat_" & SEARCHCAT ; SEARCHOPTION = "name" ; SEARCHNAME ; "" )
NOTE: IN BOTH AUTO-ENTER CALCULATIONS, MAKE SURE YOU UNCHECK DO NOT REPLACE EXISTING VALUE OF FIELD (IF ANY)
2. The next step is to set up your relationships and layouts.
Take a look at this sample file to see the rest.