SOLVED

Combining 2 tables dynamically

Copper Contributor

Hi, I need to create a SharePoint lists for a training record. I will have a record of employees with Job role and some specialisations, and a list of training with the job roles that are required to complete that training.

I need to create a 3rd list which combines employee and training records to place a completed date against each training a staff member is required to do. This should then update when training requirements or job roles change. Is there a way to do this in SharePoint without rebuilding the table via a flow?

 

I.e.

List 1

ID - Name - Role

1 - Joe Bloggs - Surveyor

2 - Jane Doe - Manager

List 2

ID - Training - Required by

1 - Training 1 - Surveyor, Manager

2 - Training 2 - Surveyor

3 - Training 3 - Manager

 

Desired List 3 to dynamically update when Table 1 or 2 changes

ID - Employee ID - Training ID - Completion Date

1 - 1 - 1 - 13/2/23

2 - 1 - 3 - 24/12/22

3 - 2 - 1 - 8/6/21

3 - 2 - 3 - 8/6/21

 

Thanks in advance

 

3 Replies
best response confirmed by leighjackson (Copper Contributor)
Solution
Yes, it is possible to create a SharePoint list for tracking training records and combine employee and training records to track completed training in SharePoint without rebuilding the table via a flow. Here are the steps you can follow:

Create the Employee List: Create a SharePoint list to store employee records. Add columns for Employee Name, Job Role, Specialization, etc.

Create the Training List: Create a SharePoint list to store training records. Add columns for Training Name, Required Job Roles, etc.

Create the Training Assignments List: Create a SharePoint list to track completed training. Add columns for Employee Name (lookup column from the Employee List), Training Name (lookup column from the Training List), Completed Date, and any other relevant columns.

Create a Workflow: Create a SharePoint workflow that triggers when a new item is added to the Training Assignments List. The workflow should look up the Job Role(s) associated with the selected Training Name in the Training List, and update the corresponding Employee records in the Employee List with the Completed Date for that Training Name.

Create a View: Create a view in the Training Assignments List that shows the Completed Date for each Employee and Training Name. You can also add a filter to show only the records that are relevant to a specific Job Role or Specialization.

Hi @leighjackson 

 

you could use lookup columns from "List 3" back to the lists "Lists 1" and "List 2"

 

So, create "List 1"
1list.pngand "List 2"
2list.png

Now create "List 3" and add a new Column "Employee" as a lookup column
3list.png

Set "List 1" as the datasource and "Name" as a column to pull primary values from. Then expand "More Options" and pull role as a secondary column via "Add additional columns from source list"
4list.png

Do the same for a new lookup column "Training"

5list.png

Now you can create a new item in "List 3" and choose just the employee and the training
6list.png

But this view will now dynamically pull the employee role and the training requirements from the other lists.

Now if the employee name or the training or the "required by" value change in the other lists, these changes will be automatically displayed in this list.

But this does not insert new rows into "List 3" if you i.e. enter a new employee, it justs updates the data.

Best Regards,
Sven

@jaortizgolf78 Perfect thanks. I have done just this and after a bit of faffing with power automate so didn't create records that already existed I have it working,

1 best response

Accepted Solutions
best response confirmed by leighjackson (Copper Contributor)
Solution
Yes, it is possible to create a SharePoint list for tracking training records and combine employee and training records to track completed training in SharePoint without rebuilding the table via a flow. Here are the steps you can follow:

Create the Employee List: Create a SharePoint list to store employee records. Add columns for Employee Name, Job Role, Specialization, etc.

Create the Training List: Create a SharePoint list to store training records. Add columns for Training Name, Required Job Roles, etc.

Create the Training Assignments List: Create a SharePoint list to track completed training. Add columns for Employee Name (lookup column from the Employee List), Training Name (lookup column from the Training List), Completed Date, and any other relevant columns.

Create a Workflow: Create a SharePoint workflow that triggers when a new item is added to the Training Assignments List. The workflow should look up the Job Role(s) associated with the selected Training Name in the Training List, and update the corresponding Employee records in the Employee List with the Completed Date for that Training Name.

Create a View: Create a view in the Training Assignments List that shows the Completed Date for each Employee and Training Name. You can also add a filter to show only the records that are relevant to a specific Job Role or Specialization.

View solution in original post