Create multiple records based on a quantity field with a Script

[Sam_Cederwall]

I would like to create a script that creates a number of records based on a quantity field. Essentially, if there is a record that has a quantity (x) of more than 1, I want to copy and create that record x amount of times within the table, then lower the quantity for the record and each subsequent record to 1.

I have some of the script set up. Are there any ideas on how I could specify the # of records to create, based on the quantity field, using createRecordsAsync in the Scripting Block?

As usual, thanks for the help!

[Peter_Rosasco]

Yes, thank you for that. I now have:

//Tables

let orders = base.getTable('fulfilment');
let codes = base.getTable('test');

//Record Input

let record = await input.recordAsync('Please select a order', orders);

//Record variables

let quantity = record.getCellValue('boxes');
let order = record.id;

//New record array

let recArray = [];

//Iterate by quantity

if(quantity > 1){
    for(let i=0; i < quantity; i++){
        //Push to the array
        recArray.push({
            fields: {
                'Order': [{id: order}],
            }
        });
    }

    //Create new records
    await codes.createRecordsAsync(recArray);    
}

Which runs with no errors but it is not putting the records into the new table labeled “test”

[JonathanBowen]

Well, this essentially works, as far as I can tell. There’s a bug in it:

if(quantity > 1){

should be:

if(quantity >= 1){ or if(quantity > 0){

but it seems OK. Here’s my modified script, with a bit of best practice around selecting a record from the table - the if(record) {... part

//Tables

let orders = base.getTable('fulfilment');
let codes = base.getTable('test');

//Record Input

let record = await input.recordAsync('Please select a order', orders);

if (record) {
    //Record variables
    let quantity = record.getCellValue('boxes');
    let order = record.id;
    console.log(quantity, order)
    //New record array

    let recArray = [];

    //Iterate by quantity

    if(quantity > 0){
        for(let i=0; i < quantity; i++){
            //Push to the array
            recArray.push({
                fields: {
                    'Order': [{id: order}],
                }
            });
        }
        //Create new records
        await codes.createRecordsAsync(recArray);    
    }    
}

If I start with this:

and take each record in turn, I end up with this in the 2nd table:

i.e. a record for each order to the total original boxes value

[Peter_Rosasco]

Would there be a way to keep the name field the same so if there was a 3 in the boxes column then there would just be 3 duplicate records in the test table. Thank you very much for your help, I am quite new at this.

Here is an example of what I am trying to achieve:
This would be an example of the “fulfillment” table

and this would be the “test” table

[Hannah_Wiginton]

If you need a no-code way of creating multiple (bulk) records, this is a feature of the On2Air Actions app

On2Air | Accomplish Anything with Airtable – 8 May 21

On2Air: Actions | On2Air

On2Air: Actions is an Airtable automation and workflow tool to integrate and connect your Airtable data with any other software tools used in your business. Create powerful business processes with almost 20 additional action options beyond the...

[Peter_Rosasco]

JonathanBowen:

//Tables

let orders = base.getTable('fulfilment');
let codes = base.getTable('test');

//Record Input

let record = await input.recordAsync('Please select a order', orders);

if (record) {
    //Record variables
    let quantity = record.getCellValue('boxes');
    let order = record.id;
    console.log(quantity, order)
    //New record array

    let recArray = [];

    //Iterate by quantity

    if(quantity > 0){
        for(let i=0; i < quantity; i++){
            //Push to the array
            recArray.push({
                fields: {
                    'Order': [{id: order}],
                }
            });
        }
        //Create new records
        await codes.createRecordsAsync(recArray);    
    }    
}

Thank you so much for your help, it is working exactly how I would like it now!

[Doug_Nelson1]

This works great, quick question:

Instead of having the Quantity column be just the number 1 is it possible to have that count. For example if Quantity is 3, it would make 3 records and the quantity column would then be 1, 2, 3 in order. Also erasing and renumbering the first cell with “3” in it to start at “1”.

[Lexie_Ma31]

I have replicated this with my table names but keep getting this error message: TypeError: input.recordAsync is not a function at main on line 8

[Justin_Barrett]

The above script will only work if you’re running it in the Scripting extension. If you’re trying to use it in an automation “Run a script” action, it won’t work because that input.recordAsync function doesn’t exist in the automation scripting API. In fact, both the input and output mechanisms for automation scripts are completely different, so any script in a Scripting extension that uses either of those will need to be modified to run in an automation.

To use the above script in an automation, you’ll first need to add an input variable that passes the record ID of the triggering record from the automation’s trigger step. Read through the help docs for more on adding input variables.

With that done, replace this line from the above script:

let record = await input.recordAsync('Please select a order', orders);

with these lines:

const { recordId } = input.config()
const record = await orders.selectRecordAsync(recordId, {fields: ["boxes"]})

If your input variable is named something besides “recordId”, change that name in the first of the two lines above to match the name you chose. Also, if the fields that you need to retrieve from the triggering record are different, you’ll need to replace ["boxes"] with your actual field names; e.g. ["Field 1", "Field 2", "Field 3"]

[Alexey_Gusev]

For those who interested - ‘array-helpers’ version

UPD:
checked - ternary operator in 4th line not needed, simple function works identical:

let table = base.getTable('copyrows');
let query = await table.selectRecordsAsync({fields:['Name','Quantity']});
let q=rec=>rec.getCellValue("Quantity");
let arr=rec=>Array(q(rec)).fill(rec.getCellValue('Name'))
let create=rec=>arr(rec).map((name,ix)=>({fields:{'Name':name,'Number':++ix}}))
await table.createRecordsAsync(query.records.flatMap(create))

[Lisa_Bauer]

Hi @JonathanBowen ! I’m using one of the earlier scripts you shared here above (pasted below with my fields) but I keep getting the following error:

SyntaxError: Unexpected token ‘)’
on line 1
at s on line 1
at Generator._invoke on line 1
at Generator.next on line 1
at t on line 1
at a on line 1
on line 1
on line 1

What am I doing wrong? Thank you for your help!

let table = base.getTable("NA-Availability");
let query = await table.selectRecordsAsync();

let recordsArray = [];
for (let record of query.records) {
    let quantity = record.getCellValue("#️⃣ Quantity for this Record 📜");
    if (quantity > 1) {
        for (let i = 0; i < quantity-1) {
            recordsArray.push({
                fields: {
                    "Status": record.getCellValue("Status"),
                    "🗒️ Teambuilding Notes": record.getCellValue("🗒️ Teambuilding Notes"),
                    "Starts On 📅": record.getCellValue("Starts On 📅"),
                    "Duration 📝": record.getCellValue("Duration 📝")
                    "Role Type": record.getCellValue("#2 Role Type for this Record  🔄")
                    "Home Project": record.getCellValue("Home Project")
                    "Teambuilding REQUEST 🔄": record.getCellValue("Teambuilding REQUEST 🔄")
                }
            })
        }   
    }
    await table.updateRecordAsync(record, {
        "Quantity Script ": 'Completed'
    })           
}

console.log(recordsArray);
let newRecords = await table.createRecordsAsync(recordsArray);```