With Excel integration we have run into issues from the business user sending us older excel files with updated content that causes the ID numbers and content to become out of sync.
The scenario follows:
- We download the data set using excel integration with existing data. Let's say January 1st.
- Email this file to the user for them to update data.
- The business user save the file on their computer in folder "Data Template", updates the data and emails it back to us.
- We receive the file, import it with the updated data and everything is wonderful.
- This process continues back and forth with multiple revisions and multiple users.
- Months later ... something happens to the data within the table so we truncate the table and use our latest received template with blank ID numbers to reset the data set.
- We then download and send out the Excel file with new id numbers, etc. And instruct the business users to use this latest excel file going forward.
- Weeks later we receive a excel file with updated information, however the user forgot to use the new excel file and instead used an older one without knowing.
- We import this Excel file but the incorrect records get updated because the ID numbers no longer match.
- Without careful analysis this could be easily overlooked since there is no indication of a problem.
A suggestion of how to handle this would be to have a revision indicator of some kind on the excel file. And when you run the import process, a warning could be shown when there is a mismatch. Also, since there could be multiple downloads of the excel file that do not qualify as a revision, you could add a checkbox to the download screen that indicates "This is not a revision" or "This is a master revision".
Eric Jensen
