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?
+21Hi 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.
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)
+21Thank 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).
+35A 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:
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.