Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Compare records between two tables and return values that do not match

Solved
Jump to Solution
444 0
cancel
Showing results for 
Search instead for 
Did you mean: 
sreevi14
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, 

I'm trying to compare "name" field between two different tables and trying to fetch records that matches both.
Table 1 - Name, Table 2 - Legal name

What's the best method to achieve this? (New to Airtable)

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try:
1. Create a formula field and make it the primary field for 'Table 1', and give it a formula of "Name"
2. In 'Table 2', create a linked field to 'Table 1'
3. In 'Table 2', in a view where all records are visible, click the header of the 'Legal name' field
- This should select the entire column
4. Hit CMD/CTRL+C
5. In 'Table 2', in a view where all records are visible, click the header of the linked field from step 2
6. Hit CMD/CTRL+V

This will create a one to one link between the two tables for all the records where "Legal Name" match "Name" only

Table 1:
Screenshot 2024-08-20 at 12.17.03 PM.png

Table 2:
Screenshot 2024-08-20 at 12.17.06 PM.png

The linking process:
Screen Recording 2024-08-20 at 12.17.15 PM.gif

Link to base

See Solution in Thread

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try:
1. Create a formula field and make it the primary field for 'Table 1', and give it a formula of "Name"
2. In 'Table 2', create a linked field to 'Table 1'
3. In 'Table 2', in a view where all records are visible, click the header of the 'Legal name' field
- This should select the entire column
4. Hit CMD/CTRL+C
5. In 'Table 2', in a view where all records are visible, click the header of the linked field from step 2
6. Hit CMD/CTRL+V

This will create a one to one link between the two tables for all the records where "Legal Name" match "Name" only

Table 1:
Screenshot 2024-08-20 at 12.17.03 PM.png

Table 2:
Screenshot 2024-08-20 at 12.17.06 PM.png

The linking process:
Screen Recording 2024-08-20 at 12.17.15 PM.gif

Link to base

sreevi14
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you @TheTimeSavingCo ! it worked! 
I would also like to know how the table comparison can be done through Automation/scripting.

Hmm with an automation you could try having an automation that'll run per record in "Table 2" with a "Find record" step to look for a record in "Table 1" with the same Name value as the Legal Name, and have a conditional to update the records it manages to find.  This'll burn through your automations really quickly though

Scripting gets a bit more involved and I'd recommend reading up on the documentation, fairly straightforward if you're familiar with JavaScript  https://airtable.com/developers/scripting/api

If you're looking to hire someone to do it I'd be happy to help too!