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

    Find Question


      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


      Field C is equal to Field D


      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.



        • 1. Re: Find Question

          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

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

            • 3. Re: Find Question

              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

                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


                Field C = Field D


                Field E <> Field F


                then find 1 on calculation.

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

                  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



                    Are those fields always the same fields?

                    What's the purpose?




                    • 7. Re: Find Question

                      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

                        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.