Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

(Many-to-many) Creating new records from on a separate table when a linked record is updated on Table 1

Topic Labels: Base design
1553 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Chad_Smith1
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a scenario with a many-to-many relationship and trying to achieve an outcome where a new record is created on a separate table any time a linked record from Table 1 is updated.

For example, Product #1 is assigned to Purchase Order A and Purchase Order B. So if I add Purchase Order and B to Product #1, those should be separate line item on the second table.

Is this possible?

4 Replies 4

Hi there, yes that is possible. Basically you can use a lookup field to get the field which will be affected by an update. If that field is updated, you use that as a trigger in an automation to then create a new record in a separate table or wherever you want to create that record.

Thank you for the response! Was able to achieve this, however there was a subsequent issue where more than 2 records on Table 1 are being grouped on the second table by Purchase Order A, B, etc…

For example:

Product 1, Purchase Orders A & B
Product 2, Purchase Orders B & C

Should output:

Product 1, Purchase Order A
Product 1, Purchase Order B
Product 2, Purchase Order B
Product 2, Purchase Order C

(Not grouped by the linked Purchase Order field in the second table)

@Chad_Smith1 This is a known issue/feature. Grouping operates on the full contents of a field, not on the sub-parts (if any).

@Chad_Smith1

A many-to-many relationship has 3 tables, not 2 tables. You can get what you want in the 3rd table, but you’ll need to restructure your base to become a many-to-many relationship.

Here’s a support article on many-to-many relationships: