AnsweredAssumed Answered

Problem on calculation field involving several related records

Question asked by TaylorJansen on Aug 3, 2013
Latest reply on Aug 3, 2013 by raybaudi


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.