Create multiple records based on a quantity field with a Script

Forum|Forum|5 years ago
April 22, 2020
46 replies
434 views

+17

Sam_Cederwall
Inspiring

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!

Show first post

This topic has been closed for replies.

Forum|pagination.label 2 / 2

Peter_Rosasco
Participating Frequently
Forum|Forum|4 years ago
December 10, 2021

Thank you for the welcome!

I have tried the one posted by JonathanBowen on Jan 30th, not sure how to copy that script into this post correctly.

When try to run this script I get the following error:

I also tried the first 2 that are at the top of this thread. When trying the first two I got one to work with no errors but all it did was change all my entries in the “boxes” column to 1 instead of putting the data in the new table.

+18

JonathanBowen
Inspiring
Forum|Forum|4 years ago
December 10, 2021

Thank you for the welcome!

I have tried the one posted by JonathanBowen on Jan 30th, not sure how to copy that script into this post correctly.

When try to run this script I get the following error:

Hi @Peter_Rosasco - this error:

Usually points me to an extra (not needed) bracket somewhere in the script. If you can post your full script (copy and paste), it will be easier to see what the issue is. To add code to a post, enter 3 backticks, then, on a new line, paste your full block of code, then on another new line, etner 3 additional backticks. This will format the full script as a code block.

Peter_Rosasco
Participating Frequently
Forum|Forum|4 years ago
December 10, 2021

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

Thank you for telling me how to do that! There is the code it is the same one from above with the base names changed

+18

JonathanBowen
Inspiring
Forum|Forum|4 years ago
December 10, 2021

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

Thank you for telling me how to do that! There is the code it is the same one from above with the base names changed

This isn’t a full script (which may be the problem!!). The script ends with the above snippet, but there will need to be more to it than this to make it work. It might be you copied in only part of it though.

Peter_Rosasco
Participating Frequently
Forum|Forum|4 years ago
December 10, 2021

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”

+18

JonathanBowen
Inspiring
Forum|Forum|4 years ago
December 10, 2021

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”

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
Participating Frequently
Forum|Forum|4 years ago
December 10, 2021

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

+19

Hannah_Wiginton
Inspiring
Forum|Forum|4 years ago
December 10, 2021

Hello I am trying to make a script that will create multiple rows in a new table based on the number in a given column from the first table. I have tried the scripts above and I can not get them to work.

Example: I want to send 7 boxes of magazines to “Joe”. In the first table I will have “Joe” and his contact info and a column for the total number of boxes to be sent which would be 7. From there I want to create 7 individual records in a different table for Joe.

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

---Hannah - On2Air.com - Automated Backups for Airtable

Peter_Rosasco
Participating Frequently
Forum|Forum|4 years ago
December 10, 2021

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

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
New Participant
Forum|Forum|3 years ago
April 12, 2022

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 )

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
Participating Frequently
Forum|Forum|3 years ago
July 28, 2022

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

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

+21

Justin_Barrett
Inspiring
Forum|Forum|3 years ago
July 30, 2022

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

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"]

+25

Alexey_Gusev
Brainy
Forum|Forum|3 years ago
August 1, 2022

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
Inspiring
Forum|Forum|3 years ago
October 10, 2022

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);```

+25

Alexey_Gusev
Brainy
Forum|Forum|3 years ago
October 10, 2022

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);```

Hi,

you missed for loop counter increment.

+18

JonathanBowen
Inspiring
Forum|Forum|3 years ago
October 12, 2022

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);```

@Lisa_Bauer - a couple of things to note:

As per @Alexey_Gusev, you are missing the incrementer on the loop, so this:

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

should be something like this:

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

I can see that on the last line of the script you also have the 3 backticks, which may not be in your actual script, but are shown here, so worth checking that they are not there.

One other thing to note - your create records action - last line - works on the recordsArray, which is all good, but the action is limited to creaitng 50 records, so if you array could contain more than 50 records, you need to do something like:

while (recordsArray.length > 0) {
    await table.createRecordsAsync(recordsArray.slice(0, 50));
    recordsArray = recordsArray.slice(50);
}

Lisa_Bauer
Inspiring
Forum|Forum|3 years ago
October 12, 2022

Thank you both for your helpful reply and assistance!

Houston_Llew
New Participant
Forum|Forum|3 years ago
November 9, 2022

Hi @Kevin_S - the code is pretty similar. I can’t see how your code table is structured, so making this up. But if you have tables like this:

The running the script below against a record will create a new code record for the value of quantity in the record chosen:

The script is:

//Tables

let orders = base.getTable('Orders');
let codes = base.getTable('Codes');

//Record Input

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

//Record variables

let quantity = record.getCellValue('Quantity');
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);    
}

Not that the script doesn’t deal with quantities of more than 50, but I wrote a post noting how to deal with this

This is excellent and very close to what I’m trying to do.
Is there a way to produce all the records at once?

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

and just to get greedy: by button?

+18

JonathanBowen
Inspiring
Forum|Forum|3 years ago
November 12, 2022

This is excellent and very close to what I’m trying to do.
Is there a way to produce all the records at once?

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

and just to get greedy: by button?

Hi @Houston_Llew - there’s a variation on this theme higher up in the thread that I think does what you are asking for:

Is this what you mean?

Jack_Tranckle
Participating Frequently
Forum|Forum|3 years ago
January 6, 2023

Hello all, I am looking at doing almost the same as above, however instead of "quantity", I would like it to be a linked cell with an array of data (in my case names). What would need to be changed to achieve this?

kkowalski
New Participant
Forum|Forum|3 years ago
January 10, 2023

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);

Hope this helps

Could you help me modify this script?

Forum|pagination.label 2 / 2

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded