"AND" and "OR" search question

[kfok]

My database has three value list fields which are check box type. Each fields contains four to five values. For instance,

Field 1 contains value list of A1, A2, A3, A4, A5

Field 2 contains value list of B1, B2, B3, B4

Field 3 contains value list of C1, C2, C3, C4, C5

I would like users to be able to find records using both "And" and "Or"

criteria on one search. So another field with "And" and "Or" selections will be added for users to choose for each value list field.

An example of the search is:

A1 or A2 or A3

B1 and B3

C2 and C5

Is it possible to write a script to perform the search?

[BobWeaver]

All of the AND criteria need to go into one find request. When you do an OR search each criterion goes into a separate find request.

So for your example,

((A1 or A2 or A3) and (B1 and B3) and (C2 and C5) )

is logically equivalent to

((A1 and (B1 and B3) and (C2 and C5)) or

(A2 and (B1 and B3) and (C2 and C5)) or

(A3 and (B1 and B3) and (C2 and C5)) )

So you would have 3 find requests:

Request 1: Field 1="A1", Field 2= "B1 B3", Field 3="C2 C5"

Request 2: Field 1="A2", Field 2= "B1 B3", Field 3="C2 C5"

Request 3: Field 1="A3", Field 2= "B1 B3", Field 3="C2 C5"

[kfok]

Thanks for your input. Do you know how to script the finding criteria for general use?

[BobWeaver]

If you mean a way that would convert an AND/OR search, like in your example, into the equivalent Filemaker find requests, that's a very good question. And, I don't really have a good answer. I've never tried to do it except for a special case which I will describe below. Filemaker's find request format has always been intended to be easy to use by non programming savvy users, which makes it annoying for people with SQL or programming background who think in boolean logic terms. So, generally, developers will set up some standard types of find scripts tailored to the kind of searches they expect their users to do. Having said that, if you want to do some serious programming to parse a boolean type search formula, it should be possible, but it's not something that I would approach casually.

As I mentioned above there is a quick and dirty workaround, but it performs an unindexed search which could be quite slow if you have a lot of records. Essentially, you create a global field called gSearchCriteria where the user enters a boolean type formula (producing a 0 or 1 depending whether the record matches or not). Then you create an unstored calculation field called cSearchMatch with the formula:

Evaluate(gSearchCriteria)

Then to do the search, just search in the cSearchMatch field for a value of 1.