Make a report to guide manual changes to seperate database.

[cgreaves]

My team has several hundred user profiles with personal information stored in a database with no API. We keep the personal info there, but we have an SFTP to upload CSVs for information regarding statuses and due dates on various per-profile tasks. Those CSVs land in Airtable, where I'm working to create interfaces that are a lot more user friendly than the database. 

The really unfortunate thing is, I need a way to track any status or date changes we make in Airtable in order for teammates to manually update that data in the database. At any one time, there will be three "truths": what the old upload previously said, what the teammate has changed it to, and what the new upload says. (I should note, the new upload will probably overwrite the old upload? I'm not sure, I haven't set it up yet as I'm designing the base with fake data.)

I can't wrap my head around the best way to do this. I could:

1. Have user status/date changes change the last uploaded data, but then compare to the new uploaded data before overwriting and spit out a list of conflicts? It gets complicated when I think about overwriting after that, though. In the case of conflicts (assuming that list is saved somewhere now) I would ideally have Airtable prefer user changed data, then new CSV data, then old CSV data. But after the overwrite, will I lose the fact that some of it is user changed?

2. I could have separate fields for the database data and the Airtable data, and then I could filter for any records where they don't match and use that as an update list. But at some point, I'd want the new database data to still be a source of truth as records get added or deleted, for example. So that seperate airtable field would need to be refreshed or something every now and then? 

I feel like I'm just thinking about it wrong and overcomplicating it. Any ideas how to manage this?

[Blake_D · Accepted solution]

Without seeing your base overall structure you could try this.

User Profiles Table: to store personal information and a unique identifier for each profile.

Task Status Table: Store task statuses and due dates with fields for: 

• Old CSV Data
• User-Changed Data
• New CSV Data

Task Status Table Fields

• Profile ID: Link to the User Profiles table.
• Old CSV Status/Date: Fields to store the last uploaded data.
• User-Changed Status/Date: Fields for users to manually update the status and date.
• New CSV Status/Date: Fields to store the newly uploaded data.
• Final Status/Date: Formula fields to determine the final status/date based on priority (User-Changed Data > New CSV Data > Old CSV Data).
• Conflict Indicator: Formula field to highlight any conflicts between the User-Changed Data and New CSV Data.

Upload New CSV Data

1. Import New CSV: Import the new CSV data into the New CSV Status/Date fields.
2. Compare and Detect Conflicts: Use the Conflict Indicator field to detect discrepancies between the User-Changed Data and New CSV Data.

Update User-Changed Data

1. Update by Users: Allow users to make changes in the User-Changed Status/Date fields.
2. Conflict Resolution: When a conflict is detected, the Final Status/Date field should use the following logic:
• Prefer User-Changed Data if it exists.
• Use New CSV Data if User-Changed Data does not exist.
• Default to Old CSV Data if neither User-Changed nor New CSV Data exists.

Overwriting Data

1. Generate Update List: Create a filtered view to show records where the Final Status/Date differs from the Old CSV Status/Date. This view will act as your update list for manual database updates.
2. Overwrite Old CSV Data: Once the manual update is confirmed, overwrite the Old CSV Status/Date fields with the Final Status/Date values.
3. (Option) If you want to get rid of some the manual process, you can use an automation set by a time trigger monthly or quarterly, depending on how often you upload new data, that updates the old with the new and clears out the other fields. 

Formulas To Use

• Conflict Indicator: IF(User-Changed Status != New CSV Status, "Conflict", "No Conflict")
• Final Status: IF(User-Changed Status !=' ', User-Changed Status, IF(New CSV Status !=' ', New CSV Status, Old CSV Status))

If you have any questions feel free to ask.

[cgreaves]

Thanks! Just copying the pervious round of uploads to seperate fields is smart, and I think it's simple enough to be easy to automate.