Create multiple records based on a quantity field with a Script
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!
Page 1 / 2
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
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
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.
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.
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.
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
Thank you so much for taking the time to write this out, this is super helpful.
Best,
Sam
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
duplicate other cell values in the original row into the new rows?
populate the new rows in a different table than the original “Quantity?”
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, 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
duplicate other cell values in the original row into the new rows?
populate the new rows in a different table than the original “Quantity?”
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!
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
}
})
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");
)
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!
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:
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.
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!
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
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!
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!
Hi @Charley - I wrote a post about this - have a look here:
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"); ...
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!
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 = r];
//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.
Hi @Charley - I wrote a post about this - have a look here:
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"); ...
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
})
}
}
}
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.
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);
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);
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 = a];
//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++){ ...
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 = y];
//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++){ ...
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. :grinning_face_with_big_eyes:
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.
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. :grinning_face_with_big_eyes:
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
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
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
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?
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
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
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
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 = e];
//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 @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
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:
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:
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:
is there something I need to replace to make sure it selects a Record ID?
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
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:
is there something I need to replace to make sure it selects a Record ID?
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!
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.
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.
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?
