2 Replies Latest reply on Aug 28, 2013 6:44 AM by philmodjunk

    Calculation field problem



      Calculation field problem


           There are  2 tables:

           Class Table & Registration Table


           Fields in Class Table:

           "Class ID" & "Class details" & "No. of Registration to this class" which is a calculation field I am having trouble with.

           Fields in Registration Table

           "Class ID" & "Status" . For Status the data may be "In Progress" & others" 


           So in the "No. of Registration to this class" field, I want to set up a calculation to look for the number of registration to this class with Registration Status "In Progress". The result maybe like" 10 students has registered for this class." Since a class will have a upper limit of students. 

        • 1. Re: Calculation field problem

               The database consists of two tables Class (a parent table) and Registraion (a child table), with one -to many relationship. Using this relationship your are getting the count of - no.of registration of this class, which gives total number of registration for a class irrespective of 'registration status'.

               To get the count of registration with specific status, create one global text field 'gStatus' in 'Class' table. set its value to 'In rogress'. Create one more relationship between Class & registration table, in following manner (two field sets)
               Class::ClassID = Registration::ClassID
               Class::gStatus = Registration::Status

               Create calculation field in 'InProgressCount' in Class table, which uses newly created relationship (registration Table occurence). This should give you count of registrations with specific status for particualr class.

          • 2. Re: Calculation field problem

                 Instead of a global field set to "In Progress", you can also define a calculation field that returns this value. There are trade offs to either variation of that method.

                 You can also use a filtered one row portal to Registration with a "count of" summary field defined in Registration and a filter expression that limits the records to only "In Progress" records of that class. This avoids the need for an extra match field and extra relationship, but also produces a value that you can display but which cannot be used in other calculations or referenced in a script should that be needed.

                 The third option for selectively counting/summing records is to use SQL in calculation field that uses the ExecuteSQL function. This last FileMaker 12 only option avoids the added changes to your relationships, yet produces a value that is accessible for other calculations and scripts.