Combining 2 tables dynamically

[leighjackson]

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

[#2]

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.

[#3]

Hi @leighjackson 

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

So, create "List 1"
and "List 2"


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


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"


Do the same for a new lookup column "Training"



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


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