9 Replies Latest reply on May 31, 2017 7:34 PM by greatgrey

    Populate next ID number based on conditions.


      I have a Student Information System database that I have created. I would like to write a script to assign students an ID number. This ID number is NOT the primary key for the student's record.


      Here are some parameters for the ID number as we manually assign them now that I cannot figure out how to incorporate into a script.

      1.) Students in each grade level have ID numbers that start with the same two numbers, ie. 24, 26, 28, etc.

      2.) Students are assigned an ID number upon enrollment and keep it throughout their years as a student in the this school.

      3.) For a particular grade level, ID numbers are assigned in order of enrollment, ie. the first child to enroll in a grade level has the ID number of 2401, the second child to enroll in a grade level has an ID number of 2402, etc.


      I can certainly have a serial number to populate a field, but I need a script (or calculation) to:

      1.) determine the range of ID numbers that should be used for a child in that particular grade

      2.) find the highest ID number used within that range

      3.) assign the highest ID used + 1 as the ID number for that student


      Thanks, in anticipation of your help.

        • 1. Re: Populate next ID number based on conditions.

          You also need to avoid having two users who might be assigning student numbers at the same time from getting the same value to assign to two different students. (You'll find many suggestions where you are advised to use one method or another to get a maximum value and to then add one, but two or more users doing this at the same time can all get the same maximum value and thus all get the same max + 1 value to assign to the record they are creating and you end up with duplicates.)


          But you need to explain how rules 1 and 2 do not contradict each other. In one, you indicate that the student's grade level determines part of the value in the other, you indicate that this value does not change for all the years that they are enrolled. Since their grade level will change each year, it would seem that these two rules are not compatible.


          I'm sure that they are, but you need to explain how both of these apply to a given student...

          • 2. Re: Populate next ID number based on conditions.

            Clarification for Rules 1 and 2:


            Students are assigned an ID number upon initial enrollment at the school. The first time in their lives that they have been enrolled in our school. So, if a student is enrolled at age 3, he/she is assigned an ID number for life (or duration of enrollment). Even though that student might come back the next year for 4 year old class, and then the next year for Kindergarten, and then First Grade, and so on, he/she keeps the SAME ID number each year.


            How grade level determines what ID number they are assigned is a predication of what year they will graduate from 12th grade. For instance, a student that graduates high school this year will have an ID number that starts with 17. He/she might have been assigned that number upon his/her initial enrollment in the school yesterday or he/she might have been assigned it upon his/her initial enrollment when he/she was in kindergarten and we predicted what year he/she would graduate from 12the grade.


            I hope that helps, philmodjunk!

            • 3. Re: Populate next ID number based on conditions.

              How many clients might do this student number assignment at the same time? Never more than 1 or possibly more than one?

              • 4. Re: Populate next ID number based on conditions.

                Possibly more than one, but the field validates for a unique value, so the second user would get a validation error, correct?

                • 5. Re: Populate next ID number based on conditions.

                  That would be the minimum setting needed. (More might be done to help avoid this in order to get a more user friendly process, but you need at least that validation option.)


                  I would set up a related table with two fields: GradYear (two digit value) and Sequence.


                  Concatenating the two produces your student ID.


                  From your students table, you would set up this relationship:


                  Students::GradYear = StudentIDs::GradYear


                  Then this code assigns your ID:


                  Set Error Capture [on]

                  Set Field [ Students::StudentID ; GradYear & StudentIDs::Sequence + 1 ]

                  If [ Get ( LastError ) = 0 // unique validation error did not occur ]

                     Set Field [StudentIDs::Sequence ; StudentIDs::Sequence + 1 ]


                     Revert Record [no dialog ]
                     Show Custom Dialog ["Error assigning unique student ID, try again" ]
                  End IF

                  • 6. Re: Populate next ID number based on conditions.

                    It seems to me that you could put the assign StudentID in a loop to keep trying and exit when no error occurs. Maybe a bit of a random time delay to keep from having multiple records getting into an endless loop battle.

                    • 7. Re: Populate next ID number based on conditions.

                      clayhendrix This is off topic but, I have seen many school record systems that have a place for parents and other that are allowed to pick up students but, never have I seen one that has way that to keep information on those whom are not allow to pick-up a student should include pictures if possible.

                      • 8. Re: Populate next ID number based on conditions.

                        The system I wrote does have that function. I store the individual(s) who are NOT allowed to pick-up students or have contact with students in the same table as I do the rightful guardians and pickups. Table is titled Adults. There are fields populated for each record that indicate what type of Adult this is: parent/guardian, emergency pickup authorized, pickup not authorized, no contact ordered by court, etc.


                        All records in the Adults table can have a picture uploaded. It's easy to get pictures of the Adults that are authorized for pickup, but it seems most of those personal relationships with individuals that parents do NOT want to have contact with their child are such that the rightful parents do NOT have pictures of the restricted individual. In those rare circumstances where a photo is provided by the guardian, we do have those pictures.


                        All records from the Adults table that are related to the Student records are displayed in a portal filtered for authorized pickup on one tab. Another portal on another tab is filtered for those that are prohibited from picking up the child or prohibited from having contact with the child.


                        That's how I do it. Beyond that, it takes STAFF TRAINING or the information is worthless.

                        1 of 1 people found this helpful
                        • 9. Re: Populate next ID number based on conditions.

                          Nice to see that someone else thinks the same about child safety as I do. True the staff has to use the information but, they can't act on it if it's not there.