Create new records ONLY for newly added linked record

Hi,
please clarify linking of 2nd and 3rd field. Are they linked to other tables or you are using linking to the same table?

@Alexey_Gusev , great question, sorry for missing that clarification!

The Role/Designer and the "Being Considered" fields are both linked to the same table called the Designers Table.

The records that you are seeing in the images and where I want the new records to be created are in a different table named "North America Availability". 

(just added a little more clarification in the original post!) 

What does your automation look like?

@Stephen_Orr1 , the automation looks like:

Trigger: When a record is updated in the NA Availability Table, specifically looking at the "Being Considered" field

Action: Create Record - where the Role/Designer field is filled by the "Being Considered" linked record in the original triggered record. and the Status is marked as "Being Considered". 

Challenge:

If you add more than one linked record under the "Test Being Considered" field, it creates one new record with all of the linked records under "Role/Designer". If you also remove a linked record from "Test Being Considered" field that also triggers the automation. 

Well, that's unusual, I mean two links between the same two tables, but if it needs to reflect different levels of relations, why not? 
But returning to your question - you can create the trigger from the point of candidate, that means - to add Status as lookup in linked table, so when you add Will here, in Designers table Will's Status will change to 'Open Need'.  You should also think possible further workflow steps in a same way (i mean what if a person Approved, Assigned, Rejected etc).

I think you might need to restructure your data a bit.

Am I understanding this logic correctly?
- Role requests come in via form to a table that is essentially your unique list/SoT of Roles
- NA Availability (essentially "Role Assignments") is where you map Designers to Roles
- Designers is a unique list of who can be assigned to a role
- You're looking for a way to track metadata about the relationship between a single Designer and a Role, currently this is handled by creating new Role Assignment records per Designer via automation in the same table

If this is correct, here's what I suggest (it's very close to what you have) note: take a manual snapshot and/or duplicate the base before trying or create a new base:

1. Set up your tables to be linked as:
   Role Requests <-one-to-many-> Role Assignments <-many-to-one-> Designers (for the relationship types: in Role Assignments, disable link to multiple for both linked records)
2. Make your primary field in Role Assignments a formula field that combines Role and Designer linked records: {Role} & " | " & {Designer}
3. In Role Assignments, group by Role (linked record to Role Requests)
4. Setup an automation that creates a new record in Role Assignments whenever the status of a role in the Role Requests table (I'd call this table Roles and make a view for "requests" based on status btw) changes to Being Considered or something that tells you you're ready to link a Designer

Your workflow is now: Role request comes in, you approve it for consideration in the Role Request table via a status field, an empty record is created in the Role Assignments table via automation, you attach Designers to the Role in the form of combo Role/Designer records in the Role Assignments table. Now you can see metadata about the relationship between unique roles and unique designers.

Btw this approach is commonly referred to as a "junction table" or "cross-reference table" approach.

Please let me know if you have any questions!

-Stephen

Thank you @Alexey_Gusev and @Stephen_Orr1 for your suggestions! unfortunately, I don't think either of those will work. But @Stephen_Orr1 , I'm also not sure if I fully understood your walkthrough. Would you have time to briefly connect via zoom? Would love to get your input on if what I'm asking for is even possible. It might not be....

@Alexey_Gusev and @Stephen_Orr1 just wanted to let y'all know that I figured out a work around last night! Thank you both for your time and input! 

Glad to hear! How did you solve it?