Jun 09, 2023 04:13 AM - edited Jun 09, 2023 04:59 AM
Used Case Scenario:
Table Set Up:
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
Jun 09, 2023 04:48 AM
Hi,
please clarify linking of 2nd and 3rd field. Are they linked to other tables or you are using linking to the same table?
Jun 09, 2023 04:57 AM - edited Jun 09, 2023 05:00 AM
@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!)
Jun 09, 2023 05:54 AM
What does your automation look like?
Jun 09, 2023 06:06 AM
@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.
Jun 09, 2023 07:22 AM
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).
Jun 09, 2023 07:24 AM - edited Jun 09, 2023 07:40 AM
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:
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
Jun 09, 2023 11:40 AM
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....
Jun 10, 2023 04:33 AM
@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!
Jun 10, 2023 04:43 AM
Glad to hear! How did you solve it?