Script Automation for Creating New Records & Numbering Them
Hello fellow Airtable peeps!
Let me start by detailing what I need out of this script I’m trying to make, then I’ll give ya’ll the two scripts I have been using that I’m trying to replace.
What I Need
In Table 1, I have a field where I input the amount (i.e. “6” or “10”). In Table 2, I want this number to be translated into that number of records and I need them to be numbered. Here’s an example:
Table 1:
Table 2:
What I Have
I’m currently using two scripts to accomplish this goal on the second table, but not only does it require two scripts, but if I turn them both on at the same time it will expand the records forever (first script will expand any number that isn’t 1 and change their number to 1, and the second script will just keep changing the numbers, so it just keeps creating records). So, basically, I’ve been turning each one on individually to avoid the eternal expansion. I searched the forums far and wide just to find these two scripts, but they’re just not quite what I need.
I tried adding two checkboxes (Expanded and Numbered) that the scripts would check for before they did anything and change when they completed, all in an attempt to stop them from expanding, but they don’t. I’ve never tried to program anything before, so just to get it working at all was enough. Below are the two scripts I’ve been using.
Number input → # of records (rows) script:
let table = base.getTable("Tasks");
let query = await table.selectRecordsAsync();
let recordsArray = [];
for (let record of query.records) {
let quantity = record.getCellValue("Pup #");
if (quantity > 1 && (record.getCellValue("*Expanded*") == null)) {
for (let i = 0; i < quantity-1; i++) {
recordsArray.push({
fields: {
"*Litter*": record.getCellValue("*Litter*"),
"*Strain*": record.getCellValue("*Strain*"),
"*Wean Date*": record.getCellValue("*Wean Date*"),
"*Birth Date*": record.getCellValue("*Birth Date*"),
"*Expanded*": record.getCellValue("*Expanded*"),
"Pup #": 1
}
})
}
}
await table.updateRecordAsync(record, {
"Pup #": 1
})
}
console.log(recordsArray);
let newRecords = await table.createRecordsAsync(recordsArray);
Numbering script:
const table = base.getTable("Tasks")
const view = table.getView("Grid view")
const query = await view.selectRecordsAsync()
const records = query.records
const groupPath = ["*Litter*"]
const rankFieldName = "Pup #"
const batches = []
let updates = []
records.forEach(record => {
let group
groupPath.forEach(x => group += record.getCellValueAsString(x))
const batchIndex = batches.findIndex(x => x.batch === group)
if(batchIndex >= 0) {
batches[batchIndex].recordIds.push(record.id)
} else {
batches.push({batch: group, recordIds: [record.id]})
}
const rank =
batchIndex >=0
? batches[batchIndex].recordIds.length
: 1
updates.push({id: record.id, fields: {[rankFieldName]: rank}})
})
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50));
updates = updates.slice(50);
}
