How to do advanced search combining "AND" & "OR"?

[cxwang]

Hi, I need to add advanced search features in FMP, for example, there are 5 fields I want to put search criteria, there are "AND" and "OR" in each field.

Field1: State

Field2: Sex

Field3: Grade

Field4: Membership

Field5: Profession

I use checkboxes to display all the possible choices for each field. How do I make a script to search for people satisfying, say, the following criteria:

From State 1 OR State 2 OR State 3, AND

Male, AND

In Grade 1 OR Grade 2, AND

Member OR nonmember, AND

Civil Engineer OR Architects

I know how to use script to search multiple selections for a single field (counting the carriage return signs between the checkbox selections...), but don't know how to combine it with more search field.

Can I save a found set and then do find on top of this subset?

Any help greatly appreciated!

[LiveOak]

AND searches populate multiple field in the same find request. OR searches add more find requests.

-bd

[kennedy]

And an implication of that is that you have to convert your intended search into OR-AND normalized form:

((field1 = X) AND (field2 = Y) AND ...) OR ((field4 = Z) AND (field6 = W) AND ...) OR ...

which can get ugly. In your case it becomes 12 requests:

(State1, Male, Grade1, Civil) OR (State1, Male, Grade1, Architect)

OR (State1, Male, Grade2, Civil) OR (State1, Male, Grade2, Architect)

OR (State2, Male, Grade1, Civil) OR (State2, Male, Grade1, Architect)

OR (State2, Male, Grade2, Civil) OR (State2, Male, Grade2, Architect)

OR (State3, Male, Grade1, Civil) OR (State3, Male, Grade1, Architect)

OR (State3, Male, Grade2, Civil) OR (State3, Male, Grade2, Architect)

assuming there is only "Members" and "Nonmembers". If there is something else,

then you'll need to repeat the above requests once for members and once for nonmembers (24 requests).

That can get very clumsy. Given they handle <, >, and so on in fields in Find mode,

why don't they handle "OR" or a comma list?

This is where Number fields can be real nice; for example, if the Grade field is just a number, you can specify "1...2", shortening the above to:

(State1, Male, 1...2, Civil) OR (State1, Male, 1..2, Architect)

OR (State2, Male, 1...2, Civil) OR (State2, Male, 1...2, Architect)

OR (State3, Male, 1...2, Civil) OR (State3, Male, 1...2, Architect)

Am I missing any better ways to shorten this up?

[cxwang]

Thank you for the response, LiveOak and Kennedy.

What I am trying to do is avoid the conversion, as totally there are 7 states, 4 grades, about 10 professions... (or I need a program to do the conversion)

Can I do "OR" first, save the found set, and then do "AND" ?

If someone can definitely tell me this is a task FileMaker Pro can not do, I 'll drop this idea, otherwise I am obsessed with it.

[SteveB]

If you can define what your search requests will be (as opposed to varying every time the user runs the system), you can make your life easier by defining an unstored calculation that combines your fields with logical operators. Have the calc return a value of '1' if the expression results in true, or '0' if not and then just do a search on the calc. Just make sure the calc is unstored and indexed.

[kennedy]

Thinking out loud, using those old hardware design classes...

Given (A | & (C | D) & (E | F), there's two ways to get to OR normal form:

ACE | ACF | ADE | ADF | BCE | BCF | BDE | BDF

which is what we want to avoid (8 Find requests on 3 fields, with lots of redundant specs), ** OR **:

(A'B' | C'D' | E'F')'

The outer part of that is easy... do the find three requests, each on two fields, and then Show Omitted. The trick is how to do A' and B', etc., and that will depend upon the nature of your fields. If FMP offered decent regular expressions, you could do it easily for most things. But with the wimpy stuff FMP offers, you have to get lucky. Some things are easy (T/F, Male/Female, Exists/Not Exists); some things are ugly but possible (33 vs. >33 or <33); some are lucky (U matching all universities in the list except Boston College; @@@@* getting all but the one three letter word); others are just not workable.

But, in any case, such conversions are clearly not the stuff for users... this will only be for canned search requests, as Steve mentions. But if canned works, this technique may help simplify some of your finds. But then again, if canned works, you can do as Steve suggests, and use real logical expressions.

If you need a more generic solution, you might need to add a "Mark" field and "Mark All Found" and "Unmark all Found" commands... allow your users to do the searches one at a time, and apply or remove marks as appropriate for them. Some users will be able to do fancy stuff; others moderate stuff; some only simplistic stuff... but all will understand the mechanism.

HTH.

[cxwang]

Thanks again for the good ideas, I would like to have a generic solution, thus will try the way Kennedy suggested: adding a "Mark" field. I will keep you posted.

More comments and suggestions?

[Vaughan]

Any soultion that relies on mark fields I likely to screw up big time in multi-user. Is your solution going to be used my more than one user at a time?

[BobWeaver]

If you are using FM version 6, then the ability to constrain the current found set gives you a lot more power to do complex searches. Doing successive finds with the constrain option effectively gives you the ability to do the following:

((A or And (C or D))

plus quite a bit more.

[kennedy]

If you are using FM version 6, then the ability to constrain the current found set gives you a lot more power to do complex searches. Doing successive finds with the constrain option effectively gives you the ability to do the following:

((A or And (C or D)) plus quite a bit more.

Oh, very cool! I had not seen that... that's the first thing I've heard that makes me want to upgrade from 5.5 to 6.0. And that's definitely the right solution to this thread.

[cxwang]

Hi, I am so happy to know that version 6 can do this! Thank you, Bob(I am using Version 5.5).

Also, Kennedy, I have implemented your idea and it works, for up to 3 fields now, I am sure the same for 5 fields. I was excited about this before I read Bob's message.

Vaughan, thank you for your help, I sent you emails to ask silly questions just to avoid showing my ignorance here and to get instance response. Did you say that we can get a free upgrade to 6.0 through ITD ?

[Vaughan]

Phone x2222 and somebody will take it from there.