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: