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!

[JonathanBowen]

Hi @Sam_Cederwall - two ways to do this based on a table like this:

Use createRecordAsync:

let table = base.getTable("CopyRows");
let query = await table.selectRecordsAsync();

for (let record of query.records) {
    let quantity = record.getCellValue("Quantity");
    if (quantity > 1) {
        for (let i = 0; i < quantity; i++) {
            let newRecord = await table.createRecordAsync({
                "Name": record.getCellValue("Name"),
                "Quantity": 1
            })
        }
        await table.updateRecordAsync(record, {
            "Quantity": 1
        })        
    }
}

This creates the new records one by one.

Use createRecordsAsync:

let table = base.getTable("CopyRows");
let query = await table.selectRecordsAsync();

let recordsArray = [];
for (let record of query.records) {
    let quantity = record.getCellValue("Quantity");
    if (quantity > 1) {
        for (let i = 0; i < quantity; i++) {
            recordsArray.push({
                fields: {
                    "Name": record.getCellValue("Name"),
                    "Quantity": 1
                }
            })
        }   
    }
    await table.updateRecordAsync(record, {
        "Quantity": 1
    })            
}

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

Here I’m building the array of records first, then use the “records” method to create these in one go. They’re very similar of course, so which one you use would be down to personal preference. I like the first method because the creation and the reset of the original quantity happen closer together, although in practice, this probably makes little difference. The first method is undoubtedly slower when used with many records, although the second method has an upper limit of 50 records that can be created at one time (so you’d need to build in something to deal with this if this is an issue for you).

Hope this helps

(side note - I didn’t know if a row has, say, quantity of 2, whether you wanted to end up with the original and 2 new rows, so total 3, or the original and 1 new row, so total 2. My script works to the former method, but if you want the latter just modify for (let i = 0; i < quantity; i++) to be i < quantity-1 )

JB

[Sam_Cederwall]

You nailed it, this is brilliant. Thank you much for the help.

Edit:

I had come somewhat close with my own script but am still learning, the main thing I was missing was the loop: for ( let i =0; i < quantity; i++)

For my own personal growth in scripting, could you possibly enlighten me as to what is happening here if you have the time? Thanks again.

[JonathanBowen]

@Sam_Cederwall - sure. Here’s a proper write up of it:

Loops and iteration

And this is also useful:

https://www.w3schools.com/js/js_loop_for.asp

but you can try it out on a script block. Add this on its own in a new script block:

for (let step = 0; step < 5; step++) {
  // Runs 5 times, with values of step 0 through 4.
  console.log('Step: ', step);
}

If you run this script you’ll get the following output:

In this case “step” is just a counter (as is “i” in the original script above). The code inside the for block runs as many times as the counter code allows. So in the step case above we have the counter starting at 0, must be less than 5 and increments by 1 (step++) each time it runs. So our step variable runs through the values, 0, 1, 2, 3, 4 and then stops. (It is common practice for the loop to start at counter 0, not counter 1)

Going back to the original problem, if a row has quantity of 3, for example, we want to create 3 copies of that row, so we use the quantity value to drive the loop counter. In:

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

i will take the values 0, 1 and 2 and the code inside the block will run once for each counter - 3 times.

Hope this helps!

JB

[Sam_Cederwall]

Thank you so much for taking the time to write this out, this is super helpful.

Best,
Sam

[Rieve_Bule]

Hi, I had a few questions regarding this topic. I’m trying to have Airtable create and remove rows in one table based on a change in the number value of a given cell in another table. Some sources on this forum have implied that it’s not possible, but I think your solution above may be able to be adapted to meet my needs. In other words, how could I edit your macro above to

1. duplicate other cell values in the original row into the new rows?
2. populate the new rows in a different table than the original “Quantity?”
3. remove the last added row(s) if the “Quantity” is decreased while also adding if increased?

I understand if all these obstacles don’t have one magical script that can do it all. Thank you for your help in advance!

[JonathanBowen]

Hi @Rieve_Bule:

1. You can get the other field values for the row you want to copy here:

recordsArray.push({
                fields: {
                    "Name": record.getCellValue("Name"),
                    "Field 1": record.getCellValue("Field 1"),
                    "Field 2": record.getCellValue("Field 2"),
                    "Field 3": record.getCellValue("Field 3"),
                    "Field 4": record.getCellValue("Field 4"),
                    ...
                    "Quantity": 1
                }
            })

2. Adding to a different table - just reference another table in the final line. Instead of this:

let newRecords = await table.createRecordsAsync(recordsArray);

do this:

let newRecords = await YOUR_TABLE.createRecordsAsync(recordsArray);

(where YOUR_TABLE has been set up with

let YOUR_TABLE = base.getTable("Name of 2nd table");

)

3. This is a bit more complicated. What’s the scenario you are trying to implement here?

[Rieve_Bule]

Thanks for your help, I’ll give those a shot.

As for my last question, we’re trying to use Airtable to track video production for large Playlists of videos. Sometimes the number of videos we expect to make changes over the course of production.

Our production planning “Overview” table organizes things by Bundles of videos that will eventually combine to form a full Playlist. And our “Catalog” table breaks everything down by individual video.

Where the Bundle in our production Overview lists the quantity of videos, we want to be able to change that amount and have it automatically change the number of rows in the Catalog table that correspond to each video.

So if Bundle X is expected to have 10 videos, we add the number 10 to a cell beside Bundle X in the Overview table. Then in the Catalog table, it automatically creates 10 rows (1 for each video) and lists each as Bundle X.

But if production changes and we decide we’ll only do 8 videos, we also want to be able to just change the quantity in the Overview table to 8 and have it automatically remove two rows from the Catalog table.

I hope that gives some perspective. I understand this additional part represents pretty much the opposite of what you detail above. Thanks again for your advice!

[Justin_Barrett]

What you want could be accomplished with the help of Airtable’s scripting features. My first thought was to go the automation route, but I can see a possible problem there.

Using your example, let’s say that Bundle X needs to go from 10 videos down to 8. You click in the number field, delete the 10, enter 8, and hit Enter. The problem comes with the automation trigger. Regardless of how the view is set up that actually triggers the automation, the trigger will likely fire the moment you begin deleting digits from the number “10”. This means that the automation could begin operating under the assumption that you’re going from 10 to 1 (removing the zero), so it takes out nine video records. Then the field becomes empty, which will likely trigger the automation again, removing the last record. Finally, it sees the new 8, and runs a third time to add 8 completely new records. Chances are that series of actions isn’t what you want.

My recommendation would be to use a script in the Scripting block. That script could be incorporated a couple of ways:

1. You change the number of videos in a bundle using a number field, then click a button in that record to trigger the script and add/remove linked video records accordingly.
2. You click a button field to kick off a script that asks you for a number. That adds/removes linked video records accordingly, and a Count field uses those links to show you how many videos are part of that bundle. In this variation, there is no manually editable number. The number is determined by the links, and those links are made by the script. That way you eliminate the possibility of someone changing a number but forgetting to run the script and update the links.

If you’d like help with either of those options, just holler!

[Charley]

Could someone help me figure out how to launch this code from a button? I am stuck at figuring out how to implement the input.recordAsync

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

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

let query = await table.selectRecordsAsync();

//I would like to launch this from a button but I don’t know how to use this next line in place of the table.selectRecordAsync()

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

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"),

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

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

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

,
“Qty”: 1

        })

    }

          

}

}

Thank you!

[JonathanBowen]

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

Using buttons (part 1)

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]

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]

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]

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]

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]

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.

[Charley]

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.

[Scott_Noll]

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]

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]

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