Tablewide changes based on new employee number

[AlanP]

I have a big problem. Payroll assigns our employees their own 'number' when they start with us. However some may leave then come back so payroll assigns then a new 'number'. This doesn't happen a lot but it does happen.

In our database I use at least 10 related tables and they base their relationship off the employee number...so if someone changes to a new employee number I have to manually change the records in all those tables.

Is there a way to fix this (etc)...if I have to manually do changes to make the relationship work better I'll do it!

Thanks!

[T-Square]

I'd create a script that manages this, and assign that script to a "Reassign Employee Number" button on the employee page. Essentially, this script will go to the different layouts (which represent the dependent tables), perform a find on the old employee number, replace that old number with the new number, and then move to the next layout/table. You should be sure to check that you perform the changes in an appropriate order.

HTH,

David