AnsweredAssumed Answered

Populate next ID number based on conditions.

Question asked by clayhendrix on Dec 21, 2016
Latest reply on May 31, 2017 by greatgrey

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.