Hello,
I have a script that I’d like to run as an automation when a record is updated but have been running into a problem when trying to update the record (specifically one field - “Conflict”) since it exceeds the 15 mutations per second limit…
The records are screenings of films in various venues, across 10 days. The script is checking whether there is enough turnover time between the screenings at a single venue. If there is less than 55 mins, both records will get their Conflict field checked to notify the humans working on the scheduling. SlotIDs are ascending numbers unique to each scheduling slot and this is how I’ve been able to determine the time difference.
The script does what we need it to do when run manually, inside apps. But turning it into an automation gives the error. I’ve narrowed down that it’s the for loop used for updating the records that’s causing it but don’t know how else to achieve the same result.
I’ve also tried using a while loop for updating the records but I only need to update one field (while I use start/end time fields to determine the logic). Is there a way to target just one field in a while loop updateRecordsAsync? or any better way to do this and avoid a for loop all together?
Many Thanks!!
Here’s the script:
// query for all the records in a view
const table = base.getTable("Films");
let queryResult = await table.selectRecordsAsync();
let records = queryResult.records;
let recordsData = [];
for (let record of records){
let slotID = parseInt(record.getCellValueAsString("Slot ID"));
let ignoreConflict = record.getCellValue("Ignore Conflict");
let endTime = record.getCellValue("End Time");
// slot ID exists = record is scheduled
if( slotID ){
if( ignoreConflict !== null ){
let newRecord = {
id: record.id,
slotID: slotID,
fields: {
"Conflict": false
}
}
recordsData.push(newRecord);
}
else if( endTime !== null){
let startTime = record.getCellValue("Start Time")[0];
let newRecord = {
id: record.id,
slotID: slotID,
fields: {
"Start Time": new Date(startTime),
"End Time": new Date (endTime),
"Conflict": false,
"Venue": record.getCellValueAsString("Venue")
}
}
recordsData.push(newRecord);
}
} else{
let newRecord = {
id: record.id,
fields: {
"Conflict": false
}
}
recordsData.push(newRecord);
}
}
recordsData.sort((a,b) => a.slotID - b.slotID);
for ( let i = 1; i < recordsData.length; i++ ){
let prevScreening = recordsData[i-1];
let prevScreeningEndTime = prevScreening.fields["End Time"];
let nextScreeningStartTime = recordsData[i].fields["Start Time"];
if( prevScreening.fields["Venue"] === recordsData[i].fields["Venue"] ){
// timeDiff in milliseconds -> minutes
let timeDiff = (nextScreeningStartTime - prevScreeningEndTime)/60000;
//if less than 55 mins between screenings
if (timeDiff < 55){
prevScreening.fields["Conflict"] = true;
recordsData[i].fields["Conflict"] = true;
}
}
}
for(let i=0; i < recordsData.length; i++){
await table.updateRecordsAsync([
{
id: recordsData[i].id,
fields: {
"Conflict": recordsData[i].fields["Conflict"]
}
}
]);
}
