7 Replies Latest reply on Aug 25, 2011 12:09 PM by philmodjunk

    Search Term List

    wbanks

      Title

      Search Term List

      Post

      I have a list of search terms that I need to run across a single field within a table.  The individual "FIND" isn't an option.  Ideally, I'd like to have a method of linking the particular search term with the resulting hit, but if that's not possible, a tagging mechanize would work just as well.

      If I created a table of list terms (one per record), could I create a script to search the contents of another table?

      Any help would be appreciated.

        • 1. Re: Search Term List
          philmodjunk

          I can think of a fair number of possible approaches, but don't have a clear enough picture of what you want to do and why an "Individual FIND" isn't an option for you.

          Can you provide an example of what you want to do here?

          • 2. Re: Search Term List
            wbanks

            Sorry, it always seems clearer in my mind... and I've thought about this more since the post.

            Table A contains multiple records (within a single text field) of search terms that I want to run across a single field (also text) in TABLE B.  Each record in Table A is a different search term or phrase.  The search terms and phrases will contain wildcards/and/or statements.

            I was thinking that I might be able (within scripting), to use the FIND statement with LOOP to cycle through the records in Table A.  As these terms are found in Table B, a "results field' is appended with the new search term hit.

            Does that seem plausable?  Is there a better method?  I'm still unsure how to actually write the script.

            • 3. Re: Search Term List
              philmodjunk

              It is possible. I'm not totally sure that it's the best approach.

              To perform an "and" type find, enter criteria in a single request. To perform an "or" type find, create multiple requests and enter different criteria in each.

              You'll probably need to loop through your criteria table to build a list of selectd criteria in a variable first, then enter find mode and build the find requests.

              On the other hand, if you are using FileMaker 11, you might get something that works well for you via a filtered portal instead of performing a find. It depends on how you want to display the records that match your criteria.

              • 4. Re: Search Term List
                philmodjunk

                I'm still not very clear on what you are trying to do here. You mentioned "and" and "or" as part of your criteria so I responded with info on how you can perform such searches.

                Have you scripted finds before or is this all very new to you?

                • 5. Re: Search Term List
                  philmodjunk

                  Say you have a list of values in a field: Apple, Orange, Strawberry. These are values separated by returns. They could be from a checkbox group or a list function might pull the list from a related group of records. You want to find all records with "Apple", "Orange" or "Strawberry".

                  Set Variable [$List ; YourTable::YourCheckBoxField // or you might have List ( RelatedTable::Field )]
                  If [Valuecount ( $List )
                  Enter Find Mode []
                  Loop
                    Set Variable [$I ; Value: $I + 1 ]
                    Set field [YourTable::Fruit ; GetValue ( $List ; $I ) ]
                    Exit Loop IF [ $I = ValueCount ( $List ) ]
                    New Record/Request
                  End Loop
                  Set Error capture [on] // keeps system dialog from interrupting your script if no records are found
                  Perform Find []

                  This is just one of many ways you can script a find. The details depend on the criteria you need to use. The main thing to keep in mind is that when the script enters find mode, the data stored in all fileds disappear except for fields with global storage. Thus, you have to either put data used for criteria in global fields or variables before you enter find mode.

                  • 6. Re: Search Term List
                    wbanks

                    Thanks for you assistance thus far, but I'm definitely missing something here.

                    Table A - Search Term List

                    Search Terms
                    Apple
                    Orange
                    Strawberry
                    Grape

                    Table B - Data

                    Search Phrase Search Results
                    I Like orange and apple juices Orange, Apple
                    How now banana cow  
                    Strawberry Fields Forever Strawberry

                     

                    This is the script so as I've understood it.

                    Set Variable [$List; Value:Search Term List::Search Terms]

                    If [ValueCount ($List)]

                    Enter Find Mode []

                    Loop

                    Set Variables [$Count; Value:$Count + 1]

                    Set Field [ Data::Search Phrase; Get Value ($List; $Count)]

                    Exit Loop If $Count = ValueCount ($List)]

                    New Record/Request

                    End Loop

                    Set Error Capture [On]

                    Perform Find []

                    End If


                    I'm my examples of Tables A (Search Term List) and B (Data), I would like the terms in Table A to loop through each record in Table B.  Each time there is a hit between Search Term List::Search Terms and Data:Search Phrase, I'd like that value to populate to the field Data::Search Results.

                    Does this make it clearer what I'm attempting to accomplish?

                     

                    • 7. Re: Search Term List
                      philmodjunk

                      What you describe is not what my suggested script is intended to do. It simply produces a found set of all records with at least one of the listed values.

                      To even bring up that found set would require a small change in the script you posted:

                      Set Variable [$List; Value:List ( Search Term List::Search Terms )]
                      If [ValueCount ($List)]
                             Enter Find Mode []
                             Loop
                                     Set Variables [$Count; Value:$Count + 1]
                                     Set Field [ Data::Search Phrase; Get Value ($List; $Count)]
                                     Exit Loop If $Count = ValueCount ($List)]
                                     New Record/Request
                             End Loop
                             Set Error Capture [On]
                             Perform Find []
                      End If

                      And this will only work if you have a valid relationship between data and Search Term List. (note that I am assuming that each search term is in a different record in the Search Term List table.

                      What you describe could be done with this script:

                      Set Variable [$List; Value:List ( Search Term List::Search Terms )]
                      If [ValueCount ($List)]
                             Enter Find Mode []
                             Loop
                                 Exit Loop If $Count = ValueCount ($List)]
                                 Set Variables [$Count; Value:$Count + 1]
                                 Set Field [ Data::Search Phrase; Get Value ($List; $Count)]
                                 Set Error Capture [On]
                                 Perform Find []
                                 Replace Field Contents [no dialog ; data::Search Results ; Data::Search Results & ", " & Get value ( $List ; $Count ) ]
                             End Loop
                      End If

                      With some calculation fields and a relationship, it's even possible to produce the results in Data::Search Results without any script at all.