Forum Discussion

WayneMajor's avatar
WayneMajor
Qrew Cadet
7 years ago

Assigning employees ID#'s based on their programs

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.
Forms
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    ... 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.






    • WayneMajor's avatar
      WayneMajor
      Qrew Cadet
      7 years ago
      The goal was to make the process automatic once an employee finish a payroll form, I do see what you mean about the whole thing being very tedious and quite complex... I have advocated to my supervisor that we just do the assignments manually since we are a relatively small company (~50 people) so it wouldnt be much of an issue compared to what setting up an automatic program would be. The thing is that she is looking more towards the future with this and not needing to manually input or change data once the company grows.

      I really appreciate the advice and guidelines you provided and will try to implicate them as best i can.

      Thank you.