I was tasked with creating a method to assign an employee ID# to all active employees, however i have hit a bit of a wall while trying to do this project due to the conditions for which an ID# is assigned.
Here is the situation:
- All employees are assigned a program (OMHC, PRP, Group Home, or SUD), most employees are only assigned one program but there are a select few who are a part of 2 or 3.
-Numbers should generate in chronological order by program type. If program is PRP, number should begin with 1, Group Home 2, OMHC 3, SUD 4. ID#'s are 4 digits, so an ID# for OMHC would look like : 3001, 3002, 3003 etc.. (employees #'s cannot start with 0 : 1000, 3000 etc..)
-Employees who are a part of multiple programs will receive a separate ID# for each program EX: An employee in PRP and OMHC might have 3002 as well as a separate number 1005.
I have created a new test table to try and complete this task that looks like this.
">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1778799/RackMultipart20190226-105894-172plsr-Capture_inline.PNG?1551218186">
I had planned to use a relationship to the table itself and than use summary fields to take the maximum employee ID# for a program and add 1 to create a new ID, but this is where i am stuck as the summary field does not work as intended and i am not sure how to account for employees who are apart of multiple programs.
">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1778801/RackMultipart20190226-5759-ylo1ey-Capture_inline.PNG?1551218603">
">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1778802/RackMultipart20190226-8453-7063ex-Capture2_inline.PNG?1551218634">
Any help would be appreciated.