I'm working on an employee directory and the context I'm trying to optimize for is when an organization is downscaling.

My base has these tables

In short what I'm trying to achieve is that when an employee is matched with a New Org Role a new record is automatically created in Assessments where I list the employee, the role and the manager that is reponsible for assessing that employee for the role. The plan is to then send a notification to the manager to conduct the assessment. It should look something like this:

In this example the main column is a concat, the "Employee" and "Assessment" by column is linked to the Employee Directory and "New Org Role" linked to New Org Roles. The "Assessment" column is a free text field that I plan to expose in a form. 

My challenge is that an employee can be a candidate for several roles, and a role can have several candidates - as can be seen in this example.

I have gotten as far as parsing the candidates field into text with an IF and Replace 

With that I can create new records in the Assessment table via an automation. But in that same automation I'm having a hard time appending the right role (naturally as I have parsed it to text and not a relation).

so here I am a bit stuck and wondering if anyone has any ideas on how to solve this in either a different way or with som automation magic that would help me on the way?