Create multiple records based on a quantity field with a Script

Sam_Cederwall · ‎Apr 22, 2020

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!

JonathanBowen · ‎Sep 21, 2020

Hi @Charley - I wrote a post about this - have a look here:

You don’t do this:

let query = await input.recordAsync(‘Choose a record’, table);

but this:

let record = await input.recordAsync('Choose a record', table);

Once the record is picked by the user, I think you should be able to continue with your script from here:

let quantity = record.getCellValue("Received"); ...

Sam_Cederwall · ‎Sep 21, 2020

Hello Charley,

I recently did the same thing on one of my bases but I’m still new to JavaScript so hopefully this will still be helpful.

//Tables

let table = base.getTable("Parts");


//Record Input

let record = await input.recordAsync("Please select a record", table);


//Record variables

let quantity = record.getCellValue("Received");

let name = record.getCellValue("Name");

let purchased = record.getCellValue("Purchased");

let cost = record.getCellValue("Cost");

let msrp = record.getCellValue("MSRP");


//New record array

let recArray = [];


//Iterate by quantity

if(quantity > 1){

    for(let i=0; i < quantity -1; i++){


        //Push to the array

        recArray.push({

            fields: {

                "Name": name,

                "Purchased": purchased,

                "Cost": cost,

                "MSRP": msrp,

                "Quantity": 1

            }

        });

    }


    //Update the original record to have a quantity of 1

    table.updateRecordAsync(record, {

        "Quantity": 1

    });

}

//Create new records

table.createRecordsAsync(recArray);

I did my best to adapt your fields to the script I was testing this on so hopefully I got everything correct. As mentioned above by @JonathanBowen, you may want to adapt the (i=0;i<quantity-1;i++) by omitting the “-1” part depending on how many records you want to create from the original.

All you should need to do then is create a field for the button and point the button to run the script.

Hopefully this helps.

Charley · ‎Sep 21, 2020

Thank you Jonathan. This helps me some but unfortunately I don’t seem to know enough about it to address the line “for (let record of query.records ) {” I know its wrong but I don’t know what to do about it. I am sorry.

let table = base.getTable(“Parts”);

let field = table.getField(‘Add to Inventory’);

let record = await input.recordAsync(‘Choose a record’, table);

// I know this next line is not correct but I don’t know what it should be

for (let record of query.records ) {

let quantity = record.getCellValue("Received");

if (quantity > 1) {

    for (let i = 0; i < quantity; i++) {

        let newRecord = await table.createRecordAsync({

            "Name": record.getCellValue("Name"),

            "Part No":record.getCellValue("Part No"),

            "Purchased":record.getCellValue("Purchased"),

            "Cost":record.getCellValue("Cost"),

            "MSRP":record.getCellValue("MSRP"),

            "Qty": 1

        })

    }

          

}

}

Charley · ‎Sep 21, 2020

Sam, thank you for this. It works! However, I don’t seem to be able to launch it from the button. The script App opens but it doesn’t run it. I added line 4 based on some guidance I found online but it didn’t change anything. Here is what i have loaded:

//Tables

let table = base.getTable(“Parts”);

let field = table.getField(“Test Button”)

//Record Input

let record = await input.recordAsync(“Please select a record”, table);

let cellValue = record.getCellValue(field);

//Record variables

let quantity = record.getCellValue(“Received”);

let name = record.getCellValue(“Name”);

let partNo = record.getCellValue(“Part No”);

let purchased = record.getCellValue(“Purchased”);

let cost = record.getCellValue(“Cost”);

let msrp = record.getCellValue(“MSRP”);

//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: {

            "Name": name,

            

            "Part No": partNo,

            

            "Purchased": purchased,

            "Cost": cost,

            "MSRP": msrp,

            "Qty": 1

        }

    });

}

//Update the original record to have a quantity of 1

//table.updateRecordAsync(record, {

//    "Qty": 1

//});

}

//Create new records

table.createRecordsAsync(recArray);

JonathanBowen · ‎Sep 21, 2020

Hi @Charley - you’re almost there. I’ve simplified the number of fields here to make it easy for me to replicate (but you can just add yours back in). This works:

//Tables

let table = base.getTable('Parts');

//Record Input

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

//Record variables

let quantity = record.getCellValue('Qty');
let name = record.getCellValue('Name');

//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: {
                "Name": name,
                "Qty": 1
            }
        });
    }

    //Create new records
    await table.updateRecordAsync(record, {
    "Qty": 1
    });
    await table.createRecordsAsync(recArray);    
}

The lines referencing “test button” and “cellValue” aren’t required as far as I can see. I’ve added await to the update and create actions (so that each one completes before moving to the next - not absolutely necessary in this case, but will be in other scenarios).

One other point - as it stands, if the quantity is 3, then it will create 3 new lines in addition to the original, leaving a total of 4, which may not be what you want. If not, you can set the iterator in the for loop to max out at quantity - 1, i.e.:

for(let i = 0; i < quantity - 1; i++){ ...

Charley · ‎Sep 22, 2020

Excellent! Thank you so much. I have learned a lot from this and have already successfully implemented for other use cases now that I have the framework and a better understanding of what is happening and what is needed. :grinning_face_with_big_eyes:

Charley · ‎Sep 22, 2020

Got it! Thank you so much for this simplified, easy to follow framework. I have already successfully implemented this in some other use cases and I am looking forward to doing more with scripts. :grinning_face_with_big_eyes:

Scott_Noll · ‎Jan 07, 2021

Hi. Thank you for the solution. Did try the scripts and it works wonder.
Then I have this another Quantity field is on formula-format field, and I encounter this error:

Can't set cell values: Field 'Quantity' is computed and cannot be set

What could be the solution?
Thank you

JonathanBowen · ‎Jan 10, 2021

Hi @Scott_Noll - I think the answer depends upon your base setup. As the error message says, you can’t set or update a formula field - Airtable is automatically calculating this in the background.

So you could create the tasks without attempting to set the quantity field. Or you could create the new records in another table. But the “best” solution will be determined by your set up and what you are trying to achieve? Can you post more detail for a better answer?

Kevin_S · ‎Jan 26, 2021

Hello there!

I have tried and tried using the above but I’m so new to scripting in particular and wondering if you’d be so kind as to help me a little further.

I am simply trying to create a way to generate multiple entries on the ‘Codes’ tab based on the Quantity Purchased in the ‘Purchases’ tab.

Currently I have an automation which allows one single entry to be created in Codes based on the New Record trigger on Purchases.

I have tried to go through scripting and cannot get my head around it and would so greatly appreciate if someone would be kind enough to assist me by any chance.

Many Thanks indeed,
K