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

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.

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?

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

Thanks for update @ScottWorld - heres hoping!!

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!

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

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:

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)

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.

Absolutely love the support on these forums.

@Adam_TheTimeSavingCo thanks so much for putting this together but I don’t think it will work with my configuration. I’ll try and provide some more information with screenshots.

Table 1: This is a table where we map Product, Qty & Project for an order.

• We use ‘Helper - Product’ as the reference to use to look for matching components in Table 2.

Table 2: Is a list of components that each Product needs to have shipped when leaving our factory.

• We use ‘Helper - Line Item’ as the reference which is looked upon to find the match from Table 1.

Table 3: This is where we build a list/ledger of all the components.

• We add the matching component name from Table 2 into ‘Thing’ field.
• We add the matching ‘Qty’ from Table 2 into ‘Qty of Thing to Line Item’
• We add the ‘Qty (unit not doors)’ from Table 1 into ‘Qty of Line Item’
• Then we also map the project, product and component (named Thing) as linked records.

What we’re actually trying to achieve with Table 3 is a packing list for our packing team which they can mark off as ‘Packed’ once its on a pallet shipped.

The crazy thing is that Airtable Automations actually can Find the correct information, it just cant create the multiple records at once. Which is a real shame because it would be a seamless solution.

Any help or tips would be awesome.

@ScottWorld I also appreciate your suggestion. I have added a screenshot of the current configuration in Make I have set up. Its more complex because of the 3 tables I believe (maybe I’m wrong). I’m just really keen to have the configuration in Airtable as Make is proving to throw to many errors and is very cumbersome to get working again.

Once again thanks for the support!

Absolutely love the support on these forums.

@Adam_TheTimeSavingCo thanks so much for putting this together but I don’t think it will work with my configuration. I’ll try and provide some more information with screenshots.

Table 1: This is a table where we map Product, Qty & Project for an order.

• We use ‘Helper - Product’ as the reference to use to look for matching components in Table 2.

Table 2: Is a list of components that each Product needs to have shipped when leaving our factory.

• We use ‘Helper - Line Item’ as the reference which is looked upon to find the match from Table 1.

Table 3: This is where we build a list/ledger of all the components.

• We add the matching component name from Table 2 into ‘Thing’ field.
• We add the matching ‘Qty’ from Table 2 into ‘Qty of Thing to Line Item’
• We add the ‘Qty (unit not doors)’ from Table 1 into ‘Qty of Line Item’
• Then we also map the project, product and component (named Thing) as linked records.

What we’re actually trying to achieve with Table 3 is a packing list for our packing team which they can mark off as ‘Packed’ once its on a pallet shipped.

The crazy thing is that Airtable Automations actually can Find the correct information, it just cant create the multiple records at once. Which is a real shame because it would be a seamless solution.

Any help or tips would be awesome.

@ScottWorld I also appreciate your suggestion. I have added a screenshot of the current configuration in Make I have set up. Its more complex because of the 3 tables I believe (maybe I’m wrong). I’m just really keen to have the configuration in Airtable as Make is proving to throw to many errors and is very cumbersome to get working again.

Once again thanks for the support!

Ah, hmm, okay, let me know if I’m understanding things right

1. You’ve got Projects, and each Project has different Products attached to it, and each Project may need different quantities of each Product that’s attached to it
2. Each Product is made up of different quantities of different Components. The components and quantity of each component that make up a Product stay the same regardless of which Project it’s attached to

And the thing you want out of this is a list that contains:

1. All the Components and how much of each are needed for the Project
2. What Product each of those Components are for
3. The Project name

Is that right?

@Adam_TheTimeSavingCo that’s absolutely correct.

Appreciate all the help!

Roger that. Do you have a Pro account? And how comfortable are you with scripting?

I’ve been trying to figure out how we could accomplish this without automation scripting, and I think I’ve got something that might work but it’s so convoluted that I think we might just want to do this with scripting instead.

If we’re going down the scripting route, depending on how comfortable you are with scripting, I can just pass you code that you can modify as needed

If you’re not comfortable with scripting, private message me a link to a duplicate of your base with the sensitive data removed and from there I can put something together for you.

@Adam_TheTimeSavingCo you’re an absolute legend.

I’m pretty new to scripting but keen to learn more, so happy to have a crack at implementing if you’re happy to share the script?

Absolutely love the support on these forums.

@Adam_TheTimeSavingCo thanks so much for putting this together but I don’t think it will work with my configuration. I’ll try and provide some more information with screenshots.

Table 1: This is a table where we map Product, Qty & Project for an order.

• We use ‘Helper - Product’ as the reference to use to look for matching components in Table 2.

Table 2: Is a list of components that each Product needs to have shipped when leaving our factory.

• We use ‘Helper - Line Item’ as the reference which is looked upon to find the match from Table 1.

Table 3: This is where we build a list/ledger of all the components.

• We add the matching component name from Table 2 into ‘Thing’ field.
• We add the matching ‘Qty’ from Table 2 into ‘Qty of Thing to Line Item’
• We add the ‘Qty (unit not doors)’ from Table 1 into ‘Qty of Line Item’
• Then we also map the project, product and component (named Thing) as linked records.

What we’re actually trying to achieve with Table 3 is a packing list for our packing team which they can mark off as ‘Packed’ once its on a pallet shipped.

The crazy thing is that Airtable Automations actually can Find the correct information, it just cant create the multiple records at once. Which is a real shame because it would be a seamless solution.

Any help or tips would be awesome.

@ScottWorld I also appreciate your suggestion. I have added a screenshot of the current configuration in Make I have set up. Its more complex because of the 3 tables I believe (maybe I’m wrong). I’m just really keen to have the configuration in Airtable as Make is proving to throw to many errors and is very cumbersome to get working again.

Once again thanks for the support!

What kind of error message are you getting in Make?

Roger that, in that case, let’s use scripting to just create the records. We’ll use an automation task to find the data we need

Here’s what you’ll need to get started:

//input.config() allows you to grab data trigger / action preceding this automation script
let inputConfig = input.config()
//You should be in the Scripting action of the automation setup, and should see a column on the left.  In there, we add a value called `matchingRecordIds`, where the dynamic value is the comma separated list of records found
let matchingRecordIds = inputConfig.matchingRecordIds

let tableToUpdate = base.getTable('Table 1')

let updates = matchingRecordIds.map(recordId => ({
    "fields":{
        //format: 
        //"[FIELD NAME]" : "[FIELD VALUE]"
        //e.g.
        //"Name": "New name"
        //For link fields, the format is:
        //"[FIELD NAME]" : [{id: [RECORD ID OF RECORD WE WANT TO LINK TO]}]
        //e.g.
        //"Thing": [{id: 'recKwiNgmGbihcSHN'}]
        //or if you're passing in a value:
        //"Thing": [{id: thingRecordID}]

        //Examples: 
        //"Name": "New name",
        //"Thing": [{id: 'recKwiNgmGbihcSHN'}]
    }
}))

while (updates.length > 0) {
    await tableToUpdate.createRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

This is about as specific as I can make it with the information I have now; let me know if you need more help

@Adam_C - thank you so much for this.

I’ve managed to get it to post blank records into Table 3. However, I’m using the ‘Find Records’ function in automations to filter the components from Table 2. I cannot get the script to post the actual text result. It will just write the text I set up in script.

Any suggestions?

Absolutely love the support on these forums.

@Adam_TheTimeSavingCo thanks so much for putting this together but I don’t think it will work with my configuration. I’ll try and provide some more information with screenshots.

Table 1: This is a table where we map Product, Qty & Project for an order.

• We use ‘Helper - Product’ as the reference to use to look for matching components in Table 2.

Table 2: Is a list of components that each Product needs to have shipped when leaving our factory.

• We use ‘Helper - Line Item’ as the reference which is looked upon to find the match from Table 1.

Table 3: This is where we build a list/ledger of all the components.

• We add the matching component name from Table 2 into ‘Thing’ field.
• We add the matching ‘Qty’ from Table 2 into ‘Qty of Thing to Line Item’
• We add the ‘Qty (unit not doors)’ from Table 1 into ‘Qty of Line Item’
• Then we also map the project, product and component (named Thing) as linked records.

What we’re actually trying to achieve with Table 3 is a packing list for our packing team which they can mark off as ‘Packed’ once its on a pallet shipped.

The crazy thing is that Airtable Automations actually can Find the correct information, it just cant create the multiple records at once. Which is a real shame because it would be a seamless solution.

Any help or tips would be awesome.

@ScottWorld I also appreciate your suggestion. I have added a screenshot of the current configuration in Make I have set up. Its more complex because of the 3 tables I believe (maybe I’m wrong). I’m just really keen to have the configuration in Airtable as Make is proving to throw to many errors and is very cumbersome to get working again.

Once again thanks for the support!

Hi,
You can create multiple items linked to your first table by single automation, putting their names comma-separated into link field. If name already exists, it links to it. If not, it creates new record and link.
I had scenario when first automation perform such ‘auto create’, adding words 'Create new ’ to guarantee that such record will not exist. And then second automation follows all records containing 'Create new ’ in name, and do other job, removing the phrase at final step.
In short, first automation ‘marks’ multiple N records, then second runs N times, for each record marked.
But it can’t be done when your primary field is formula. Some people even use formula in primary field to protect from new records creation, when somebody enters wrong name into link field.

I am following this thread to set up an automation that is similar, but cannot seem to connect the dots.

I have Tables for Requests, Vendors and Invites.
When a new Request record is created for a particular Service (this is a selection field in the Request table), I need to find the Vendors who match the Service (also a selection field in Vendors table) and then create a new record in the Invites table with the Request and Vendor for each of the Vendors that match.

For example, a Request is entered for Service “Cleaning.”
I need to find all of the Vendors in the Vendor table that show Cleaning as a Service.
ABC Cleaning, Super Cleaning and Best Maintenance are matches.
Automation creates a new record for each match in the Invites table. The Request ID from step 1 is entered and the Vendor from Step 2 is entered, and each record has a Status field that must show “Invited.”

Currently, I can’t seem to get these to be entered as three separate records in the Invites table.

My experience with scripting is limited. I am wondering if you can help with this, as it seems to be fairly straightforward for someone with more experience!

Hi,

With more fields, script is quite complex, with 2d-array, but in your case, where first value is static and third is constant, it should be smth like

Input:
1.request (insert request id from step 1)
2.vendors (insert your list of vendors from step 2, ‘make a new list of’ - ‘field values’ - …)

Script:

const table=base.getTable('Invites')
const {request,vendors}=input.config()
const create=ven=>({fields:{'RequestID':request,'Vendor':ven,'Status':'Invited'}})
const crt=vendors.map(create)
while (crt.length) await table.createRecordsAsync(crt.splice(0,50))

note that if your field type(s) in ‘Invite’ other than just text (for example, link or single-select, you should update your third line (press API, choose field types and see document and example)
Linter will highlight 5th line until you make it all correct.

const table=base.getTable('Invites')
const {request,vendors}=input.config()
const create=ven=>({fields:{'RequestID':request,'Vendor':ven,'Status':'Invited'}})
const crt=vendors.map(create)
while (crt.length) await table.createRecordsAsync(crt.splice(0,50))

Thanks, Alexey! I think I’m getting there. The field type in the Invite table for Status is a Single Select. How would I change Line 3 to make this work correctly? I am getting an error that the field cannot accept the value.

Sorry, I missed your reply.
I think you already found the answer, but for those who may read this topic later, for Single-select it will be

const create=ven=>({fields:{ 'RequestID':request,'Vendor':ven,'Status':{name:'Invited'} }})

note: if your single-select field has no ‘Invite’ choice, it will reject the update.
you should add it to existing choices before using, procedure described here:

Thanks for getting back. I’m still working on this! The fields for RequestID and Vendor are linked fields back to those tables. How do I fix those?

the cell value of link record
should be array:
[ {id: recXXXX},{id: recXXYY}, {id: recXXZZ}… ]
with any number of objects {id: recXXXX} , including 0 and 1

so, in your case:

const link=recID=>({id:recID})

const create=ven=>({fields:{‘RequestID’:[link(request)],‘Vendor’:[link(ven)],‘Status’:{name:‘Invited’} }})

Thanks for your help, Alexey. I got some extra phone help on this and now it’s all set.