Help

Re: Create multiple records based on a quantity field with a Script

5061 2
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 @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:

Screenshot 2021-01-30 at 08.16.39

Screenshot 2021-01-30 at 08.16.46

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

Screenshot 2021-01-30 at 08.19.25

Screenshot 2021-01-30 at 08.19.31

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

Hi Jonathan,

Thank you SO much for your assistance, you have no idea how much I have struggled!

I tried what you sent, and I truly am a novice at this, so please bear with me.

I replaced 'Orders' to ‘Purchases’ and left ‘Codes’ as it is and then replaced ‘Quantity’ to ‘Quantity Purchased’.

When I run the script, it then asks me to choose a ‘Record ID’ instead of automatically doing it – based on this, I have 2 questions:

IMG_20210130_124521.png

Screen Shot 2021-01-30 at 12.38.32.png

Not sure where the rest of my message went! I was saying:

When I run the script, it then asks me to choose a ‘Record ID’ instead of automatically doing it – based on this, I have 2 questions:

  1. is there something I need to replace to make sure it selects a Record ID?
  2. How can I make sure it does this every time a Record is created with a Quantity inputted?

Thank you so so much for your assistance thus far!

Warm Regards,

Kevin S

Hi @Kevin_S - have a look at this post:

This shows the two way to manually trigger a script. The button trigger specifically uses the record that the button is on, so with this method you don’y need to pick a record once the script is running - this all happens as part of the script startup process. I’ve written a post about script buttons here that might be worth reviewing.

For your second point, you could look at Automations. In your case I would suggest something like “when a record matches conditions” (so, quantity > 0), then “run a script”.

Using a script in an automation, specifically when you are targeting a single record with the automation script, needs some setup - have a look at this post for more detail.

Peter_Rosasco
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

Welcome to the community, @Peter_Rosasco! :grinning_face_with_big_eyes: Could you please share which script you’re trying to use, and the specific issue(s) that you’re encountering when trying to use it?

Peter_Rosasco
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
airtable

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.

Hi @Peter_Rosasco - this error:

Screenshot 2021-12-10 at 19.56.59

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
5 - Automation Enthusiast
5 - Automation Enthusiast
//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.