Grade level based on age as of 9/15/xx
I need to be able to have a grade level (1st, 2nd, 3rd, etc.) automatically entered into a student table, based on his/her age as of 9/15 of a specific year.
The student table has a Birthdate field, and an Age fields that auto-calculates the current age as of today. There is also a Grade field, but it is just a manually updated field that is set as text.
A complicating factor is that one age range (5 yrs - 6 yrs, 11 mo) can be either "Extended Preschool" or "Kindergarten" based on the student's needs.
I've created a GradeLevel table that I intend to use as a source table. I had this working in an Excel spreadsheet, but am not sure how to set this up in FileMaker.
Any help would be appreciated. Thanks,