Data sync - Remove not found records

[JPing]

Hello, 

I am importing a daily Excel file. I put the data in a staging table and then transform it to my actual table. 

The Excel file contains a list of active status for user in some system. My goal is to have my table update the records that are no longer in that file. 

Example: 

Day 1: 

I get a row for: Alice, Bob, Charlie, Dave

I add 4 Records to my Table and set the status flag to true. 

Day X: 

I get rows for Alice, Dave, Edward.

I add 1 Records for Edward and set the status flag to true

I set the status flags for Bob and Charlie to false. 

How would I know to flip Bob and Charlie's flags to false if they are not in the file any more?

Possible Ideas: 

1. write an "onStart" transform script that switches all flag to false and have the transform map reset all the flags
   
   1. Downside is that I'm updating all the records unnecessarily on every run.
2. Add a "last synced" field and then do a OnComplete or Schedule job that flips the status flags to false that were not last synced.
   1. Downside is that I have a field that has no business value, and is only used to facilitate this logic.  

Is there a simpler solution that I'm not aware that might be a built in feature of transform maps?

Thanks In advance. 

[Tushar]

Hi there,

Use a transform script to compare the records in the staging table to the records in the Excel file. The transform script could then update the status flags for any records that were in the staging table but not in the Excel file.

This solution would not require you to add any new fields to your table, and it would not require you to run any scheduled jobs.

However, it would require you to write a transform script, which could be more complex than using a scheduled job.

Please, don't forget to mark my answer as correct if it solves your issue or mark it as helpful if it is relevant for you!

Regards,
Tushar