You create a New Record for each search value you need
Search Record 1
Field Name searching for Johan
Search Record 2
Field Name searching for Martin
Search Record 4
Field Name searching for Louise
Thanks for the reply Johan, but that gives loads of unwanted records.
In my example i would also get every COMPUTER KEYBOARD and COMPUTER CABLE.
The query should check for two text strings that should appear in one and the same field.
With "*" in your search you can get more information then just form one word
Say I want to find all articles that starts with COMPUTER, then I just write computer. If I want to find a word in the field that is exactly COMPUTER then I set =COMPUTER and it will only find articles with Uppercase COMPUTER.
If you need to group your records it is much better if you create a new field called Group and in that group you have DISPLAY / KEYBOARD / MOUSE or whatever. This will give you a possibility to search for all that have to do with DISPLAY
The basic principle would be something like:
Enter Find Mode
Set Field: ArticleDescription; "Computer"
Set Field: ArticleDescription; "Display"
Constrain Found Set
You can do this either manually or in a script. If you use a script, you may want to add a loop to handle searching on any number of words, plus of course some error handling etc.
Yes in my example that would work because article names are quite unique. But we need it for a large user database
where people fill in their non uniform job title. E.g. we found 16 synonyms for what is- or could be a developer - programmer- software engineer - consultant etc etc). So if I want all to find all the senior developers..
(8 synonyms SR- SENIOR - LEAD -MANAGER - HEAD etc) .. 16*8.... see the problem?
Best would be 2 tables and a script with a loop I think but I don't know how to do that properly.
Is this the best way to go?
- add a field as a flag for "found by script"
- define 2 tables with search terms that need to be found in combination in one field
- load value 1 of table A
- start loop
- search for A1
- constrain found set
- load value 1 of table B
- search for B1
- for all records found: set flag to Y
Because of the CONSTRAIN FOUND SET I assume i can't use nested loops,
so after every loop cycle SHOW ALL RECORDS?
Or I could use a second temporarily flag to be able to go back to the previously found set for A1?
But it is as much work I guess - no gain in there.
- Enter Find mode
- Type into the field ArticleDescription: COMPUTER MONITOR
- Perform find
This should find any records with both words "COMPUTER" and "MONITOR" in the field Article Description. The words must be present, but may be in any order. The field may contain more words. You will not find any records without any of the to words you were searching for.
These are examples of records that will match: "COMPUTER MONITOR", "MONITOR COMPUTER", "COMPUTER LCD MONITOR", "COMPUTER AIDED MONITORING".
These records will not match your find: "COMPUTER", "LCD MONITOR".
Note: If you type "COMPUTER MONITOR" (with quotes) in your find request, then you are matching for the phrase "COMPUTER MONITOR". This request will not find "COMPUTER LCD MONITOR".
A very interesting problem you have there. I would love to solve it for you. Unfortunately my boss thinks I should solve his problem during work hours. So I come back to your problem at the weekend.
If you could supply those list of 16+8 words and some sample data to search in, it would help a lot.
With best regards Magnus Fransson.
great ideas on constrain.
However, let's start with the way FileMaker finds work:
1. 'begins-with' is the default, so any find on any part of a word in a field is at the beginning of the word anywhere in the field (not just the first word)
'DA' finds: "database", "meta-data" (because the dash is a word separator) & "data"
2. use of the insert-from-index on a field shows you the words in a field, if that helps
3. searches are case-insensitive
'joe' finds: "Joe", "joe", "JOE"
4. there are several "wildcards" (operators/symbols) that change the find
5. 'anywhere in the field' and the use of more than one word in search criteria will find if ALL of the words match, but in any order
'joe brown' finds records with these in the field: "Joe Brown", "Browning, Joella" and "Ann and Joe have a brown puppy"
this is an AND find in a single field
'computer display' would have found all your records with these in the field searched: "COMPUTER LCD DISPLAY", "the display for computers had...", "COMPUTER FLAT PANEL DISPLAY"
multiple fields AND search is same record, different search
6. an OR search (_any_ of the words) is where you use the multiple find requests (as you found)
7. omits are the "NOT" for find requests and should be at the end of any scripted or manual finds (after all the finds.
8. Constrain and Extend can help
a good place to start (and follow the links):
hi fran55 /beverly,
@fran - will send you as requested - thanks for the help!
@ beverly - yes as johan also mentioned, i can find what i need with a standard PERFORM FIND.
however, the problem is in the sheer number of text string combinations, what makes using the standard way a tedious work. therefore i was thinking (helped by david) to make two different tables and combining the values as needed (by selecting) and then running a script.
will have a look at your links anyway - am sure i will learn a lot from it :-) thanks!
OK, I didn't see your changes to the original post with multiple criteria. I think I would have a value list of possibles and format as checkbox. If you Substitute() the returns for spaces, your "list of words" would find ALL. If you want to allow the user to find ANY, then loop through the list with separate requests for each one.
Building on Beverly's last post. If you have a return separated list of words--whether created via check box set or just typed into a field and want to find all records with any one of the words:
Make that field a text field with global storage, I'll call it "gSearchText" in this example script:
Enter Find Mode  -->clear the pause, no stored criteria
If [Not IsEmpty ( YourTable::gSearchText ) ]
Set Variable [$K ; value: $K + 1 ]
Set Field [ YourTable::TextField ; GetValue (YourTable::gSearchText ; $K ) ]
Exit Loop if [ $K >= ValueCount ( YourTable::gSearchText ) ]
New Record/Request ---> this produces a new request, not a record in find mode
Perform Find 
This "peels off" one word at a time from the list and uses it as a separate find request. When you create multiple find requests--either manually or like this in a script, it's an "OR" find where you find the records that match Request 1 OR request 2 OR Request 3....