Dealing with race condition

[japh]

Hi,

I am trying to automatically create or update records based on certain criteria from another table. It's to create invoice with line items. The process is the following:

1. if a record's date field is today, create a record in table A.

2. then find a record with the same client in the table invoice, 

3. if not found, create a new client record in invoice table with a line item that is linked to the newly created record from table A.

4. If find the client, then append the line item field with the new record from table A.

The problem is that sometimes there are multiple records matched in the first step of the automation, then both try to update the line item field in the invoice table and the later automation run overwrites the previous run.

I imagine there could be problem if no client found then two clients records would be created in the invoice table. I haven't encountered it in my testing yet.

What's the recommended solution here?

Thanks!

[CalvinAndHobbes]

The problem is that sometimes there are multiple records matched in the first step of the automation, then both try to update the line item field in the invoice table and the later automation run overwrites the previous run.

If I'm understanding your scenario correctly, the issue here might be that you're trying to update the same line item field in the invoice table with a set value, which is determined at the same time from both updates. If you try linking from the new record in Table A side instead of the Invoice table side, it should be able to better handle the appends happening at the same time because the multiple new records won't have previous information that could go stale in their linked record field, and Airtable will handle the appending in the other side of the link.

---

I imagine there could be problem if no client found then two clients records would be created in the invoice table. I haven't encountered it in my testing yet.

---

Would that not be a valid scenario to have new clients created in a single day?

[japh]

---

@CalvinAndHobbes wrote:

If I'm understanding your scenario correctly, the issue here might be that you're trying to update the same line item field in the invoice table with a set value, which is determined at the same time from both updates. If you try linking from the new record in Table A side instead of the Invoice table side, it should be able to better handle the appends happening at the same time because the multiple new records won't have previous information that could go stale in their linked record field, and Airtable will handle the appending in the other side of the link.

---

I imagine there could be problem if no client found then two clients records would be created in the invoice table. I haven't encountered it in my testing yet.

---

Would that not be a valid scenario to have new clients created in a single day?

---

That might work.  I could do Find Records, then do insertion in Repeating group.

The only problem is that there are more than 100 records that I need to populate for monthly invoice. Do you know a good way around it?

I didn't think of linking from Table A to the Invoice table. Like it! (not sure how to do multiple inline reply;)

Many thanks,

[japh]

A quick update.

I played around by adding a random delay script at the beginning of the automation which is easy to implement but still not solving the race condition problem.

In the end, I created a script to add the invoice records first then did what @CalvinAndHobbes suggested by linking from table A to the Invoice table in automation. It is working fine so far.

Thanks!