Matching values from two different tables

I have a CRM system that can export Revenue data in a .csv format. That data includes an Owner field, which displays the person responsible for generating each line of revenue.

I have created a table in my base that has the same fields as the Revenue Report file. Weekly, I want to import the Revenue Report data into my base. The goal is to calculate commissions owed, while two other tables capture hours worked per week and miscellaneous commissions earned. Payroll calculation is the ultimate use case here.

For these reasons, I have a company Personnel table in my base. My question is how to leverage a link between the Personnel and Revenue tables. When the imported Revenue data comes in, it includes the Owner’s name. Since the Personnel table contains the exact same names, I have created a link between the Revenue and Personnel tables.

My Question: When I import data from the externally generated Revenue Report into my Revenue table, the Owner in one of the records is Sam Smith. I have a Sam Smith record in my Personnel table, but there is no way to attach Sam’s Personnel record to that Revenue record automatically.

I have read there is no Match function that can automatically, or formulaically, do this. Any suggestions?

The easiest way of doing this is this:

  1. In your Personnel table, the name of the person needs to be the primary field (i.e. the first column).
  2. In your Revenue table, create a linked record field that links to the Personnel table.
  3. When you import data into your Revenue table, import the person’s name into the linked record field.
1 Like

Thanks ScottWorld. That sounds like a good plan. I already had the name of the person as my primary field in the Personnel table. I’ll give the importation strategy a shot. So, I’ll be placing the linked Personnel record field in the same position as the Owner field from the import, correct?

Yes, import the “Owner” field into your linked record field in the Revenue table, and Airtable will automatically connect it to the Personnel table.

Worked like a champ! Thanks!

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.