Help

Create multiple records based on a quantity field with a Script

Topic Labels: Scripting extentions
25896 46
cancel
Showing results for 
Search instead for 
Did you mean: 
Sam_Cederwall
7 - App Architect
7 - App Architect

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!

46 Replies 46

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

Screenshot 2020-04-23 at 09.51.56

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

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.

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

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:

Screenshot 2020-04-23 at 20.27.20

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

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

Best,
Sam

Rieve_Bule
4 - Data Explorer
4 - Data Explorer

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!

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
                }
            })
  1. 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");

)

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

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!

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!

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!