Combining data from 2 tables

[Struan_Farquhar]

Hi new to Airtable, and getting stumped combining data on 1 table from 2 other tables. using 1 table to reference another table. The setup is:

I have 3 tables Staff/Projects/Billing,

• in the projects table I assign staff to a project and count the hours
• in the staff table I have each staff member and their rate and location
• in the billing table I list a billing line item link it to a project and then from the projects table I pull in the assigned staff member and the hours worked. I then want to perform a lookup on the staff table using the staff name pulled from the projects table, using this to get the country and the price. But this is where I am getting stumped.

The obvious answer is that I pull the staff data to the projects table and then pull that data again to the billing table, but I want to avoid the doubling up if possible as in the long term this will be used on a largish database so any optimization I can achieve will help.

FYI in this situation, linked columns are single records only.

stills of the setup below to help explain.

Overall Schema

Projects table

Staff table

Billing table - the price and country column are just to illustrate

Is there a way to do lookups as I’m after or is it better to just pull data through.

Cheers.

[ScottWorld · Accepted solution]

You are correct.

Unless you’d like to link each billing record to BOTH a project AND a staff member directly from the billing record, the only way for your current system to pull in the staff data is to first pull it into the projects table and then pull that data again to the billing table.

Unfortunately, unlike other database systems, Airtable’s lookups can’t pull in data from “multiple tables away” from the current table. Airtable’s lookups can only pull in data from the one DIRECTLY-CONNECTED TABLE that the table is linked to.

Manually linking each billing record to both a project and staff member would be one solution, but you’d be doubling your efforts by typing in the staff member’s name again.

You could also potentially automate the process of typing in the staff member’s name again by writing a JavaScript to retype the staff member’s name for you, or using a tool like Integromat to retype the staff member’s name for you. But it might not be worth the extra effort. Might be easier to just pull the staff information into projects first, and then pull it in again to Billing Records.

[Struan_Farquhar]

Thank you @ScottWorld. Pulling data through the Projects table it is.

[ScottWorld]

You’re welcome! Glad I could help! :slightly_smiling_face: