I have a client where we needed to consolidate into a single employee master, records from 5 different payroll systems.
What we did is to create a field in the 5 source tables where we took the EE# and prefixed it with a Company identifier. That way the Company-EE# would be unique even if there were two EE# with EE# 123.
Then we set up 5 saved table to table imports to map the fields across into the Master table.
Then an Automation to run the import each night.
So the the only issue to deal with is if you need to delete employees who are no longer in the source table. That could be done with a look-ups back to the source table to detect if the source record was deleted and then an Automation to delete Orphans. An atternate appoach is to import
Today()
Into a field called [Date EE last seen in Source].
into the master and then for EE's who have an old value in that field, say more than 1 week old they must have been deleted, so you could delete those Automatically with an Automation.
