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

[Chad_Smith1]

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?

[Rupert_Hoffsch1]

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.

[Chad_Smith1]

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)

[Justin_Barrett]

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

[ScottWorld]

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

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...