+2Hello,
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:
the case is if I updated by adding item name ‘A’ to SO with subject ‘ee’, the result will automatically update like this:
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?
Best answer by Kamille_Parks11
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:
IF(
AND({Subject}, {Item Name}),
SUBSTITUTE({Item Name}&',', ',', '-' & {Subject} & ',')
)
Trigger:
Action:
Now you’ll have this in your Logistic table automatically.
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}, '')
End Result (before you add your Lookup fields)
+27Typically, 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:
IF(
AND({Subject}, {Item Name}),
SUBSTITUTE({Item Name}&',', ',', '-' & {Subject} & ',')
)
Trigger:
Action:
Now you’ll have this in your Logistic table automatically.
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}, '')
End Result (before you add your Lookup fields)
+27There’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
+2Typically, 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:
IF(
AND({Subject}, {Item Name}),
SUBSTITUTE({Item Name}&',', ',', '-' & {Subject} & ',')
)
Trigger:
Action:
Now you’ll have this in your Logistic table automatically.
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}, '')
End Result (before you add your Lookup fields)
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’?
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…
+27Wow, 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’?
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.
+2If 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…!
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.