Create a new record in a new table from a unique value in another table

[Nikiska]

Hi everyone!

Okay.. so... bear with me... I have people that have multiple roles {Contact Type} in the company (Humans Table)

I also have a Junction table (CT Category by Human) so I can manipulate the data

so in my third table (CT status NEW) now that each role {Contact Type} is on a separate row I can add a different 'Status' to each role

My issue arises when I add a new role to a contact in the 'Humans' Table. I have a working automation that populates the Contact Type row in the junction table with the new human that has been assigned this role....

But I can't work out how to get that new info into a new row in the third table in order to allocate a status for it.... What I have been able to do is create a fourth table that creates records anytime the [Contact Type] field is updated in the first table... like so..  mainly so i can keep track of how often role changes happen. Table names 'HU to CT new':

I'm hoping i haven't lost anyone so far... so currently AA2 AA3 human is a Potential Volunteer, but he has just signed up to be a Subscriber too.  I already have a record in my 'CT Status NEW' table showing this, but I need to run an automation that says if a new record has been created in table 'HU to CT new' can it check in the 'CT Status NEW' table that if it's not there to create a new record.  I have matched the fields so the find value should be the 'fx string' formula in the 'HU to CT new' table as that mirrors the primary field in the CT Status New table.

Please please, someone put me out of my misery as i have spent hours on this and nothing i do works!!

[Nikiska]

@ScottWorld , @Jake_Wilson any thoughts 🙂 

[TheTimeSavingCo]

Hmm, if your names and roles will never have commas, you could:
1. Create a formula field 'Humans' that'll output a comma separated list that consists of '[NAME] - [ROLE]'  (You can use the 'SUBSTITUTE()' formula for this
2. Create an automation that'll update whenever the 'Contact Type' field in 'Humans' is updated, and give it an 'Update Record' action where it'll update the triggering record and paste the value of the formula field into the linked field to the 'CT Status New' table

Should do what you need!  Let me know if you could use an example and I'll whip one up for you

[Nikiska]

@TheTimeSavingCo That would be soo great, thank you as it is says insufficient permissions in my automation!. My guess is that the primrary field in CT Status NEW is a formula field which cant be modified!!

All I need is a new record created when someone takes on another role that isn't already listed in the CT Status New table 🙂

[TheTimeSavingCo]

Yeap, could you try changing the field type to a single line text field?  The data's going to be the same thing, i.e. [PERSON NAME] - [ROLE TYPE], so there's no difference in the data display

If you can't change the field type to a text field for some business reasons, I'm afraid the only other way I know how to do this would be with a script

[Nikiska · Accepted solution]

I managed to find a Solution!!: 

1.There are no changes to any of the fields or tables in the base mentioned in the original message.
2. Automation is as follows: