Related records via type
I am having trouble getting a relationship to get the desired outcome for me. Filemaker Pro 10 Advanced, Mac OS X.
I have a Contacts table, which has records for lots of students and a handful of staff. Each record has a 'type' field where either 'staff' or 'student' is recorded.
There is an Inventory table and an Equipment Loan Data (ELD) table which is used to record when an item from the Inventory is loaned out to either a staff or student.
When a loan gets checked out to a student, it produces a print out based on the Equipment Loan Data table with all the details of the hire, and then the user clicks a button which automatically saves it as a pdf and emails it to the student. This is all working fine.
However, whenever something is checked out, I'd like each email to also automatically be CC'd to the handful of staff on the list.
I tried setting up a global field in the Contacts table that always contains the word 'staff' (let's call the field gStaff) and then setting up a relationship like this:
ELD::gStaff = Contacts(StaffTO)::type
I thought that by using this relationship when I selected the email field from Contacts(StaffTO) table occurence, that it would isolate all the records that have 'staff' in their type field and send the email to them.
What happens though, is if I don't have the 'collect address across found set' checkbox ticked, then the email gets CC'd to the first found staff member on the list, which is to be expected, but not what I want.
If I do have the 'collect address across found set' checkbox tixed, then it puts the right number of email addresses into the CC line of the email, but does the same email 3 times (the first on the list).
I can't think of how else to do this, other than hard coding the emails into the CC field, which I don't want to do for obvious reasons.