14 Replies Latest reply on Oct 8, 2013 1:57 PM by ClairJudas

    Grade level based on age as of 9/15/xx

    ClairJudas

      Title

      Grade level based on age as of 9/15/xx

      Post

           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,

           Clair

            

      Screen_Shot_2013-10-07_at_12.55.39_PM.png

        • 1. Re: Grade level based on age as of 9/15/xx
          philmodjunk
               

                    based on his/her age as of 9/15 of a specific year.

               And what year would be that "specific year"?

               The same basic calculation that computes age for the current date can be used with a date computed from that specific year.

               It might be date ( 9 ; 15 ; Year ( Get ( CurrentDate ) ) )

               And what should the system do with students whose actual grade in school is different from that predicted by their age due to being held back a year?

          • 2. Re: Grade level based on age as of 9/15/xx
            ClairJudas

                 The 'specific year' would normally be the beginning of the current school year. So this year, it would be 9/15/2013. Next year they would like it to auto update as of 9/15/2014.

                 This is kind of a special situation. The school is for kids with cognitive disabilities. Consequently, they are assigned a grade level based on age, rather than academic prowess. Mostly this is being done for IEP (Individual Education Program) purposes. That is also why there is some overlap on the grades; Extended Preschool: 5-6.11; Kindergarten: 5-5.11; First Grade: 6-6.11). The number of kids in the Extended Preschool is pretty nominal and could be updated manually. 

                  

            • 3. Re: Grade level based on age as of 9/15/xx
              philmodjunk

                   This looks like data that can be looked up from a table consisting of the data in the right hand columns. With the data looked up, you can then manually edit a field to document exceptions such as your extended preschool designation.

                   The modified age calculation

                   GradeAge:

                   Let (  [ B = YourTable::BirthDateField ;
                               T = Date ( 9 ; 15 ; YourTable::SchoolYearField )  
                            ] ;
                               Year ( T ) - Year ( B ) - ( T < Date ( Month ( B ) ; Day ( B ) ; Year ( T ) ) )
                         )

                   The first few rows of your look up table might look like this:

                   MaxAge           GradeDescritpion
                   0                      infant:
                   2                      Toddler
                   4                      Preschool
                   5                      Kindergarten

                   Your relationship between the two tables can be:

                   IEP::GradeAge = GradeLevelLookup::MaxAge

                   You can specify a looked up value setting with the "if no exact match, use next higher value" setting selected (Age 1, then looks up "Toddler)

                   or it can be:

                   IEP::cGradeAge < GradeLevelLookup::MaxAge

                   With a sort order in the relationship that sorts GradeLevelLookup by MaxAge in descending order. THen you can still use a looked up value setting but without the "If no exact match" option specified.

                   since this value is copied from the lookup table, you can edit it to assign extended preschool.

              • 4. Re: Grade level based on age as of 9/15/xx
                ClairJudas

                     That seems to be doing the trick, but I have a couple of questions...

                       
                1.           what did you mean by "cGradeAge"?

                     And... I know I'm being dense, but I'm not finding the "looked up value setting with the "if no exact match, use next higher value" setting".

                Can you point me in the right direction?

                Thanks for all of your help, it is much appreciated.

                Clair

                • 5. Re: Grade level based on age as of 9/15/xx
                  philmodjunk

                       Sorry, I edited my field name one time too many. cGradeAge should read GradeAge and it's simply the calculation field that computes the client's age as of the 9/15 date.

                       See the uploaded image for the steps to set a looked up value auto-enter field option. You start by selecting Manage | Database | Fields:

                  • 6. Re: Grade level based on age as of 9/15/xx
                    ClairJudas

                         Aha...

                         I had made it a calculation field so was getting different options.

                         Thank you very much for your assistance. This seems to be working like a charm. Take care,

                         Clair

                    • 7. Re: Grade level based on age as of 9/15/xx
                      philmodjunk

                           But be careful to keep in mind that this system will not automatically update as time passes and the client becomes older. My assumption here is that each time you schedule an IEP (I have kids with special needs so I am familiar with the process.), you would create a new IEP record and thus this data would be looked up and recorded as the current info at the time the IEP is held.

                      • 8. Re: Grade level based on age as of 9/15/xx
                        ClairJudas

                             Thanks for the reminder.

                             I posted a note to the secretary that she would need to update the grade levels next Sep 15. I've left a note about how to "Replace Field Contents..." to update the school year quickly and easily for all of the records. Just have to remember to do it!

                             I think I knew you had a child//children with special needs. You helped me out once before on this same database. That came up as I explained the use of the database at that time. :-)

                             Take care and if you think of anything else I should know please do.

                             Clair

                        • 9. Re: Grade level based on age as of 9/15/xx
                          philmodjunk

                               If you are creating a new record for each IEP and looking up the info into fields in that table, I don't see any need for updates. Only if you are showing the data in the Clients table. In that case, you may want to revisit the idea of a calculation field which can be used with the inequality operator instead of =, but you'd then have to come up with a different method for manually overriding this reference in cases such as extended preschool clients.

                          • 10. Re: Grade level based on age as of 9/15/xx
                            ClairJudas

                                 I just talked to the secretary, they no longer use the Extended Preschool designation so that particular wrinkle has ironed itself out.

                                 The school has a record for each student with tables for...

                                   
                            •           Students (Name, birthdate, age, grade, room number, the new School Year field  and portal to bring in other info from other tables)
                            •      
                            •           Staff (teachers, psychologists, social workers, PTs, OTs and others who work at the school, tied to the students they work with)
                            •      
                            •           Bussing (private companies and school districts that bus the kids to the school)
                            •      
                            •           Caregivers (parents, aunts, uncles, group homes, etc.)
                            •      
                            •           Schools (they serve students from other school districts)
                            •      
                            •           IEP Data (tracking IEP info for students to make sure the appropriate steps are being followed)
                            •      
                            •           GradeLevel (the Lookup table)

                                 The "School Year" field is in the Student table

                                 What happens now...

                                 Students who are in school need to have their grade incremented by 1 each year: changing the School Year from 2013 to 2014 on Sep 15, 2014 will do that, then using the Replace Field Contents.

                                 New students will have their birthdate entered which calculates the age; the school year entered which finds their grade, using the lookup table GradeLevel.

                                 Are you suggesting there is a way to do this that will auto-increment the age every Sep 15th?

                                 Clair

                            • 11. Re: Grade level based on age as of 9/15/xx
                              philmodjunk

                                   Replace Field Contents can be a bit risky as it can modify large numbers of records all in one batch operation with no way to undo it short of reverting to a back up copy made prior to that step.

                                   The Relookup option in the Records menu is much safer, but no update need be done at all other than updating the school year field to get an updated age.

                                   I recommend using the second relationship option so that you can place fields from the grade look up table directly on the client layout. That method will update automatically with no need to relookup the data.

                              • 12. Re: Grade level based on age as of 9/15/xx
                                ClairJudas

                                     I was suggesting updating the School Year field using the "Replace Field Contents" option. It will update that field in all of the records at once.

                                     Otherwise they will have to change the year in the School Year field one at a time. Am I correct?

                                     Is there a better way to do that?

                                      

                                     Also I'm not clear by what you meant by "using the second relationship option".  And... how does it update automatically? 

                                      

                                • 13. Re: Grade level based on age as of 9/15/xx
                                  philmodjunk

                                       I'd set up a single global field for that value so that you only have to change it in one place. On a networked system, you can use a nonglobal field in a single record table and use a start up script to load the global field with this value.

                                       

                                            Also I'm not clear by what you meant by "using the second relationship option".

                                       Well keep in mind that I was thinking of a slightly different situation, but from Previous Posts:

                                       

                                            IEP::GradeAge < GradeLevelLookup::MaxAge

                                       

                                            With a sort order in the relationship that sorts GradeLevelLookup by MaxAge in descending order.

                                       

                                            You can place fields from the grade look up table directly on the client layout.

                                       Since these fields are the actual fields from the look up table a change in the value of GradAge will automatically update the displayed grade level description without needing to physically copy data for multiple records from one table to the other.

                                  • 14. Re: Grade level based on age as of 9/15/xx
                                    ClairJudas

                                         Well, that was easy. :-)

                                         Works like a charm!

                                         Clair