Finding a Record with Multiple Criteria

[Phrogz]

I'm frustrated and baffled. I have a test DB with the following data:

bar   foo   value

1      dog  77

2      dog  11

1      jim   33

1      dog  88

2      dog  99

This script works:

show (every record whose cell "bar" = 1 and cell "foo" = "dog" )

correctly showing the values 77 and 88



This script works:

show (every record whose cell "value" > 80 and cell "foo" = "dog")

correctly showing the values 88 and 99



BUT, this script (adding a third criterion) fails horribly:

show (every record whose cell "foo" = "dog" and cell "bar" = 1 and cell "value" > 80)

showing the values 77 and 88.

Repeated tests in this area show me that Filemaker only honors the first two criteria, and blatantly ignores the rest!

SO...how can I do the above? I need the search values to be specified by the AppleScript. In the end, I don't even want to see the values...I really need to calculate the total of a set of records found by values in four different cells.

I know how to do this in SQL, but controlling FileMaker is driving me bonkers. I'd give up, except that this is a legacy database with 28,000 financial records that are very important to my mother.

Your help would be greatly appreciated. I figure that using AppleScript to control FileMaker's own 'scripts' for finding records may be the way to go, but I can't figure it out.

[aaa]

On my PC it works correctly - shows only 88

[Phrogz]

...I'm confused. AppleScript works on the PC?

[Søren Dyhr]

So what you tend to do is a AND'ed criteria, what you need to know is that the identifiers database and document references the entire table and the found set respectively, so if your compilation of the script makes a fuzz about more than two criterias in a line, stack them instead:

show (every record whose cell "value" > 80 )

show (every record of document 1 whose cell "foo" = "dog")

show (every record of document 1 whose cell "bar" = 1)

An alternative is to write every cliteria in request 1 and perform a native find.

But why bother doing it in applescript, say if no field is used twice in the query use Set Field in the request on the other hand if you wish to enter several values in the same field, have there since FM6 been an ability to "Constrain Found Set"

--sd