Find Records in One Table and Create Multiple Records in Another Table from the Result

[Harry_McDonald]

Hey Community,

I need a hand to know if something is possible.

• I have Products on Table 1 which are created manually.
• These products are made up from components which are mapped as records in Table 2 (anywhere from 2 - 20 components per product).
• When a product is added to Table 1 I’m trying to find the matching components in Table 2 and then create a list of the components as records in Table 3 (as I need to perform order specific calcs here).

I can see using the automation and find records I’m able to achieve this for a single record but not multiple at once, is there something I’m missing or a simple script I could use to overcome this?

Any help would be appreciated.

[ScottWorld]

As you mentioned above, this can only be accomplished with scripting. If you don’t want to mess around with JavaScript, you would have to turn to an external automation tool such as Make.com, which is a low-code automation environment.

[Harry_McDonald]

Hey @ScottWorld thanks for the reply! I have set up an automation in Make.com which works but seems to be throwing up errors on every second run.

I was keen to keep the solution within Airtable as it seems to be more stable. I simply just want to return the name of components from Table 2 to Table 3 based on a match of a field in table 1 and table 2.

If there are any JS templates you know of - I would greatly appreciate it?

[ScottWorld]

There are several JavaScript experts in this forum… hopefully they will chime in!

[Harry_McDonald]

Thanks for update @ScottWorld - heres hoping!!

[TheTimeSavingCo]

Hi Harry, I’ve created something for you here that might solve your problem. You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button. You’ll then be able to see all the formulas, how the fields are set up, automations, etc

The idea is we make a formula field that’ll create a comma separated list of unique names in the format “[Product Name]|[Component Name]”, and we use the automation to paste this value into a field that’s linked to Table 3. Here’s a gif of it working:

I assumed that you’d need the records in Table 3 to link back to the components in Table 2, and that’s accomplished by creating a formula field that’ll extract the component name for us, and then using an automation to paste said value into a field that’s linked to Table 2

Note that this will consume 1 task per product, as well as 1 task per component that said product is linked to

If you’re limited by the number of tasks, you could use a scripting action for your automation which would solve this pretty easily. That’s a Pro account thing though, and you’d have 50k tasks if you were on a Pro account anyway, so that probably isn’t an issue

Your final option would be to getting a scripting app done up, and that would mean you having to click the “Run” button for the app every time you want records created

Let me know if you have any questions!

[TheTimeSavingCo]

I’ve thrown together a scripting version of it here in case you’d rather do that

[ScottWorld]

The problem with this solution is that it won’t always create records in Table 3. If the same value already exists in Table 3, it will simply link to the existing record instead of creating a new record. This is why I recommended Make.com above, which is as simple as this 2-step automation:

[TheTimeSavingCo]

The core of it is really creating a unique ID per component linked to the product. The solution includes the product name for this purpose (I was assuming the product names would be unique!), resulting in the format:

[Product name]|[Component name]

If the product name is not unique, then we can add an auto number, resulting in the format:

[Autonumber] [Product name]|[Component name]

And if you were really worried about edge cases where both the auto number and the product name were somehow the same you could just use the record ID instead, which would work 100% of the time but would look icky

(Oh it’s worth noting for other readers that we’re specifically discussing the solution that does not involve scripting; the scripting solution just creates records on it’s own and doesn’t care about unique names or anything)

[ScottWorld]

Nice! I like that idea! That’s a good workaround to add in an always-unique value into the formula, such as an autonumber (or record ID). I didn’t think about that possibility earlier.

Of course, as you mentioned, all of these workarounds are relatively “icky”. This is not the fault of your solutions. Rather, it is just the limitation of Airtable’s automations at this moment in time. I come from such a “clean programming” background that I always try to look for the cleanest solutions, but I realize that to keep everything in Airtable at this point, we do have to deal with some ickiness.

Or turn to scripting, which I do not know.