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.

[Alexey_Gusev]

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.

[Lauren_S]

Alexey_Gusev:

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.

[Alexey_Gusev]

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:

[Lauren_S]

Alexey_Gusev:

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 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?

[Alexey_Gusev]

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’} }})

[Lauren_S]

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