3 Replies Latest reply on May 17, 2013 9:51 AM by philmodjunk

    Calculation Help



      Calculation Help



           Background: I have 3 tables- Contacts (all information on students is here-name, id etc), Courses (This is connected to contacts via "Concentration" tab- this is a table that lists all of the courses needed for each concentration), and Student_Course (This is a table of a student's Id, Concentration, CourseID and status. This is connected to Contacts table via ID and Concentration. This table lists all of the courses needed for the concentration and then is marked "complete", "needed" or "enrolled".).

           I would like to mark a status for each student based on the status of the courses. 

           ie. when all of the student's courses are marked "Completed," I would like a new status field to change from "In Progress" to "Program Complete." I tried an "if" calculation, but this only calculated on each field If ( Status ≠ "Completed"; "In Progress";"Program Complete" ). I need it to calculated based on ALL courses for the Student_ID. 


           Any thoughts?

        • 1. Re: Calculation Help

               Set up a calculation field, cCompleteFlag in Student_Course that returns a 1 if the course is completed and null or 0 if it is not. Then Sum ( student_courses::cCompleteFlag ) will return the number of completed courses and Count ( Student_Courses::Student_ID ) will return the number of such courses. If the sum = the count and the count is not 0, then the student's course work is complete.

          • 2. Re: Calculation Help
             Thank you for your quick response. I am a bit confused on where the sum and count would be utilized... I have the calculation field set up in student_course- would i set up separate fields for sum and count? Thanks again.
            • 3. Re: Calculation Help

                   The sum and count calculations have to be evaluated from the context of the student table--either a calculation field defined in that table with the correct "context" selected or in script steps executed while a layout based on the student table is current or in a conditional format expression on a student layout.

                   Which method (or combination of them) is used depends on how you need to work with this data.

                   Please note that a single calculation can reference both the sum and count functions so you will not need separate calculation fields for each to show which students have completed all required courses.