Automation to update one table based on common field in another table

Since the reported error is "The record does not exist" I suspect one of two things is happening:

1. The "Find Records" action did not return any records

2. The original test record you were using was deleted.

If it's number two, go back to the trigger step and select a new test record, then try it again.

Overall though, the intelligent solution here is to use a linked record field for the client, and use a lookup field for the notes. You mentioned this can't be done without a huge amount of effort. Can you elaborate on this?

Actually, I just noticed that you have the incorrect record in the update step. The record you are updating should not be a list of the records you found in step 2, it should be the trigger record from step 1.

There will be hundreds of projects and so it is very inefficient for our team to have to use linked records in this step. The data is pulled through an API and so the client name is already part of the Project table. This means our team would have to add the project and then the client as a separate linked field. We are finding this very inefficient for larges amounts of data. It also causes issues where the team could accidentally select the wrong client with the linked fields. We can create checks to ensure this data entry error doesn't occur but again it is adding additional effort, time and complexity. 

In other data systems like SQL you would just do a join between the two tables which is automated and ensure there is no ability to have data entry errors. 

The linked fields are a great feature but in some cases like this are highly error prone and inefficient. 

This seems to do it but there is one bug I don't understand. This works when I run it as the test preview or run as configured in the automation. It doesn't work when a new row is added. When a new row is added all the values are added as list not just the matched value for that client from the client table, see project 0006 compared to project 0003.

The goal is that when new projects are added the corresponding client information is added to the projects table. 

How is your client table populated? If it's pre-existing data that the client info from the Project must already match, then whatever function/automation you are running to populate the Project table should be able to automatically link the client record (there should be no need for human intervention/no chance for human error).

"In other data systems like SQL you would just do a join between the two tables which is automated and ensure there is no ability to have data entry errors."

As far as I understand, Airtable functions much like SQL in its abilities as a relational database, given the right base design and implementation.

In any case, see my second message above and let me know if that was the issue, I'm fairly confident that that is the source of your error.

If you move forward with this route, another question I have is whether or not the notes in the Client info is ever updated/changed. If so, you'll want to write a second automation to watch for updates in the relevant fields in the client table to update the client info in the project table. Again, this would be done automatically with linked and lookup fields, but if that is not feasible then this is the way.

Yes the client information is pre-existing data. It can change but it is not very common for it change so yes I will need to have another automation to watch for updates on this. 

I find the syntax of the automations to be confusing and the official Airtable guides I don't find very helpful. I very much appreciate your help. 

When you are adding the new project (adding a row), are you typing in the name of the Client or is the new row getting added with all data simultaneously (ie through a form, being pasted in from a spreadsheet, or getting added from an external "glue factory" like Make/Zapier)?

If you're typing it in manually, here's whats happening...

When you add data to a cell, Airtable does not wait until you hit "Enter" to register the update. In other words, while you are typing "Client B," Airtable is registering the field value update multiple times from "C" to "Cli" to "Client" to "Client B", or some other variation of steps from "C" to "Client B". The automation is getting triggered prematurely (somewhere between "C" and "Client "), so when it runs the Find Records Action, it returns all records because all records pass the validation check Client Name contains Client Name-- all records start with "Client ").

You can verify this by looking at the automation's run history, and seeing what value it used for the Find Records action.

When you have a base structure like this (specifically, your automation as curently configured), you need to force record creation through a form so that all data is added in a complete state when the record is created (Airtable will see this field go from empty directly to "Client B").

If you'd like to retain the ability to add records from directly in the base's Data layer, you will need to use a different trigger (ie. a checkbox that is manually ticked when the client input is complete).

Side note: If you know that you are looking for a 100% match between the Client in the Project table and the Client table, change your Find Records condition to is exactly instead of contains.

This is what was happening. Amazing, thank you so much for the clear and thorough explanation! It is fully working as I need now. 

No problem, glad I was able to help!

Hi there! 

You could use a similar automation structure to create the link for your team automatically. If there's an export of your client information already synced or added in your base, it could find it by a common field in the record (such as client name &/or point of contact email). Additional steps to ensure the automation runs smoothly is implementing a formula for your primary field with the information you'd like to search for (Client Name & '|' Email) .

You could also set up the automation to be conditional

- if length of found record =1 , link the record

- if length of found = 0 (or its a 'new' client record) it can create a new record and link it to the project