I have a list of records that's something like this.
| PROJECT NAME | PROJECT VERSION | CONTACT | ASSIGNEE |
| Apples | 1 | Joe | Bob |
| Apples | 2 | Joe | Bob |
| Bananas | 1 | Sally | Jim |
| Oranges | 1 | Joe | Judy |
I receive a .csv report with the most recent information about the first three fields every week. The fourth field (assignee) lives only in Airtable. There are additional fields as well as notes and comments on the records that need to be maintained.
The weekly report often includes changes. For example, a new "active project" may be added or something might change about the project, like the CONTACT field.
Example .csv report:
| PROJECT NAME | PROJECT VERSION | CONTACT |
| Apples | 1 | Joe |
| Apples | 2 | Joe |
| Bananas | 1 | Jill |
| Oranges | 1 | Joe |
| Pears | 1 | Joe |
What I'm trying to do is find a way to import this new .csv report and have Airtable update/merge the existing records that have changed (in this case, the CONTACT on Bananas project is now Jill) and add any records that didn't already exist (Pears project).
The CSV Import extension only allows you to compare one field so the closest I've gotten to get this to work is to create a new formula field in Airtable that concatenates all relevant values:
| PROJECT NAME | PROJECT VERSION | CONTACT | ASSIGNEE | DupCheck |
| Apples | 1 | Joe | Bob | Apples1Joe |
| Apples | 2 | Joe | Bob | Apples2Joe |
| Bananas | 1 | Sally | Jim | Bananas1Sally |
| Oranges | 1 | Joe | Judy | Oranges1Joe |
And I did the same in the report that I receive:
| PROJECT NAME | PROJECT VERSION | CONTACT | DupCheck |
| Apples | 1 | Joe | Apples1Joe |
| Apples | 2 | Joe | Apples2Joe |
| Bananas | 1 | Jill | Bananas1Jill |
| Oranges | 1 | Joe | Oranges1Joe |
| Pears | 1 | Joe | Pears1Joe |
This allows me to use the DupCheck fields to compare and it does find the records that change. However, I can't get them to merge records. It only creates new ones, which forces me to have to update records that have changed manually. In this example, the result would be
| PROJECT NAME | PROJECT VERSION | CONTACT | ASSIGNEE |
| Apples | 1 | Joe | Bob |
| Apples | 2 | Joe | Bob |
| Bananas | 1 | Sally | Jim |
| Bananas | 1 | Jill | |
| Oranges | 1 | Joe | Judy |
| Pears | 1 | Joe | |
I still have to comb through the results and find ones like the Bananas project, eyeball the changes, and then manually update the existing Bananas project with "Jill" as the new CONTACT, then delete the newly created Bananas project.
I suspect the reason the merge does not work is because I'm using a formula field to compare and Airtable obviously can't merge anything into that field. I need to map the DupCheck fields/columns to each other so it can use them to check for differences, but I don't need to actually bring in the DupCheck info from the .csv file.
Is there a better way?
