Create Set of Student Term Records - Updated!

Student Term Records Creation

Obejectives

Make a new set of student term records for a new student (status = planned) or
Make a new set of studnet terms when a student progresses into a new grade span.
Make one student term record for every term the student is planning to attend in the associated grade span.
Associate the new student term records to student.
Associate the new student term records to the school term.
note: students are only associated to school terms through the student term association

From the student table, we know the following fields:

Student's first term - calculated by looking up the school term based on the start date
Enrollment year terms - the number of terms the student will attend in their first year
Grade level - the students current grade level
Grade span - the planning horizon for that student based on their grade level

Records that must already be created

Academic year records ~~untill~~until the end of the grade span
School Term Records until the end of the grade span

New Students

For a new student, there are no student terms records in the system. The first student term begins with the student's start term. The school term corresponding to the first student term is the start term name, entered into the student record field, "student start term" The student sequence of this first student term will be 1.

New students may not have school terms made for the complete year. If they enrolled in the last term of the school year, then for that school year, there will only be one school term created.

Recurring Students

A recuring student already has at least one student term in the system. The last student term is found by the maximum student sequence value.

Grade Spans

The student terms records are created from the point the student enters the school until the end of the student's grade span. For example, If the student is promoted from grade 5 to grade 6, and grade 6 is in a grade span from 6 to 8 (like US middle school) then all the student term records will be created until the student has completed 8th grade. The student terms for high school will not be created untill the student is promoted into the next grade span.

Student Sequence

The student sequence stores the sequence the student progresses through the terms. Student Sequence is a continuously increasing integer value from 1, the first term at the school, until the student has graduated from all available grade levels. For example, a student enters the fall term of kindergarden, the school has defined two school terms. 13 years later, the student's last term (13x2 = 26) will have a student sequence of 26.

Student Term at_grade

The student term must store the predicted grade level of the student for that specific term in the at_grade field. At first, the at_grade is the starting grade level of the student.

Trigger to increase the at_grade

When a student term is created, the record is associated to a school term, which is associated to a term label record. If that term label record is flagged (flag field) as the official end term of the school year, the student's grade level will increase by one. With the completion of the official end term, the student is promoted, and so the at_grade value increases for the next student term.

Determining the end of the grade span

If the student's predicted at_grade is greater than the end grade of the assigned grade_span, then the student's predicted grade level exceeds the grade span, student term records do not need to be created for the next span.

Optional and Required School terms

Schools may configure optional terms, for example a summer term that students may or may not take. Student terms are made for optional and required terms.

For New Students

Key Trigger: New student record added to the student table.

Find the school term with the same name as that in the student start term field. Note: From the student start date, the school start term has already been determined.

Create a student term record and set the student sequence to 1.

Associate the student term record with the school term record found above and the student

Set the at_grade field = student's current grade_level

Check the associated school term record's flag field, if the flag field value is "official end" then at_grade = previous value + 1

Check the new at_grade value. If the new at_grade value is greater than the student's grade_span's end grade, then stop. note: continue if the new at_grade value is equal to the end grade, this representst the last year of the grade span.

Find the next school term with ordinate = last school term ordinate + 1

Make the first student term record

Make a new student term record, associated the student term record with the school term which has been entered into the "student first term" field in the student table
Enter the "flag" from the term record into the "flag" field in the student term record.
Enter the "current program" from the student table into the "student program" in the student term record
Enter the "current grade level" from the student table into the "at grade" field in the student term record
Set the S"tudent Sequence" in the student term record to 1

Determining Grade level for each student term for the “at_grade” field

The student’s grade_level increases after the student has completed all the terms in an academic year. The term record has a field called 'flag" which notes if the term is the official end term. After the official enter term, the student's grade level is expected to increase, and so the "at_grade" field will increase by one.

Create the student term records in sequential order, according to the term records. When you get to a term record that is “official end” then you know to increase the grade level integer by one for the next record.

Loop Algorithm

Find the term record name where the student record's start_date is between the term's start and end date and ENTER the name of the term in the Student records' school_start_term field. This is the student's first term, with student sequence = 1.
Loop to create associated student terms records
1. define a "variable for the student's current grade level number", this value will increment in this loop
2. associate the student term record to the student
3. associate the student term record to the term record found above (the term the student is first enrolled)
4. set the student terms student_sequence field to "1"
5. set the at_grade field to the start_grade in the student record
6. If the associated term has a flag field = "official end" then increase the variable defined or the student's grade level number by one
1. check the student's associated grade span, if the "variable for the student grade level" is greater than the end_year of the associated grade span, then stop. If the variable is equal to or less than the grade span end_year, continue to create records
2. create a student terms record
  - note: student term records are created for optional as well as required terms

Create the first student terms record
Create the next student terms record (until the end of the loop)

Calculate Field Values

Student Term Name

Concatenate: Term Name, StudentInitials, Student Code, “GL” grade level number:

Example from Kassidy’s Record: Spring 2023 KKB14 GL3

Update the record name every time the record is saved with a change to the associated term, associated student fields, or grade level field.