Splitting cells with linked records and populate a new table via automations

Topic Labels: Automations Formulas
650 4
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Skjermbilde 2023-09-14 kl. 09.29.01.png

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:

Skjermbilde 2023-09-14 kl. 09.41.22.png

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.

Skjermbilde 2023-09-14 kl. 09.34.43.png

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

Skjermbilde 2023-09-14 kl. 09.37.30.png

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?

4 Replies 4

please clarify what's the trigger and how the Roles table looks like.

The trigger would be manually adding a employee as a candidate for a role. 

The New Org Roles table looks like this:

Skjermbilde 2023-09-14 kl. 12.08.31.png

to be honest, I cannot understand what's the problem then. just put the id of trigger record to link to it.

Well, if that was the case it would be easy. If I set up an automation like that I'd end up with the problem of multiple employees in the same cell:

Skjermbilde 2023-09-15 kl. 12.16.57.png

So I need a single employee in each row with the correct role attached.