Feb 09, 2022 08:28 PM
As the title says. How would this be written differently to avoid this error? My test environment only had 10 records, tonight was its first scheduled automation run and it grabbed 95 records for this process.
// After Club Automations
let table = base.getTable(“Children”)
let view = table.getView(“Present”);
let query = await view.selectRecordsAsync ({
fields:[“Pts this week”, “Spent Today”, “Total Spent”, “Points”, “Attendance”, “Last Club”],
sorts: [
{field: “Pts this week”},
]
});
let totes = 0;
let today = new Date()
for (let record of query.records){
// Multiply 'Pts this week" by 10, add to ‘Points’, Subtract 'Spent Today, write new total to “Points” field
totes = 10 * Number(record.getCellValueAsString("Pts this week"));
totes = totes + record.getCellValue("Points") - record.getCellValue("Spent Today");
await table.updateRecordAsync(record, {"Points": totes});
// Add ‘Spent today’ to ‘Total Spent’ and clear ‘Spent today value’
totes = record.getCellValue("Spent Today") + record.getCellValue("Total Spent");
await table.updateRecordAsync(record, {"Total Spent": totes});
await table.updateRecordAsync(record, {"Spent Today": null});
// Add one to Attendance Column
totes = record.getCellValue("Attendance") +1
await table.updateRecordAsync(record, {"Attendance": totes});
// Update Last Club with Today’s Date
await table.updateRecordAsync(record, {"Last Club": today});
// Clear Pts this week field
await table.updateRecordAsync(record, {"Pts this week": null});
}
Thanks in Advance!
Solved! Go to Solution.
Feb 10, 2022 12:59 AM
Your problem is that your script is updating 95 records 6 times each, one at a time. That’s 570 updates, which could be just 2:
updateRecordsAsync
.Essentially, you’d replace the entirety of your for
loop with something like:
let updates = query.records.map(record => {
return {
id: record.id,
fields: {
"Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
"Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
"Spent Today": null,
"Attendance": record.getCellValue("Attendance") +1,
"Last Club": today,
"Pts this week": null
}
}
})
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
Feb 10, 2022 12:59 AM
Your problem is that your script is updating 95 records 6 times each, one at a time. That’s 570 updates, which could be just 2:
updateRecordsAsync
.Essentially, you’d replace the entirety of your for
loop with something like:
let updates = query.records.map(record => {
return {
id: record.id,
fields: {
"Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
"Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
"Spent Today": null,
"Attendance": record.getCellValue("Attendance") +1,
"Last Club": today,
"Pts this week": null
}
}
})
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
Mar 08, 2022 11:37 AM
Coming back to this after a while.
Reading up on the documentation a bit and looking at your response has been helpful.
Something I am not able to resolve however is that I get the following error when I try to run it:
TypeError: Invalid arguments passed to table.updateRecordsAsync(records): • records[0].id should be a string, not an object.
I am working on addressing it, I think I understand the error correctly but not sure of the fix just yet. I’ll post and maybe will figure it out before I get a response.
Here is the whole code now:
let table = base.getTable("Children")
let view = table.getView("Present");
let query = await view.selectRecordsAsync ({
fields:["Pts this week", "Spent Today", "Total Spent", "Points", "Attendance", "Last Club"],
sorts: [
{field: "Pts this week"},
]
});
let totes = 0;
let today = new Date()
let updates = query.records.map(record => {
return {
id: record,
fields: {
"Points": (10 * Number(record.getCellValueAsString("Pts this week"))) + record.getCellValue("Points") - record.getCellValue("Spent Today"),
"Total Spent": record.getCellValue("Spent Today") + record.getCellValue("Total Spent"),
"Spent Today": null,
"Attendance": record.getCellValue("Attendance") +1,
"Last Club": today,
"Pts this week": null
}
}
})
while (updates.length > 0) {
await table.updateRecordsAsync(updates.slice(0, 50))
updates = updates.slice(50)
}
Mar 08, 2022 12:05 PM
change
id: record
to
id: record.id
Mar 08, 2022 12:43 PM
Wowzers. Yes that will do it. Thank you very much for the help and opportunity to learn!