Run automation once when records are added to a view? (Race Condition Issues)

[r0anne0h]

I'm running into race conditions some report summaries automations, and am wondering if anyone has come up with a better solution than I have in place

I have a table that is automatically updated when a user adds values from a csv report. I need to summarize that report with an automation. I have a multi-step automation that does the following

• Triggers when a record enters a view
• Checks to see if the date of the report is already listed in my summary table with a "Find Records" step
• If "Find Records" length is 0, it runs a few scripts to do math on the raw data entered

However, I've found that the automation runs for all records in the view simultaneously, rather than one at a time, so I'm running into race conditions where multiple lines are created for the same report. I know there are other solutions such as running the automation from a button click or at a scheduled time, but those are not options for me at this point. Any advice?

[ScottWorld]

When you're running an automation based on a view, Airtable's automations will always run actions on all records simultaneously, unless you tell it to loop through your records with a repeating group action step.

But you can only loop through records using a repeating group action step if you feed Airtable an input list of multiple records.

These are the most common ways to feed Airtable an input list of multiple records:

#1. You can perform a "find records" action step and then you can loop through those found records afterwards. However, the gigantic problem here which makes it unusable for many Airtable customers is that the "find records" action will never find more than 100 records, so this option will never work if you're dealing with more than 100 records.

#2. You can loop through the linked records in a linked record field, because the repeating group action will support up to 8,000 linked records. That would entail making sure that each one of your imported records is automatically linked to the EXACT SAME RECORD in another table. Then, from that other table, you can loop through up to 8,000 linked records within its linked record field. I discuss automatic linking records to a single record in another table in this Airtable podcast episode. I discuss it in the context of creating global variables in Airtable. but you can easily extrapolate the same lesson for running a repeating loop in an automation.

#3. Perhaps the quickest & easiest way to handle this would be to simply use Make's advanced automations for Airtable. By default, Make always performs actions on records in sequential order. Make never performs actions on records simultaneously, unless you specifically tell it that you want simultaneous processing of records. I demonstrate how to import a CSV file into Airtable on this Airtable podcast episode.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[dilipborad · Accepted solution]

Hello @r0anne0h ,

Try any of these methods.

1. Use a Locking Mechanism

Implement a locking mechanism within your automation logic to prevent multiple automation from processing the same report simultaneously. This approach involves adding a "Processing" or "Lock" field in your table that gets updated before the automation runs its logic.

• Step 1: When an automation is triggered, the first action is to check if the "Processing" field is already set for the given report date. If not, the automation sets this field to "true" (indicating that processing is underway) and proceeds with the calculations and updates.
• Step 2: After setting the "Processing" field to "true", the automation performs a "Find Records" step to ensure no other records have started processing since the last check. If another record has started processing (i.e., another automation instance sets the "Processing" field to "true"), the current automation instance stops or skips the report.
• Step 3: Once the calculations and updates are done, reset the "Processing" field to "false".

This method requires careful design to ensure the locking mechanism itself does not create bottlenecks or race conditions.

2. Sequential Processing via Scripting

Utilize Airtable's scripting capabilities (if available in your plan) to enforce sequential processing of records. A script can be designed to fetch all records from the view, sort them by a specific order (e.g., report date), and then process them one by one. This method centralizes the logic, reducing the chance of race conditions but requires custom scripting and may not scale well for very large datasets.

3. Use an External Automation Tool

Consider using external automation platforms like Zapier or Integromat (Make) for more granular control over the execution flow. These platforms can offer more sophisticated mechanisms for handling race conditions, such as queues or batching operations. You can set up a webhook to trigger from Airtable and then handle the logic in Zapier or Integromat, where you can more easily manage execution orders or deduplicate processing.

• Zapier/Integromat Workflow:
  • Trigger: When a record enters a view, send a webhook to Zapier/Integromat.
  • Action: Use a Zapier/Integromat step to check for existing summaries for the report date. If none exist, proceed with the calculations and updates, ensuring that this step includes logic to handle or avoid race conditions, possibly by using internal queuing or by storing a temporary state in a data store that can be checked before proceeding.

4. Scheduled Batch Processing

Though you've mentioned scheduled time or button clicks are not options, it's worth considering a variation of this approach as a last resort. Instead of manual triggers, you could have scheduled automation run at very short intervals (as frequently as your plan allows) to process any unprocessed records. This batch process would need to incorporate checks to avoid duplication, similar to the locking mechanism described.

Each of these solutions has its trade-offs in terms of complexity, performance, and scalability. You'll need to choose the one that best fits your specific use case, considering the volume of data and the criticality of avoiding duplicates in your summary reports.
Note:- I've used AI to get information about Sequential Processing

[TheTimeSavingCo]

That's...odd.  So the problem is that your users are submitting the values from a CSV report with the same date multiple times, and the second copy gets submitted before the first automation has finished running?

That is to say,
1. User submits data with date 1 Jan 2024
2. Automation runs (Run #1), looks for the date, and can't find it, starts crunching numbers
3. User submits data with date 1 Jan 2024
4. Automation runs (Run #2), looks for the date, and can't find it, starts crunching numbers
5. Run #1 completes, creates a record in the Summary table with the date 1 Jan 2024
6. Run #2 completes, creates a record in the Summary table with the date 1 Jan 2024

Is that right?

[r0anne0h]

Thanks for your reply! Not quite - but close.

1. User submits .csv file
2. Automation triggers based on new record creation
3. To avoid a summary record being created for each new record added, I check to see if a record for that date exists already. If it does, the automation does not move on to further actions. 

My issue is that before Record 1 is finished processing (and therefore no date exists on the summary table), Record 2 is already running my full script because the date does not yet exist on the summary table, even though it shortly will.

[r0anne0h]

Thank you!! I didn't even think about having a processing field on the record itself - I think that just might work. I'll give it a go and update you.

[r0anne0h]

Thanks again! I just tried this and it works - for anyone else looking for a solution for this, I scripted out the "Processing" check and it works like a charm. Here's the code: 

let table = base.getTable("table")
let view = table.getView("reports")
let query = await view.selectRecordsAsync()
//I'm using the record ID of the record that triggered the automation
let record = input.config().recID
//counter for determening if we will continue automation
let x = 0

//update current record to check a "In Process" box
await table.updateRecordAsync(record,{"Summary In Process":true})

//loop through all records to find if if current record should trigger the rest of the automation

for (let rec of query.records){ 

//if the current record is not the only record in process, increase counter x & break loop
    if (rec.getCellValue("Summary In Process")==true && rec.id != record) {
        x++
        break;
    }
}
//if tghe current record is the first record to have the "In Process box checkeck, we use //that to trigger the batch summary
 if (x == 0){
output.set("traffic light","green")}