Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

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

1213 0
cancel
Showing results for 
Search instead for 
Did you mean: 
aylapois
6 - Interface Innovator
6 - Interface Innovator

I have a project table and a client table. I need to use automations to have the client related information auto populate into the projects table. I have created a very basic example to show what I am trying to do but the real situation is way more complex with hundreds of rows and we are unable to use the linked field type to do this without a huge amount of effort. Since the client name is a common and unique value between both tables my understanding is there is a way to use automation to populate the information in the project table with the corresponding information (Notes and Status) from the Client table. 

The Trigger and Find Records action do as I expect but I keep getting an error with the Update record action and am unsure of what to do to fix this issue. 

 

Screenshot 2025-01-10 at 8.51.38 AM.png

 

Screenshot 2025-01-10 at 8.51.57 AM.png

 

Screenshot 2025-01-10 at 8.52.26 AM.png

image.png

Screenshot 2025-01-10 at 8.49.31 AM.png 

 

 

 

 

 

 

 

10 Replies 10
Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

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?

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

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. 

 

aylapois
6 - Interface Innovator
6 - Interface Innovator

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. 

 

Screenshot 2025-01-10 at 10.02.48 AM.png

image.png

 

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. 

 

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

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. 

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

No problem, glad I was able to help!