AnsweredAssumed Answered

Creating an 'And' search across tables

Question asked by alangodfrey on May 9, 2016
Latest reply on May 9, 2016 by 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.

Outcomes