Apr 22, 2020 10:10 AM
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!
Apr 23, 2020 02:04 AM
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
Apr 23, 2020 09:25 AM
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.
Apr 23, 2020 12:44 PM
@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
Apr 23, 2020 03:25 PM
Thank you so much for taking the time to write this out, this is super helpful.
Best,
Sam
Aug 05, 2020 10:40 AM
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
I understand if all these obstacles don’t have one magical script that can do it all. Thank you for your help in advance!
Aug 06, 2020 06:09 AM
Hi @Rieve_Bule:
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
}
})
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");
)
Aug 07, 2020 08:48 AM
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!
Aug 14, 2020 03:59 PM
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:
If you’d like help with either of those options, just holler!
Sep 21, 2020 12:39 PM
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!
Sep 21, 2020 01:24 PM
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"); ...
Sep 21, 2020 01:26 PM
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.
Sep 21, 2020 07:53 PM
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
})
}
}
}
Sep 21, 2020 07:58 PM
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);
Sep 21, 2020 11:19 PM
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 = [];
//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++){ ...
Sep 22, 2020 10:47 AM
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:
Sep 22, 2020 10:52 AM
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:
Jan 07, 2021 03:40 PM
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
Jan 10, 2021 01:09 PM
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?
Jan 26, 2021 01:46 PM
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