3 Replies Latest reply on May 9, 2016 1:17 PM by alangodfrey

    Creating an 'And' search across tables

    alangodfrey

      Filemaker's search facility is so powerful and intuitive that I fear I am the victim of late-night paralysis by analysis on this one.  Would appreciate someone to free my mental log-jam.

       

      I have a table of Clients, and a table of Mailshots (with an intermediate Join table).  The Client Form has a portal showing the Mailshots they have received.

      Using that Form I can find:

      - Clients who have received the 'Spring 2015' Mailshot. (Type 'Spring 2015' into the portal field)

      - Clients who have received the 'Spring 2015' Mailshot PLUS ('OR') Clients who have received the 'Spring 2016' Mailshot. (Type 'Spring 2015' into the portal field.  Create a new Find Request, type 'Spring 2016' into the portal field)

      - Clients who have received the 'Spring 2015' Mailshot and have NOT received the 'Spring 2016' Mailshot. (Type 'Spring 2015' into the portal field.  Create a new Find Request, click 'Omit', type 'Spring 2016' into the portal field)

       

      But how do I find 'those Clients who received both the 'Spring 2015' AND the 'Spring 2016' ' Mailshots?

      I have a method that seems to work, but it grates because:

      - it seems so out of kilter with the user-friendliness of Filemaker's search facility

      - it isn't extensible (I couldn't use it to Find a 3rd criterion, eg: 'Clients who received the 'Spring 2014' AND the 'Spring 2015' AND the 'Spring 2016' Mailshot')

       

      I would rather not use a scripted method as I would like the user to have the flexibility they are used to with all other searches.  I'd rather not use ExecuteSQL as I also seem to remember it was on the 'To Be Deprecated' list.

       

      Thanks in anticipation.