Problem on calculation field involving several related records
I have the following tables and fields in concern:
Table Name | Fields
Registration | Registration ID, Course ID
Invoice | Invoice ID, Registration ID, Invoice Status
Attendance | Invoice ID, Attendance Status
The situation is each registration can have many invoices and each invoice can have a certain number of attendace record. When a new invoice is created, 4 attendace records will be created since usually there are 4 classes for 1 invoice. Only 2 values for the Attendance Status field: Unattended or Attended.
This is the Invoice Status field I am having trouble. I want it to be a calculation field. If the Attendance Status of all the 4 attendance records of this invoice is Unattended, the Invoice Status shows "4 Outstanding Classes"; if one classes is attended, the Invoice Statsu shows "3 Outstanding classes" etc and if all 4 classes are attended, it shows "All Classes Attended".
I have no idea at all on how to set this calculation field as it involves 4 records in another table. Please help.