Evaluating related records for checkbox value

[stephiesmith1]

I have a web-enabled performance review database (Pro 8 advanced). Reviews require a self-assessment, possible Team Lead assessments, and a supervisor assessment, which must be done in that order since both Team Leads and Supervisors need to see the Self assessment, and Supervisors also need to see Team Lead assessments for each person they supervise.

I have the menu set up so that when a Team Lead comes in, he can tell if the employee is ready to review because of the font color of the employee's name in the portal (i.e., if a Team Lead has 3 people to review, their names show up in red initially. As they submit their self-assessments, their name(s) turn to green. This works from a check box "SelfAssessmentCompleted" which is set to "Yes" when the person Submits his self-assessment. If this checkbox=Yes, font color turns to green.

The problem is with the Supervisor's records. Because the Supervisor's people may or may not have Team Lead assessments (related records between employees and Team Leads are in a join table called TeamLeadInputs) which must be submitted before the Supervisor can review the person, I need to run a script that checks to see if that Employee's Team Lead Inputs (if any) have been submitted yet. I have a checkbox in the TeamLeadInputs table that changes to Yes as each input is submitted. I was thinking of having a sinmilar checkbox in the Employee Table that gets checked when ALL related TeamLeadInputs have been submitted. The script could run each time a TeamLead Input is submitted, i.e., checking all inputs related to that employee to see if the checkbox="yes," and if all are "yes," then the checkbox in the Employee's record would get checked.

Does anyone know how to do this? I suspect it involves looping through the related records, but I don't know how to write the script. Any help will be greatly appreciated.

Thanks!

Stephie

[stephiesmith1]

I probably could make this script simpler, but it took me long enough just to figure it out this way and I don't have the luxury of time to keep trying other things. Anyway, in case anyone is interested, attached is a picture of the script steps. I ended up using a global counter that I set to 0 and which gets a +1 for each TeamLead assessment that has been completed for an employee. If the gcounter equals the total number in the found set, then the TeamLeadAssessmentsCompleted field gets a "Yes." Rather than have this script run when a TL assessment is submitted, I will have the supervisor run it when he comes into the database. BUT since this is a script that checks the related records for just one employee, I have to insert it into a script for the supervisor so that all the employees that he is supervising will be checked, i.e., a "go to portal row" script. THAT will probably take me another 3 days to figure out:-(

Stephie