FileMaker record searching

Folks,

I’ve got a problem that I’m hoping someone else has run across with AppleScript and FileMaker 6.

When having a table that contains a field called state which contains values like “Virginia” and “West Virginia”, doing whose cell “State” = “Virginia”, i.e.


set recordSet to  (every record of database "states" whose cell "State Name" = "Virginia" or cell "Variant Region Name" = "Virginia") as list

returns both the Virginia and West Virginia record.

I tried =, is, and equals (is equal to). All return the same result.

Does anyone know how to force FileMaker to do an actual equals and not a contains when selecting records from a FileMaker database with AppleScript?

Thanks,
Jack

If you take out:

or cell “Variant Region Name” = “Virginia”

what results do you get?

Jon

I’m getting the same results. Very strange. It also doesn’t seem to matter about white space around the comparison term but substrings don’t work. That’s a stumper… To work around it, although not as quick or elegant, you can use:

Jon

Jon,

I think I know where you’re going. It produces the same result. (The variant field doesn’t contain a value of Virginia in the West Virginia record. I checked that before I posted this.)

The issue is that “is equal to” produces the same results as using “contains” which to me [from programming in a number of other languages] should not be the same thing.

Thanks,
Jack

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.

Thanks Camelot.

This is exactly what I needed to know. I appreciate all the source code examples. I found that after “find window 1” that I could simply examine the current record to see if the search was successful.

Appreciate it!