This seems to be a common problem and I found no clear solution: getting values of fields in a FileMaker database using whose clauses.
Unfortunately there are a number of pitfalls:
records whose cellvalue of cell "MyField" is "somevalue"
Although this works without needing a specific layout or the database being the frontmost:
is very slow;
is case sensitive, at least in some versions;
raises an error if not found (-1728, “Object not found.”)
According to AppleScript’s manual “The value of a filter reference form is a list of the objects that pass the test. If no objects pass the test, thelist is an empty list: {}.”
Well, at least cellvalue returns a list if only one is found.
A much faster (hundreds of times faster) solution is:
records whose cell "MyField" is "somevalue"
But…
Fails with an error (-1728, “Event not handled.”) if:
The database is not the frontmost;
The current layout does not contain the queried field(s)
The query fails.
Yes, same error for any of these situations!
If it finds only one occurence it does not return a list of one element.
So, here comes a suggestion, respecting the expected result and all:
tell myDocument
show layout "A Layout with my fields"
end tell
tell myTable
if (count of records) = 0 then return {}
-- believe me!
try
set x to {}
set x to exists (records whose ¬
cell "theID" begins with theId)
-- error -1708, "Event not handled."
set x to {} & ( ID of records whose ¬
cell "theID" begins with theId)
-- error -1728, "Event not handled."
set x to {} & (cell "Another field" of records whose ¬
cell "theID" begins with theId)
-- error -1728, "Object not found."
on error number -1728 -- or -1708
-- do nothing
end try
end tell
Oh! And forget about first record, some record. You will always get them all!
Any flaws or pointers to more elegant solutions are very welcome.
