Hi there! I am having trouble finding an easy way to clean my base of deleted records from a source file.
I am using a base to monitor projects for an innovation program. Each project has an unique ID.
These projects come from exports from an external platform (CSV files). I then import the data into Airtable to manage these projects easily, very regularly.
However, if a project is deleted on the external platform (and thus does not appear in the “master” CSV file), I can’t find a way to delete it automatically in my Airtable base.
I can easily import new projects using the unique ID but I can’t really find a way to compare the “master” CSV file that I use for the import with my “slave” database in Airtable.
I would like to figure a way to detect (using the unique ID) the records that have been deleted from the “master” CSV file but are still present into my “slave” base.
I would import your CSV file twice… once into your “main” table, and once into a “comparison” table.
The main table MUST HAVE the unique ID as the first column (the primary field) in order for this to work properly. Otherwise, this will fail.
Before importing into the comparison table, delete all the records from the comparison table.
Then, in the comparison table, import your records, and make sure that you import the unique IDs into a linked record field that links to the main table.
Back in the main table, you will now have a reverse linked record field that will show you whether it has a match in the comparison table. If this field is empty, it doesn’t have a match so it can be deleted.
ALTERNATIVELY: Depending on how often you’re going to be doing this, you may want to consider using an external automation platform like Integromat. Then, as soon as a record is created/updated/deleted in your external platform, the exact same action can be taken immediately in Airtable (create/update/delete). No need to deal with imports at all.
Note that I am a professional Airtable consultant and a Registered Integromat Partner, and the Integromat link contains my personal referral code. If you have a budget for this project and you’d like to hire an expert to help you set this up, please feel free to contact me through my website at ScottWorld.com.