3 Replies Latest reply on Jul 3, 2016 5:49 AM by clayhendrix

    How to determine enrolment status


      I have a table storing students names, and another table storing enrolled_courses. 


      In the enrolled_courses table, I have a variable that checks the end date to determine if the student has ended, or still currently enrolled.


      Hence each student would have a few enrolled courses, and some already ended, some still currently enrolled.


      Now I want to determine if a student is currently enrolled in at least 1 course, or totally not enrolled (means have quitted for good).


      From the context of the student table, I would be able to see each student's enrolments.

      How can I use this to form a calculation that: if there is at least 1 enrolled course for a student, then the student is enrolled, else the student is ended.


      I guess my approach might be wrong, hence would like to find out if anyone has done something like this before.  Thanks!

        • 1. Re: How to determine enrolment status

          The end date seems (to me) to be a direct attribute of a course, not of an enrolment; so what you (probably) should check if is a student is enrolled in at least one active course: so, with a structure like


          Student --< Enrolment >-- Course


          you'd have a (n unstored) calculation in Course that determines the active status, like


          cIsActive = Case ( Get ( CurrentDate ) <= dateEnd ; 1 )


          so from the Student context:


          Case ( Count ( Course::cIsActive ) ; "Active" )


          Same logic using SQL (and forgoing the calc field):


          ExecuteSQL ( "

            SELECT COUNT ( * )

            FROM Enrolment E

            JOIN Course C ON C.id = E.id_course

            WHERE E.id_student = ? AND C.dateEnd >= ?

            " ; "" ; "" ; Student::id ; Get ( CurrentDate )


          1 of 1 people found this helpful
          • 2. Re: How to determine enrolment status

            Thanks!  I set the active status as 1, and others as 0, then do a sum for this field, and if it is a non-zero, means it is an active student.  Thank you for the great simplicity!

            • 3. Re: How to determine enrolment status

              I have a different solution that might help you or someone else that is looking for a solution or similar solution.


              On the students layout, place a portal that lists classes, terms, or whatever you want to examine. You do need a status field in the table that has records for classes, terms, etc. You can have that status field set via calculation (end dates), or you can set them manually.


              Sort the portal so that the first record in the portal will be one in which the student is still enrolled (if he/she is enrolled at all). You also should sort the table this way as well.


              Finally, place the status field on the Student layout/report/etc. The status field is from the classes/terms table and is actually the status of the student's classes, but when sorted, as I mentioned above, if the student has any active courses, then they will be the first record(s) at it will display active on the student layout/report. Or, if there are no active classes/terms, then it will display inactive on the student layout/report.


              I hope this helps you or someone else.