6 Replies Latest reply on Apr 11, 2011 2:04 PM by med

    Building a find condition with an "or"

    med

      Title

      Building a find condition with an "or"

      Post

      I am having difficulty in building find criteria in scripts. If you add lines to the criteria it always join them with an ADD

      1. What if I want to find records containing fields equal either one value or another.

      2. How can I say I want records with fields with values (text) contained in another comparison string.

      3. How can I say I want records with fields containing left(field;2)="ab"

      One final question: what is the difference between find and quick find

        • 1. Re: Building a find condition with an "or"
          philmodjunk

          1. What if I want to find records containing fields equal either one value or another.

          Put the criteria in Different find requests for the same find to get an "or" find between your specified criteria.

          By hand, you'd do it this way:

          1. Enter find mode
          2. enter criteria for first option
          3. Select New Request from the Requests Menu
          4. Enter criteria for the 2nd option.
          5. Perform the find and you will find all recors that match the criteria entered in the first request or that match criteria specified for the second.

           

          In a scripted find, you can use New Record/Request to generate additional find requests.

          2. How can I say I want records with fields with values (text) contained in another comparison string.

          Not sure I understand what you want here. Can you give an example?

          3. How can I say I want records with fields containing left(field;2)="ab"

          It depends on the type of data in your field. If you have a single word in your field in all cases, you can simply enter "ab" as your criteria. You have have multiple words in your field and you only want to find records where the first two letters of the first word are "ab", then you'd need to define a calculation field that eliminates this issue. Sometimes, you can craft a calculation field that uses substitute to strip out all the characters such as spaces that can be used to separate words, or you can define a field as Left ( field ; 2 ) or maybe Leftwords ( field ; 1 ) and then you enter your criteria into this field instead of the original text field.

          One final question: what is the difference between find and quick find

          When you enter find mode and specify criteria. Each criterion is entered into a single field and that criteria is only matched against values in that one field. With quick find, the criteria entered is matched against all fields on your layout where you have permitted quick find to be used. (There's an option in the Inspector that controls which fields allow quick find searches.) Example, If you enter "ab" into a last name while in find mode, you find all records where the last name starts with "ab". If you enter the same text into the quick find box, it might find all recors where the last name, first name, street address, etc. start with "ab".

          Note: as the need for ever more sophisticated finds arise for your database, using scripts that perform the find on behalf of the user become more and more useful as they can insulate your user from the complexities of creating multiple find request for an "or" type search or to find "starts with" or "contains" type of text matching.

           

          • 2. Re: Building a find condition with an "or"
            med

            Thank you Phill

            The second question was not clear I will give an example.

            Let us say a payment type can be "visa" or "master" or "americam express" etc. I will need all records with type either cash or debit or check or patpal etc. Instead of repeating the criteria many times I want to say find those type that are element of the string "CA.DB.CH.PP.....etc"

            In the third question I understood your answer but I did not give a good example let me ask again; can you build a criterion that says 

             trim(field) is element of  "abc.def"  because this will find all but "abcd" for instance

            • 3. Re: Building a find condition with an "or"
              philmodjunk

              Aren't the second and third questions now nearly the same question? I don't see much difference here.

              This is an example of where scripted support for the find can be a major problem solver for you system. You can enter such text in a global field and then perform a script that parses the text pattern into multiple find requests in order to find your records.

              What you ask here in both cases, as I understand it, is to find records where the data in the record's field is contained in the search pattern specified as search criteria. That cannot be done with a simple find. You'd need break up the criteria into multiple find requests or possibly use some type of Filtered Portal and/or relationship to find and pull up your records.

              Some times there are work arounds if you think a ways outside the box. In your first example, you can define a calculation field such as:

              PatternCount ( "CA.DB.CH.PP" ; TypeField )

              And then you can search this field for 1 or values > 0 to find your records.

              • 4. Re: Building a find condition with an "or"
                med

                Ok I guess I have to use calculation fields for complex conditions but I always worry about the efficiency of the system. Do calculation fields actually take storage space, do they cause any slower response or are they evaluated only when the particular record is accesses. I am asking these questions because I do not really know the structure of filemaker tables / variables.

                • 5. Re: Building a find condition with an "or"
                  philmodjunk

                  Yes they will take up a bit more space, but this is unlikely to be a noticeable problem. Such a calculation field should be a stored indexed field and as such it will not re-evaluate execpt when the value of a referenced field is changed and then only for the current record. Thus, this rarely results in a performance issue.

                  It's more of a problem with Unstored calculations as they need to re-evaluate everytime the record is accessed. These, along with conditional formats and summary fields can greatly slow down how fast you can scroll records in a list view, for example, if there are a lot of records involved.

                  • 6. Re: Building a find condition with an "or"
                    med

                    Thank You.