8 Replies Latest reply on Aug 6, 2017 11:46 PM by user19752

    Find Question

    mjbenun

      I am trying to script a FIND that compares the value of several fields e.g.

       

      Find all records where..

       

      Field A is not equal to Field B

      AND

      Field C is equal to Field D

      AND

      Field E is not equal to Field F

       

      the Fields are not necessarily on the same layout or in the same table.

       

      Not sure how to approach this- any help would be appreciated.

       

      Thanks!

        • 1. Re: Find Question
          philmodjunk

          Sounds like something better done with ExecuteSQL as it allows for logical expressions that compare field values.

           

          FileMaker's "query by example" process doesn't lend itself well to such field to field comparisons. It can be done, but with a bit of work. You could, for example, define a calculation field with this expression:

           

          Field A <> Field B

           

          Select number as the return type.

           

          You can then do a find for a 1 in this calculation field to find all records where Field A does not equal Field B. This works when field B is from a related record with two caveats:

          This calculation then becomes unstored and this will make finds specifyiing criteria in this field much slower to execute.

          The calculation only compares a Field A's value to the "first" related record in the related table. If there are multiple related records, only the value of Field B for the first such record is compared to Field A.

           

          You can build a complete expression similar to what you posted to make all of these comparisons in a single field or define each pair of fields being compared in a different calculation field in order to more flexibly mix and match find criteria.

           

          And yes, even that might not work for you if you want to write different expressions "on the fly".

           

          And we'll probably see a post about using regular expressions in a web service (If I recall the details correctly, correct me if I'm wrong) in this same discussion as an alternative approach to FileMaker's built in find.

          • 2. Re: Find Question
            mjbenun

            More complicated than I thought.  Will try and set up some calculated fields as you suggest.

            • 3. Re: Find Question
              philmodjunk

              FileMaker usually makes simple finds very simple, but as the complexity of your criteria increases, the scale tips and SQL type queries become simpler than the QBE method of the standard find.

              • 4. Re: Find Question
                user19752

                For "on the fly", make a global text field and a calculation field as Evaluate(global). You can enter criteria to global

                 

                Field A <> Field B

                AND

                Field C = Field D

                AND

                Field E <> Field F

                 

                then find 1 on calculation.

                1 of 1 people found this helpful
                • 5. Re: Find Question
                  philmodjunk

                  This is a good idea, but the fact that it has to be an unstored calculation in order to reference the global can make for slow performance if you have a lot of records in your table. YMMV.

                  • 6. Re: Find Question
                    karina

                    Hi,

                     

                    Are those fields always the same fields?

                    What's the purpose?

                     

                    Greetz,

                    Karina

                    • 7. Re: Find Question
                      mjbenun

                      These are always the same fields.  I have been able to accomplish what I need by creating some new calculation fields which compare the fields.  For example calculation  Field "AB" which is (FieldA-FieldB).  Then I can use the new Field in my Finds.  Thanks for all the input!

                      • 8. Re: Find Question
                        user19752

                        Yes, it need to calculate on all records "on the fly", should be slow.

                         

                        mjbenun, mark philmodjunk s answer as correct if your problem is resolved.