Replacing Primary Key

kolaboy · 2008-01-09

I am about to replace the employee nos. with a new number.
I plan to rename the present employee nos. as old employee nos. and have the new as employee nos. and map the old nos. with the new nos and the new nos. become the primary key.

but i have this present nos which are going to be changed to old referenced in some tables like the absence registration table, employee relative. I know when the primary key is changed, it will also change in all tables its referencing to. It this true?
How will this affect those table?

Any advice on how to go about with this?
Thanks

Alex_Chow · 2008-01-09

Yes, rename will change all the related entries to the new value on the primary key.

Check out the RENAME function in the Help.

kolaboy · 2008-01-10

Now Client is receiving payments which they post using the present nos. If the change is done and customers are paying with the present nos, how can this be handle
Am still seeking for advices
Thanks

bvbeek · 2008-01-10

kolaboy wrote:

I am about to replace the employee nos. with a new number.
I plan to rename the present employee nos. as old employee nos. and have the new as employee nos. and map the old nos. with the new nos and the new nos. become the primary key.

but i have this present nos which are going to be changed to old referenced in some tables like the absence registration table, employee relative. I know when the primary key is changed, it will also change in all tables its referencing to. It this true?
How will this affect those table?

Any advice on how to go about with this?
Thanks

For a quick respone, try the chat function on Mibuso.com.
Maybe there is an developer online and its free!

Alex_Chow · 2008-01-10

kolaboy wrote:

Now Client is receiving payments which they post using the present nos. If the change is done and customers are paying with the present nos, how can this be handle
Am still seeking for advices
Thanks

You'll need to find a cut off point where you can do this transition. Either that or you will need to create 2 sets of numbers and manually merge the ledger entries together.

kriki · 2008-01-11

kolaboy wrote:

Now Client is receiving payments which they post using the present nos. If the change is done and customers are paying with the present nos, how can this be handle
Am still seeking for advices
Thanks

You can create a new field in which you put the old number.
Somewhere in the code for payments (I suppose T81), you can create a new field that is used instead of the old one. This new field checks if the number exists and then it just transfers to the original field. If it doesn't exist, it must check the old numbers and if it finds it, transfer the new number for that old number in the original field.
I have done that ones for the customer-nos that were renamed, and it works without problems.

kolaboy · 2008-01-11

Kriki, I will be very happy if you can send me the sample code. Thats is a very good ideas.
May i see the code please?
Thanks

kriki · 2008-01-11

kolaboy wrote:

Kriki, I will be very happy if you can send me the sample code. Thats is a very good ideas.
May i see the code please?
Thanks

I don't have the sample code anymore, but the new field in T81 is something like this:
"New Employee No. Field"-OnValidate-trigger

IF NOT recEmployee.GET("New Employee No. Field") THEN BEGIN
  recEmployee.RESET;
  recEmployee.SETCURRENTKEY("Old Employee No.");
  recEmployee.SETRANGE("Old Employee No.","New Employee No. Field");
  IF NOT recEmployee.FINDFIRST THEN
    ERROR('Employee "%1" does not exist',"New Employee No. Field");
  VALIDATE("Employee No.","New Employee No. Field");
END
ELSE
  VALIDATE("Employee No.",recEmployee."No.");

This new field ("New Employee No. Field"), you should put on the form and hide the old one. The users must use the new field.
If you have some procedure that imports the employee no., you need to validate the new field and NOT the original one in the table.

kolaboy · 2008-01-11

Thanks Kriki, I will test this code and if need be modify it to suit me. What is T81? do you mean Gen. Journal Line Table?
Thanks

kriki · 2008-01-11

kolaboy wrote:

Thanks Kriki, I will test this code and if need be modify it to suit me. What is T81? do you mean Gen. Journal Line Table?

Yes

kolaboy · 2008-01-14

I have imported the new employee nos. in a new field. I tried changing the primary key from the present no. to this new new, but i always have the error " There are two nos. with the same primary key"

What is the problem?
Thanks

kriki · 2008-01-17

It means that you probably have an employee that you want to rename to another number and that number already exists.
Another possibility is that by renaming the employee number, Navision also renames the field in other tables and in one of those other tables (one in which employee-number is in the primary key) this creates a double.

Replacing Primary Key

Comments

Categories