Skip to main content
Question

Assigning employees ID#'s based on their programs

  • February 27, 2019
  • 0 replies
  • 66 views
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.




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.


Any help would be appreciated.
    This topic has been closed for replies.

    ... it's a very difficult project and will probably never work right after you spend 10 hours trying to make it work.  I have tacked these kind of projects before, and imho it just ain't worth it.

    I suggest that you get your uniqueness in the [Record ID#] and create a prefix or suffix as you like.  The employee numbers will not be sequential within Programs and I would suggest that you "push back" against that part of the request.

    You can then also have an override field to override the generated EE # for those EEs in multiple programs.

    If you did want to try to automate this (which I really do not recommend), then you will need to have a table with programs and the EE's will be children of those programs.  Then you need to summarize up the max record ID# for all EE's in that program and look that up down to the EEs and then snapshot it.  Then in fact you need to change the summary field to be the max of that snapshot field.

    If you somehow end up with duplicates then you will need to manually override.  It's a quagmire.






    Terms & ConditionsAccessibility statement