Then you use following Script Steps
Go to Layout (Contractors)
Show All Records
Go to records (First)
If ( Patterncount( RelatedContracts::YourStatusField ) ; "overdue" ) >= 1
Your Action to send PDF
Go to Record (Next) Finish after last
A basic (at least for me) Filemaker principle is "do your search where the data is". Not in a related table.
Of course it begins to matter when you have many records.
So instead of looping on contractors I'd loop on contracts and gather the Contractor's pk's.
But in the days of SQL you can formulate this otherwise:
$allDue = ExecuteSQL("SELECT pk_Contractor from Contracts WHERE contractStaus = 'overdue';"";"")
$uniqueDue = < get unique values from $allDue > (*)
loop $i on 1 to valueCount($uniqueDue)
set a global to getValue($UniqueDue; $i)
send mail to get data from related record (relationship from global to contractors pk)
(*) To achieve this you have
- plugins (MBS, BaseElements)
- custom functions (like FileMaker Custom Function:UniqueValues( values )))
- other tricks, like creating a valueList and using ValueListItems
- use the DISTINCT clause in $allDue, but it might be slower
You can also just perform a find on a layout based on the related records to find only those that are overdue, then loop through them. Those note overdue are already omitted so the need to check and skip records is eliminated. Your relationship will still allow you to access data from the contractor table when sending out your emails.
You can use a sorting by contractor and a loop to build a list of the contracts that are overdue for a given contractor in order to send out a single email to each such contractor with the list either in the body of the email or attached to it as a PDF.