Multi select values from a reference column

I have two sheets, one has project names and a second that has employees names.
On a form, I need to select a project and assign multiple people to it. I am using EnumList to select multiple employees to add, but it is not working as desired. The selected multiple employees end up on the row; I need them on separate rows so that I can show them individually on a slice/report. I need them individually as I need to show hours assigned to each employee,

You need a 3rd table, a linking table, to establish your “many-to-many” relationship connections. Each record of this 3rd table will reference a project record, and an employee record, as well as any other values like hours assigned.

3 Likes

@Marc_Dillon thank you for a prompt response!
Sorry, my question was not clear!
I do have a third table (EMP-PROJ), but when I select a project from the PROJ table and multiple employees from the EMP table, I get one line on the third EMP-PROJ table. On this third, EMP-PROJ table, I would like to populate one line for each employee. Currently, multiple employees are populated on the same cell.

Change the employee ref column in your third table from EnumList to just Ref, then instead of selecting multiple employees in one record, you create multiple records.

1 Like

@Marc_Dillon thanks again for the response!
Adding multiple records is an option but can be very tedious as the number of projects at a time can be over 10 and the employees being assigned to each project can be over 50.
But if that is the only way, we will have to live with it or think about managing the assignments outside the app and import them.

This is the setup that I would suggest if you need to add additional information, such as the assigned hours. May not be the “only” way.

It is possible to setup automated record creation for this.
You could start by having an EnumList, base type Ref to Employees, just for selection of employees.
Then setup an action “Execute action on multiple rows” that points to this EnumList, and to the Employees table, and to a 2nd action in the Employees table.
That 2nd action will be a “create new record in another table”.
You’ll need to figure a way to select the key value of the Project record to assign during the 2nd action, perhaps by flagging the record somehow.

This is a bit complex, and I didn’t give much detail (due to lack of time), but I hope it’ll put you in the right direction.

3 Likes

Thank you @Marc_Dillon! You are awesome!
I was able to create selection of the multiple employees on the form and place them on separate row. I am thrilled.
Now I need to figure out the execution of the actions, and I will be in good shape.

1 Like