Process new records sequentially

[Christopher_Sto]

Is it possible to queue records for processing with an automation, rather than processing them simultaneously?

I have an automation that’s triggered by a record being created in Table A.

The automation checks for records in Table B and if it finds a match it links it to the new record in Table A. If no match is found then a new record is created in Table B and linked to the new record in Table A.

The problem I’m facing is that if two or more records are created in Table A in quick succession, the automation for the first record to be created hasn’t completed when the automation for the second record starts. This means that duplicate records can be created in Table B, when only one is needed.

Is the only option to stop using the ‘When record is created’ trigger and instead build something that processes all new records on a schedule? It would have to run every 15 minutes as it needs to be close to real-time.

[ScottWorld]

Unfortunately, you wont be able to accomplish your idea of processing a batch of records on a schedule unless you know JavaScript, because Airtable’s Automations won’t natively let you perform actions on individual records within a batch of records that are found. You can only process records individually via scripting.

Unless someone else knows of a better idea, your best bet that doesn’t require any coding at all is to use Make.com for your automations. There is a checkbox in Make that you can select to process your records sequentially instead of simultaneously.

[Christopher_Sto]

Thanks Scott. I can write JavaScript, so I’ll try that route initially. Our organisation uses Zapier, and I don’t think I’ll be able to convince them to allow me to use make.com

[kuovonne]

Christopher_Stock:

Is the only option to stop using the ‘When record is created’ trigger and instead build something that processes all new records on a schedule? It would have to run every 15 minutes as it needs to be close to real-time.

This could work with a scripting action. It would take a 2688-2976 automation runs every month, though. You would also probably need to add a created time field to the table to help the script know the order the records were created.

Another option is to periodically dedupe.

[TheTimeSavingCo]

Hmm, I think I may be missing something, but could we just use Airtable’s “Link to another record” functionality to handle the creation of the new record?

So we make a field in Table A to link to Table B, let’s call that field “Link to Table B”, and every time a new record gets created in Table A, we put some sort of unique identifier into “Link to Table B”. That way you only end up with one record in Table B. The downside is that you’ll have multiple links to records in Table A, but I don’t know if that’s an issue for you?

E.g. Two records get created in Table A in quick succession, both of which have the same unique value, let’s say ‘Record 1’. Normally your automation is searching Table B for a record with the value ‘Record 1’, and if it doesn’t find it, it’ll create it.

Now, we just dump ‘Record 1’ into ‘Link to Table B’ twice. The first time this happens, a record will get created in Table B, and the second time this happens it’ll just add a link to the record in Table B; no more duplicates

[kuovonne]

Adam_TheTimeSavingCo:

I think I may be missing something

Your method only works if you have an editable primary field in Table B that matches the value from Table A. However, that might not be the right primary field for Table B.

[TheTimeSavingCo]

Ahh, yeah you’re right

[Dan_Montoya]

I came across this question while researching other queueing issues and took it as a challenge to solve for future people.  The solution is 100% no code.  It uses 3 tables and 4 automations

Table 1 - - Incoming items you want to process:

• Fields
  • unique id is a formula of an autonumber field and the record
  • Autonumber
  • Linked Record to Items
  • Linked Record to Queues
  • Status field default to "Backlog" -- optional automation to set status to "Backlog" if Status is blank

Table 2 -- List of existing items 

• Fields in addition to linked
  • Count of linked items (optional - I use this to prove that an incoming item has been matched.  If the number is > 1 it has been matched to an existing record).

Table 3 -- Queue

• Fields
  • "next in queue" Rollup where status is "Backlog" and aggregate is MIN(Values)

Automations

1. (optional) if incoming items status is empty set to backlog
2. If an incoming item doesn't have a linked record to the queue, add it
3. Process Next
   1. Find the incoming item that has the next in queue number from the queues table
   2. Set the status to Processing
4. Processor
   1. When a record status is changed to processing
      1. search for the matching item in the items table
         1. if found,
            1. update it by linking the record,
            2. set status to processed
         2. if not found
            1. create it 
            2. set the status of incoming record to "processed".

You now have a working FiFo queue that only processes one record at time.  If the incoming record already exists it links it to the existing item.  If it doesn't exist, it creates it and links it.

[stevenhq]

I ended up using a script within an automation to accomplish. I'll share my general workflow here in case anyone else is trying to solve this:

I have 3 information tables, each row has a linkId. (this should work with 1 table or many tables).
I have 1 linking table, with a primary field linkId, and linking fields to the 3 info tables.
There should be only 1 linking table record for every unique linkId that shows up in the 3 info tables.
this 1 linking table record should link to every instance of that linkId in all 3 tables.

Lets say there is a linkID that is present in every row in the 3 information tables. so a given linkID, say #12-P01 could exist in as a single instance in one of the three tables, or as multiple instances in all three info tables, or anywhere in between. But in the linking table, there can only be 1 #12-P01 and it should link to every other #12-P01.

Step 1: Always create a new linking table row for every new row in an information table.
- no matter what, any new row in an info table (containing a linkID) will trigger an automation to create a row in the invoice table, with the primary field (or any field can work) containing the linkID.

Step 2: create an autonumber field for linking table
-  when the linking table row is added, it will get a number from an autonumber column. This will represent the order in which rows are created and guarantee that we can always identify the same linking record as being the earliest created or "original". we'll call this createdNumber
- (I tried using created time, but it is possible in airtable to have two records that show the exact same created time, and then this doesn't work.)

Step 3: A second automation runs when the linking table row is created (well call the new row triggerRecord)
This automation does the following:

1. find existing link records that match the linkId of the triggerRecord.
2. run a script:
   1. get lists of found record ids and createdNumber from step 1 as input to the script and compile an array of objects containing the recordId and the createdNumber if each
   2. sort this array numerically and then extract the recordId from the first object in the array, which will be the recordId of the earliest created record (well call originalRecord)
      note: this will always be the same record in the future, since any subsequent runs of the automation will be on a row with a larger createdNumber. and even if two or more duplicate records run an automation at the same time, one of them will be designated original, and not the others.
   3. compares the originalRecord Id to the triggerRecord Id, and if they are different, deletes the triggerRecord. If they are the same, the triggerRecord is the originalRecord.
   4. searches all 3 information tables for matching linkIds, and then updates the originalRecord with links to all matches.
      note: this way, the new info record that triggered the whole sequence will get added to the originalRecord's links, even if the record that was created from the first automation gets deleted.

This way of doing it ensures that 1. there will never be duplicate records after the automation runs, 2. the automation series will run if and only if new records are added to information tables, and 3. that you can rely on a consistent linking record, since the original will never be deleted. This way, if you have other fields, links, etc. they wont be destroyed.

So far, this has proven to work well and I haven't been able to break it. It's not the fastest automation I've created, so I'm not sure if I'm doing much heavy work. But since it only runs once per item added, it should be fine for my use case, and probably a lot of other use cases.
I think that was clear, happy to answer questions. And, if someone thinks there is a better way to do this in terms of performance or just cleaner logic, I'd love to hear it! I'm still somewhat new to coding so I'm trying to piece things together.