Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

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

Topic Labels: Automations
1599 25
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

25 Replies 25

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.

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

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.