Conditional Formatting based on data from multiple Fields
Very new forum member here (I've mostly been lurking) and novice, self taught Filemaker user. I've done what I feel is reasonable due diligence in searching for similar questions but havn't been able to find any answers that suit my particular goals.
My Info: FM Pro 9.0v3, Win XP SP3, Shared, likely will be published with IWP or CWP
The database: It's basically a dispatch system. The people taking the phone calls and assigning tasks have a pool of up to 20 people to pick from. There is a field (w/ 20 reps) in which they enter the names of all the staff on duty at that time. This is on Table #1 (Shift Log), a new record is only created once per shift. Table #2 (Dispatch Form) contains all the information specific to each dispatch, new records are created for each individual dispatch.
Table #1 has a unique serial number for each record. When a new dipatch is created on Table #2 there is a related field that associates the record with the current shift log. Each dispatch has it's own unique serial number as well. We generate about 2500-3000 records per week on Table #2 ( I don't know if this is pertinent for any solution, just thought it might be relevant).
The question: I want the name on the list of available staff to change color if they are already on a job. When the job they were on is closed out I would like the name to automatically change back to the regular text color so that other people looking at the shift Log will know that that person is available again.
The field, mentioned above, w/ 20 reps is on Table #1 and named ::Available Staff. I would think the conditional formatting should be searching two fields (::Assigned Staff (3 repetitions) and ::Job Status) on all the records in Table #2 where the Job Status is "Open", see which names are "assigned" and then change the color of their name on Table #1::Available Staff to indicate that those people are currently on a job.
I've given this a go, but keep coming up short. The closest I got was creating a field on Table #2 that showed all 20 reps from Table #1. I was able to get the color to change on the related field in Table #2 but it would only look at the current record, not all the records for that shift.
I hope my explanation was clear enough.
Thanks for any assistance.