6 Replies Latest reply on Mar 11, 2013 10:49 AM by med

    finding records with a field value that is part of a word string

    med

      Title

      finding records with a field value that is part of a word string

      Post

           I need fo perform find based on a field value being a part of a string of words.

           I have a table containing codes of teachers like LT,CMB,FR,... etc

           then I am given a string of codes like "CMB_FR" which can vary in number of words

           Now I need to perform a find which will give me all records containing either CMB of FR

           if I know that the string contains only two words I can compose a find with two lines one for each but I need it to be general for any number of words

           Can any body suggest a way to do that

        • 1. Re: finding records with a field value that is part of a word string
          philmodjunk

               If you are using a script then:

               

                    if I know that the string contains only two words I can compose a find with two lines one for each but I need it to be general for any number of words

               can easily be generalized as your script can loop through the list and generate one request for each word.

          • 2. Re: finding records with a field value that is part of a word string
            med

                 I am woried about the speed because this is my main problem and I was trying to process all together.

                 If I perform the first find then loop and extend find for each word wouldn't that take more time than if I can perform one find for all?

                 Because I did not not say the whole story .. I am finding for these people and there is an additional criteria ( last activity day)

                 So I find for code="CB" and last_active=$date

                 then I will have to extend find to code="FR" and last_active=$date

                 and so on and the table is about 90,000 records.

            • 3. Re: finding records with a field value that is part of a word string
              philmodjunk

                   That's not the method that I am describing here.

                   Say you put "LT_CMB_FR" into a field and perform the following script:

                   Set Variable [$Values ; value: Substitute ( YourTable::YourSearchField ; "_" ; ¶ ) ]
                   Enter find mode [] --> clear the pause check box
                   Loop
                      Set Variable [$K ; value: $K + 1 ]
                      Set Field [YourTable::FieldToSearch ; "*" & GetValue ( $Values ; $K ) & "*" ]
                      Exit Loop If [ $K > ValueCount ( $Values ) ]
                      New Record/Request
                   End Loop
                   Set Error capture [on]
                   Perform Find[]

                   The script will generate three find requests with *LT*, *CMD*, *FR* as the find criteria specified in the "fieldToSearch", but in three separate find "requests" for a single find operation.

                   Note that the data format here "LT_CMB_FR" complicates your search process and the options that are available. If you stored the same data in a return separated list or as records in a related table, you'd have other  options possible such as using a relationship to pull up all matching records with at least one of the matching values.

              • 4. Re: finding records with a field value that is part of a word string
                med

                     Thank you I implemented this and it works great

                     I did not know that I can do this I always used the find, constraunt and extend but I think this enter find mode is great

                     The only thing I feel shaky in now is incorporating the not equal conditions in that kind of search

                • 5. Re: finding records with a field value that is part of a word string
                  philmodjunk

                       "not equal" requests can be set up just like pressing the "omit" button in a manual find.

                       When in Find mode, the Omit Records step changes the request into an omit request.

                       Just like you would in a manual find, make omit requests the last requests created before performing the find.