Create new records ONLY for newly added linked record

[Lisa_Bauer]

Used Case Scenario:

Table Set Up:

• I have a Requests Table, a Designers Table and the North America Availability Table.
• Requests come into the Requests Table but based on the requested roles, there are automations set to create a linked record in the NA Availability table for each of the requested roles. (i.e. if a request comes in with a request for a coordinator, program manager and a director, 3 new records will be created in the NA Availability table)
• The Designers Table is also linked to the North America Availablity Table so that any requested roles and any designers who are considered for these roles are linked via the Designers Table. 

1. A new request for a coordinator has been submitted (this record will have an "Open Need" tag) - see image 1
2. I originally add "Lisa Bauer" as a potential option (under the Being Considered field) - see image 1
3. An automation will create a new record in the same table for Lisa Bauer (with a "Being Considered" tag) - see image 2
4. The next day, I decide to also add "Will Getter" as another option (under the Being Considered field) - see image 3

This is where I need your help!

Is there a way to set up the automation so that it will only create a new record for Will? NOT Lisa and Will? 

Image 1

Image 2

Image 3

[Alexey_Gusev]

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

[Lisa_Bauer]

@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!) 

[Stephen_Orr1]

What does your automation look like?

[Lisa_Bauer]

@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. 

[Alexey_Gusev]

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).

[Stephen_Orr1]

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

[Lisa_Bauer]

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....

[Lisa_Bauer]

@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! 

[Stephen_Orr1]

Glad to hear! How did you solve it?