Help

Re: How to Seperate 'linked to' field to individual record and update automatically

Solved
Jump to Solution
2460 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Operation_Asta_
4 - Data Explorer
4 - Data Explorer

Hello,
I’m Sorry if this topic may have asked before, but I’ve tried searching and didn’t found the similar one.

I have 3 sample tables as kind of below, and I don’t know how to realize it to Airtable:

1

the case is if I updated by adding item name ‘A’ to SO with subject ‘ee’, the result will automatically update like this:

2

My main question is how to separate a ‘linked to’ field which is can contain multiple values, to be an individual record/ row, and update it automatically from SOTable?

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

Typically, Airtable works by doing the reverse of what you’re asking: you create a new record in the Logistics table and fill in the {Item Name} and {Quotation} fields (both of which would be of the Link to Record field type).

It sounds like you want to link Items to a Sales Order, and have Airtable automatically create a new record in Logistic for each unique pairing. That’s been done a number of ways in solutions found on this forum. I would recommend searching for “junction table” or “create junction records automatically” or something to that effect. Solutions include Automations and Scripts.

You could for instance try doing this:

  1. Create a Formula field in the Sales Orders table that creates the names of each Logistics table record:
IF(
  AND({Subject}, {Item Name}),
  SUBSTITUTE({Item Name}&',', ',', '-' & {Subject} & ',') 
)
  1. Use an Automation that copies the value of the Formula field into a Link to Record field pointing to Logistic table.
    image

Trigger:

image

Action:

image

Now you’ll have this in your Logistic table automatically.
image

To link the Item to the Logistic record, you could use a similar strategy as above.
4. Create a Formula field in the Logistic table that gets the Item name:

SUBSTITUTE(Name, '-'&{Sales Order}, '')
  1. Create an Automation that copies the Formula into the Link to Item field. (Same concept, except the trigger would be “when record is created”)

End Result (before you add your Lookup fields)
image

See Solution in Thread

5 Replies 5
Kamille_Parks
16 - Uranus
16 - Uranus

Typically, Airtable works by doing the reverse of what you’re asking: you create a new record in the Logistics table and fill in the {Item Name} and {Quotation} fields (both of which would be of the Link to Record field type).

It sounds like you want to link Items to a Sales Order, and have Airtable automatically create a new record in Logistic for each unique pairing. That’s been done a number of ways in solutions found on this forum. I would recommend searching for “junction table” or “create junction records automatically” or something to that effect. Solutions include Automations and Scripts.

You could for instance try doing this:

  1. Create a Formula field in the Sales Orders table that creates the names of each Logistics table record:
IF(
  AND({Subject}, {Item Name}),
  SUBSTITUTE({Item Name}&',', ',', '-' & {Subject} & ',') 
)
  1. Use an Automation that copies the value of the Formula field into a Link to Record field pointing to Logistic table.
    image

Trigger:

image

Action:

image

Now you’ll have this in your Logistic table automatically.
image

To link the Item to the Logistic record, you could use a similar strategy as above.
4. Create a Formula field in the Logistic table that gets the Item name:

SUBSTITUTE(Name, '-'&{Sales Order}, '')
  1. Create an Automation that copies the Formula into the Link to Item field. (Same concept, except the trigger would be “when record is created”)

End Result (before you add your Lookup fields)
image

There’s also this Script in the Marketplace that creates all possible junction records: Create Junction Table Script

And this Extension developed by myself: Junction Assistant Extension

Wow, Really Appreciate your feedback and help on this!
I’m almost on that, just miss the one thing. how to automatically update field ‘Sales Order’ on Table ‘Logistic’?

image

I’ve tried to use automation to ‘update record’ when ‘logistic’ field on ‘Sales Order’ Table is updated, then update field ‘Sales Order’ on ‘Logistic’ table with value of ‘subject’ of ‘Sales Order’ Table, but it didn’t work…

If you’re following the method I used, you don’t need to do anything to the {Sales Order} field in the Logistic table. That field is the inverse of the {Logistic} linked field in the Sales Order table.

Since the first automation is copying the formula into the {Logistic} field, it is taking care of linking Sales Order records to Logistic.

Ah! Thanks a lot for the insight, you’re right. I’ve found my mistake and fixed it.
Really appreciate your help…!