1 2 3 Previous Next 41 Replies Latest reply on Oct 11, 2016 5:41 AM by mz5005

    Search for strings


      How do I set up a PERFORM FIND so that FMP finds multiple strings In one and the same field, but not necessarily as one string and without defining separate PERFORM FINDS (because then i get a lot of unwanted records which have only 1 of the strings)?

      I need something like 2 MATCH PHRASE FROM ANYWHERE operators with an AND


      e.g. I want to find any COMPUTER DISPLAY in article description




      Any help greatly appreciated!

        • 1. Re: Search for strings
          Johan Hedman

          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

          • 2. Re: Search for strings

            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.

            • 3. Re: Search for strings
              Johan Hedman

              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

              • 4. Re: Search for strings

                The basic principle would be something like:


                Enter Find Mode

                Set Field: ArticleDescription; "Computer"

                Perform Find

                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.

                • 5. Re: Search for strings

                  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.

                  • 6. Re: Search for strings

                    Thanks for the advice David. It looks a lot like I just said:


                    "Best would be 2 tables and a script with a loop I think but I don't know how to do that properly."


                    Had not thought about the CONSTRAIN FOUND SET option - that gives perspective :-)



                    • 7. Re: Search for strings

                      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.

                      • 8. Re: Search for strings

                        Simple answer:


                        • 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".

                        • 9. Re: Search for strings
                          Magnus Fransson

                          Hi mz5005,


                          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.

                          • 10. Re: Search for strings

                            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):




                            • 11. Re: Search for strings

                              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!

                              • 12. Re: Search for strings

                                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.



                                • 13. Re: Search for strings

                                  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

                                      End Loop

                                      Perform Find []

                                  End If


                                  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....

                                  • 14. Re: Search for strings
                                    David Moyer


                                    this custom function for text parsing might be useful (but in a loop, not in find mode) ...


                                    ParseValueByBookends ( table::textField; "COMPUTER"; 1; "DISPLAY" ) will return whatever is between those two keywords (e.g. " LCD ").

                                    1 2 3 Previous Next