Create multiple records based on a quantity field with a Script
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 - 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:
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
