Telling Filemaker Pro to get every record where a field “is” or “equals” a value doesn’t work due to Filemaker’s interpretation of the search string.
If you perform a manual ‘Find’ in Filemaker, “=Virginia” searches for “Virginia” in the field contents. To do an exact match search you have to search for “==Virginia”.
Unfortunately, there’s no equivalent in AppleScript. Telling Filemaker to get every record whose cell “state” is “Virginia” performs a “=Virginia” search which returns multiple records.
Instead, you have to dig deeper into Filemaker’s find function.
Find uses a series of ‘request’ objects, each one of which defines a series of named cells and search criteria to search on. In this case, the solution is to set the first request to “==Virginia” and then perform a find:
tell application "FileMaker Pro"
tell database 1
set cell "state name" of request 1 to "==Virginia"
find window 1
end tell
end tell
As obscure as that looks, it will perform an exact match lookup on the field “state name” matching “Virginia”
You can create multiple ‘and’ criteria by specifying multiple fields, for example, to find all “New” states with populations of more than 1 million people:
tell application "FileMaker Pro"
tell database 1
set cell "state name" of request 1 to "=New" -- 'state name' *contains* "New"
set cell "population" of request 1 to ">1000000" -- more than 1 million in field 'population'
find window 1
end tell
end tell
‘or’ searches can be performed by creating multiple ‘requests’. For example, to find all “New” states OR states with populations of more than 1 million:
tell application "FileMaker Pro"
tell database 1
set cell "state name" of request 1 to "=New" -- 'state name' *contains* "New"
set cell "population" of request 2 to ">1000000" -- more than 1 million in field 'population'
find window 1
end tell
end tell
Note that since the population is set for request 2, this becomes an additional search criteria.