How can I update multiple records in Airtable? (Efficiently)

[Zylan]

Text:

I am interacting with two tables in my Airtable

1. First table: "Payment History," where I have a lookup column called "Unpaid Delivered Tasks" that displays codes in a format like "#C0005, #F0055, #T800."

2. Second table: "Sales," where the first column is called "Unique Code," and it contains codes corresponding to each sale. Additionally, there's an important column named "Advisor Payment Status" with two options: "Pending" and "Paid."

What am I looking for?

I'm trying to find the most effective way to update records in the "Sales" table when an automation or script is executed from a specific record in the "Payment History" table. The goal is to search for all the codes listed in the "Unpaid Delivered Tasks" column of the "Payment History" table and update the corresponding records in the "Advisor Payment Status" column of the "Sales" table to mark them as "Paid."

What I've tried:

1. I attempted to create an automation where I manually entered the code into a text column. This worked well with a single code, for example, "#C0005." However, when I tried with two codes, like "#C0005, #F0055," it didn't work because Airtable treated it as a single value.

2. I also tried creating a script with a button in the record, but I'm not an expert in programming. I've created some scripts, but I always receive the message that no matching codes were found in the "Sales" table, even when I'm attempting to update just one code.

I would greatly appreciate it if someone could provide a solution or tips for achieving this efficient update in Airtable. Thank you!

[Alexey_Gusev]

Hi,
Such way of working with linked tables is quite unusual, but what I've recommend:
Create another link between tables, in 1st table call linked field 'Paid Sales'. It will auto-create another linked field in table 2 and mirror there all new links that you creating in table 1.
When you paste "Unpaid Delivered Tasks" in "Paid Sales", it will link it to each corresponding code in Table 2, understanding that #C0005, #F0055 are two different records.
- Make "Advisor Payment Computed Status" formula field in table 2, so if {Auto-created linked field} is empty, it's Unpaid, and if not, it's Paid
- Create automation with 'when record matches condition'. If '"Advisor Payment Status" != "Advisor Payment Computed Status", add action 'Update record' and update your field.
Later, you can even omit 'Computed Status" and automation run, if it's OK to turn "Advisor Payment Status" to the formula field type.

Note - Codes in table 2 must be truly Unique. You should paste #C0005, #F0055 without brackets, otherwise it thinks it's a single value. If you paste non-existing code, it will create a new record with such code.