Multiple search criteria in multiple fields

You certainly can do that.  It's just a matter of scripting and performing the find based on how you parse the request.  

Another technique that you might consider, if you've only three fields is use the "Show Custom Dialog" script step.  If you look at that script step, there's a tab which allows you to enter values to three different fields.  An easy example to visualize is a zip-code lookup, where the three fields might be Zip, City, and State.  Base your find on whichever fields are filled in.  It's pretty slick.

Thank you for your post. The idea with the Custom Dialog doesn't work for me, as often I have more than three fields.

To be a little more precise: What I am looking for is: It doesn't matter in which of the checked fields kath and kim appear, but altogether both kath and kim need to appear at least once. So, the records I am looking for have kath and kim in the following positions:

Name=(kath AND kim)   OR
Details=(kath AND kim)   OR
(Name=kath AND Details=kim)   OR
(Name=kim AND Details=kath)

So, how could scripting and performing the find look like?

Your script would run whichever of these you've isolated out.  Add more as your recipe warrents.
.
.
.
Perform Find ... Find Records table::name:$name1
Extend Found Set ... Find Records table::name:$name2
:
Perform Find ... Find Records table::details:$name1
Extend Found Set ... Find Records table::details:$name2
:
Perform Find ... Find Records table::name:$name1
Extend Found Set ... Find Records table::details:$name2
:
Perform Find ... Find Records table::name:$name2
Extend Found Set ... Find Records table::details:$name1

How you decide which of these search sets to run is optional.  You only have two fields here, but it sounds like you could have many more.  One method might be to have a labeled box for each type of search, so (in your example) you could have one dummy global field for name and another for details.  You'd know what to search for because they wouldn't be empty.  If you've quite a large number of fields you could use a check-box to have them indicate which type of search to use.  Or, maybe the simplest way is to put them into Find mode and keep extending the find until they've got exactly what they're looking for.  The nice thing about the last method is that it offers the most diversity and handles its own error codes.

Thank you for your post!

I like the way you cut the search string into pieces and put them into the desired combinations, then extending the found set; but I'm afraid this works only with very few search terms and fields. Having 3 terms and 2 fields creates already 8 possible combinations where a search needs to be performed, having 4 terms and 2 fields comes to 16 possible combinations to think of and so on.

So, it would be necessary to have a calculation that creates the possible combinations automatically, based on the number of search terms and checked fields.

In addition, this kind of not stored calculation field cannot not be indexed; so the search would go slow.

Or am I missing something?

I would make a serie of "if" to generate the find lines.
principle
find mode
set field1 = criteria
if 2nd field is on
new record
set field2 = criteria
if 3rd field is on
new record
set field3 = criteria
etc...
execute find

that will make search criteria in field1 or field2 or field3, etc...

Hey lesouef,

Thank you for your post. The idea of adding new requests based on if is a good one; but as I want my users to use multiple search terms I still need to add different calculation fields. Let's say, you search for both term1 and term2 in field1 and field2. As it does not matter in which field the terms appear, I have to create a search like:

like D0N already suggested in message ID 22462538. So, the search I am trying to perform is an OR search that searches all the search terms in multiple fields at the same time, instead of searching all the terms in one search field, then another one, then another one,...

can also be done this way.
make a loop which create as many find requests as terms for each field, by stripping the terms field word by word

Stripping the terms field is not a big problem. Let's say, after stripping the terms field word by word, we have 3 search terms and 4 search fields. The records I am looking for have all the 3 search terms in the 4 search fields, and it does not matter in which of the search fields they appear, and they don't need to be in the same search field. For this I need to put fields together first, or not?

that ends up as 12 'or' requests.
for each field, you do a loop for each term.

Hey lesouef,

Thank you for your answer! Please correct me if I am wrong, but with the above mentioned conditions I think I would receive 4^3=64 'OR' requests, not 4*3.

I don't think so, you don't search all combinations, but only terms in fields, so unless I am wrong,
the among of searches is the amount of terms * amount of fields to be searched

Well, but I think I need to go to 'combinations level'. Let's use a simpler example with search terms A, B, C and search fields 1 and 2. As above mentioned, I need to find records that have all the search terms, no matter in what fields. The records that I want would look like:

(Position of letters within the field and "_" are just for reasons of easier viewing.)

So, here I have 8 combinations; adding one more search term ("D") would double the number of combinations that include each search term atleast once:

1:____  2:ABCD
1:___D  2:ABC_
 1:A___  2:_BCD
1:A__D  2:_BC_
 1:AB__  2:__CD
1:AB_D  2:__C_
 1:ABC_  2:___D
1:ABCD  2:____
 1:AC__  2:_B_D
1:AC_D  2:_B__
 1:_B__  2:A_CD
1:_B_D  2:A_C_
 1:_BC_  2:A__D
1:_BCD  2:A___
 1:__C_  2:AB_D
1:__CD  2:AB__

The second example results in 16 combinations.

you don't need to make these combinations, searching ABCD does not make sense after searching A or B or C or D, you won't get more records...

Yes, but I need records that don't just hold A or B or C or D; I need those records that hold both A and B and C and D. With the above shown combinations I intend to exclude those records where one or more terms are missing.

I understand, but when you query records containing A or B or C or D, records containing A and B and C and D will be included in the foundset anyway.
so what you say makes sense only if you want to get records containing A and B and C and D, separately from others. Am I clear?

Thank you very much for your answer. Well, honestly, I am not sure if we lost each other somewhere. Let me give you a more detailed, practical example:

Let's say there is a database with a table about books that contains the field BookTitle. Then we have a table called chapters with a field called ChapterTitle, and the tables books and chapters are related; e.g. the record with BookTitle="The really cool Backpacking Book" would be related to a record with ChapterTitle="Dressing and Packing", a record with ChapterTitle="Equipment", a record with ChapterTitle="Outdoor Hazards" and so on. Furthermore, we have a search field cSearchBookTitleAndChapterTitle = BookTitle & " " & ChapterTitle. So, a user who searches in this field for backpacking equipment, would get as a result this book. Another book with BookTitle="Sailing" and a ChapterTitle="Important Equipment" would not be a part of the found set, as all the search terms need to be found in BookTitle and/or ChaptersTitle.

Still agree with ... me! The best proof is probably to try both ways.

Well, just tried it with a simple table, and tried it with the solution I am working on...: Using a simple OR search retrieves records that do not include all the search terms. However, in my found set I need only those records that include all the search terms.

now, I think I understand, you wanna get only records with all terms in any of the 3 fields.
in this case, it is even simpler, just a query per field using all terms in a single expression, so that's only 1 search line per field, not 64.

Hey and Thanks. If I understand you correctly, using just one query per field using all terms in a single expression would be something like

So, both A and B and C and D would alltogether appear together in either Field1 or Field2 or Field3.

Then, however, I would miss out a combination like

Or not?

ok!!!!!!!!!! I am with you!!!!!!!!!!!!!
you wanna find all 4 terms in any of the 3 fields....... provided than all 4 are found in the same record...
gosh.... then you're right, you need to make up all combinations, hell of a work...
I think I would make an extra field (auto-entered, from calc) = field1 & field2 & field3
for existing records, do a replace to populate it.
and search 'term1 & " " & term2 & " " & term3 & " " & term4' in it
period.
what d'u think?

Your suggestion is what I had in mind when I asked the question (see above), making a calc field and searching for all terms in the chosen fields.

Now, here comes the actual problem: Creating all those calc fields is

a) a hell of a work, I definitely agree with you, and

b) leaves the database with a huge load of calc fields that would make the database gigantic.

So, the interesting part now is: Is there a way to avoid the "classical" calc field and / or is there a way to automatize the process of creating the needed combinations?

b > it would only be one calc field; that big? if it is a stored one, it won't slow down the search
yes, you can make a big script which does all combinations at once, and just ignores empty terms and unckeched fields...

One calc field only wouldn't be a problem. But, don't I need to make different calc fields for the different combinations? Let's say, the user choses Field1 and Field2 to search, so I need a calc field cSearchInField1andField2= Field1 & Field2. When he choses Field1 and Field3, he needs a calc field cSearchInField1andField3= Field1 & Field3.

you can do that by using evaluate (field1 & field2...)
so when the user checks field1 and 3, you define the field_to_be_used_for_searching as evaluate (field1 & field3).
of course, this has to be recal-ed at every search, not fast...
but I really can't see why you need such a complex search function; searching in all fields should be enough??

Hi lesoeuef,

Thank you for your answer, again.

Well, what do you think of the following idea. It looks like this is the answer to my question. Instaed of defining a whole bunch of calculation fields we simply do as followed:

1. We cut the search terms in gEnterYourSearchTermsHere into pieces, i.e. we make a list of values where each word is a value.

2. We take the first search term of the list and do a multiple request search. We search it in SearchField1 OR SearchField2 OR SearchField4 OR ... and end this step with Perform Find.

3. Then we take the second search term and do what we did with the first search term, a multiple request in all the fields that need to be searched. However, this step we end with Constrain Found Set.

We keep on doing this until the last search term was being searched in all the chosen search fields, et voilà: We get exactly what we were looking for, a set of records that contain all search terms no matter in which field they appear. And not a single calculation field needs to be created.

What do you think of that?

it should be slightly slower, but much easier to realize.
putting all terms in the same field is not a key point, you can easily work it out anyway.
the progressive approach with "constrain" avoids the combinations setup indeed.
I use it from time to time, just be care that some records are left in between each step, that will make it a bit faster.

Thanks again for your input... looks like we finally found a solution...

What dou you mean with "just be care that some records are left in between each step, that will make it a bit faster"?

Thanks for your help (and additional info).