Built-in comparative search between two fields

Idea created by Vincent_L on Aug 20, 2017
    Active
    Score4
    • coherentkris
    • jbante
    • Vincent_L
    • DavidThorp

    Hi,

    another common search kind that users need to do, are searches involving dynamic comparison between 2 fields.

    For instance, searching all records where field A > Field B, or Field C = Field D

     

    You obviously ca do it in sql, but you can't with the search engine if you don't create a calc field just for that.

     

    Anyways, that's a common simple common need that's not addressed by the built-in tools and that's difficult / and or time consuming to implement.

    This create difficulties for users.

     

    Of course implementing this GUI wise can be tricky, but we can simplify this, buy limiting this comparison to just 2 fields for the standard GUI (we could have it expanded to several fields in the script step search where gui is more rich).

     

    A proposal would be to introduce a new search operator, for instance the ^ sign to denote the fields to be compared.

    An error dialog would pop-up  if more than 2 search fields would contain ^

    The  "Right" filed would contain ¨followed by the operator, while the left field would only contain ^

    Filemaker would then compare search field containing ^, and would use the comparator found in the other field between the two

     

    Let's says we've 3 fields and i want to compare field b to field c, to only get those where file B > field C

     

    FieldA     FieldB     FieldC

                   ^              ^>

     

    Or maybe it would be even simpler using symmetrical symbols such as { }

    { would mean left field, } would mean right field

     

    FieldA     FieldB     FieldC

                   {              {>}

     

    If you're concerned about searching for records that actually contains those characters. Filemaker would only do comparative search if there's 2 field containing the say operator to allow searching of just those character if needed, and would pop-up a dialog to ask if user wants really to do a comparative search.

     

    That approach would go well with the current GUI, and allow extend, constraint, or omit search as well as multiple search queries.

     

    we could even combine those 2 fields with others like

     

    FieldA     FieldB     FieldC

    10..20      {              {>}

     

    This would be very useful for lots of people, and would save us to create unstored calc just for that