Find set of records based on common value list items in related table

[macmasterservices]

Hello everyone,

First off please bear with me as it's been a while since I was tasked to do anything with FMP, so I'm a bit rusty... not that I have any formal training, but I used to be much better at it and I am trying get my mind back in shape!

I am working on a solution that invoices customers for booth rentals at yearly trade shows. Many customers attend the same shows year after year. It consists of these linked files:

• Customers are in a table named "The New World!" (note the exclamation point) in a file named "The New World"
• Shows are in a table named "Show Names" in the file named "The New World"
• Invoices are in a table named "WWE Invoices" in a file named "WWE Invoices"

Here are the relationships:

I have successfully created a layout that lets the user choose any four prior shows and an upcoming show using a value list of show abbreviations from the WWE Invoices file (S19= Spring '19, F23=Fall '23, etc.). This works to find all customers who have attended any one of the prior chosen shows but not yet signed up for the upcoming show.

When they select the shows they are creating these global variables in the background:

The script then works like this:

So that's all well and good. But now I've been asked to make a similar report that looks for customers who have attended not just any one of the chosen prior shows, but ALL of them, and this is where I'm stuck. If I haven't given you enough to go by, please guide me!

Thanks!
Vince

Edited October 27, 2023 by macmasterservices

[comment]

To find customers that have attended all of the shows in the 4 variables, try making your script along the lines of:

Go to Layout [ Customers ]
Enter Find Mode []
Set Field [ Invoices::ShowAbbreviation; $$previousShow ]
Perform Find []

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow2 ] 
Constrain Found Set [ ]

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow3 ] 
Constrain Found Set [ ]

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow4 ] 
Constrain Found Set [ ]

I haven't tested this, but I suspect this requires all 4 variables to be populated. If that's not always the case, you may have to wrap each additional find in an If clause - or (preferably, IMHO) use a single variable to store a return-delimited list of the selected shows, and have your script loop over the list.

Note that there's no need to show all records before performing a find. 

 

 

[macmasterservices]

11 hours ago, comment said:

To find customers that have attended all of the shows in the 4 variables, try making your script along the lines of:

Go to Layout [ Customers ]
Enter Find Mode []
Set Field [ Invoices::ShowAbbreviation; $$previousShow ]
Perform Find []

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow2 ] 
Constrain Found Set [ ]

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow3 ] 
Constrain Found Set [ ]

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$previousShow4 ] 
Constrain Found Set [ ]

I haven't tested this, but I suspect this requires all 4 variables to be populated. If that's not always the case, you may have to wrap each additional find in an If clause - or (preferably, IMHO) use a single variable to store a return-delimited list of the selected shows, and have your script loop over the list.

Note that there's no need to show all records before performing a find. 

 

 

OK thanks for that tip, it now works! See this:

Edited October 27, 2023 by macmasterservices

[comment]

Watch out for the last Set Field step: it's performed in Browse mode, which is not what you want to do when finding records. If you're using stored find requests, then all you need is the Constrain Found Set step. Like many other developers, I usually prefer to do:

Enter Find Mode [ ]
Set Field [ Invoices::ShowAbbreviation; $$currentShow ] 
Omit Record
Constrain Found Set [ ]

because then you can easily see what the script does, without having to delve into the stored requests details.