Related Records Problem: Any Suggestions?
A scheduling application in FMP 12 Adv has three main tables, Contacts, Events, and Rosters. The data entry layout for Events allows entry of event date, type, time, location and so forth for each event. A portal on this layout allows entry of multiple contacts, assignments and so forth for each event using drop down menus from Contacts. Each portal record generates (or deletes) a related record in the Roster table that contains all info for a specific event for one contact. Thus one event may generate dozens or hundreds of records in the Roster table.
A fourth related table, Absences, contains one record (with contact name and absence date) for every date that a contact will be unavailable. If a contact will be unavailable for 7 days vacation, this table will contain seven records, one for each day of the vacation. Other contacts in this table may or may not be absent on any or all of the dates of the contact with seven days vacation.
I want to identify/flag absences in the Events data entry portal so that the scheduler can avoid them and have provided a field in this portal from the related Roster table record. For a contact to show up as absent in the Roster record, at least one record must be found in the Absences table that matches the contact name and the event date from the Roster record.
The Roster record contains a portal showing the related records from the Absences table (if any) and correctly lists the names of all contacts unavailable on the specific event date, including the contact that is the specific subject of this Roster record. I need to get this specific name from these related records and flag it in the Roster record and the Events data entry portal.
It seemed to me that I needed a script activated from the Events data entry portal to go through the Absences table related records to find a match between both contact and event date, but this has been unsuccessful so far through many permutations. Perhaps I am missing a key table relationship.
Before spending more time on scripts I wanted to check into the forum to see whether folks have been down this trail and can suggest more straightforward or better ways to solve this problem.