1 Reply Latest reply on Aug 3, 2013 3:29 AM by raybaudi

    Problem on calculation field involving several related records



      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.