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.
More complicated than I thought. Will try and set up some calculated fields as you suggest.
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.
1 of 1 people found this helpful
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.
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.
Are those fields always the same fields?
What's the purpose?
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!