Takes too much time to rename a large item table

scott

Hi everyone,

Has anyone ever had to rename a whole table of master records such as the Item table where there are many other tables that must get renamed also and where there are many, many items to rename??

I have a clients that wants to rename all the items in the item table - they have 250,000 items and it takes 2-3 minutes to rename each one. This happens if you do it manually or if you run a codeunit, report etc. If I try running the code in a database with 7,000 items it takes only a few minutes for the whole table to be renamed.

Any help on this would greatly be appreciated!

Luc_VanDyck

Try disabling some keys on the large tables (Item Entries, Sales Line, Purchase Line, etc.) where "Item No." is a part of the key. Then, after the renaming of all items, re-enable those keys again. And you can also open the database locally, to minimize network traffic.

Hope this helps.

daerom

Well I dont know if I am right on this one or not, but I think I am. Like most relational databases it has to update the records that it is related to. When you rename an Item doesnt it go back through all the item ledger entries along with G/L entries and update them with the new Item no.? I think it does... and well that is a very hard operation to do in a timely basis... my guess is that 2-3 minutes per item is about right... And, like what was suggested before, shut down the service and open the database locally to speed it up a little. There is my 2 cents

Tirta

hi.. which command can I use to rename the Item number over Report or codeunit?
I have tried with modify(true) but no results.
Can anyone help me?

best regards.
Rizal

Luc_VanDyck

You can rename a record with ... RENAME:

From the C/SIDE online help:

<start quote>
RENAME
Use this function to change a primary key in a C/SIDE table.

[Ok]:= Record.RENAME(Value1, [Value2],...)
Ok

Data type: boolean

This tells you whether the system was able to rename the primary key or not. Typically, the system will return FALSE if the record does not exist or if you do not have permission to write to the table.

Record

Data type: record

The record that contains the primary key that you want to change.

Value1, Value2, ...

Data type: text

The new values for the primary key.
<end quote>

Tirta

Thanks luc for the answer.
I have also already tried that ways before but still no result.
It comes such error messages:

"Too many key fields were specified, so Item could not be retrieved.
The number of fields in the primary key is 1."

Do you have any idea?
Thanks before for your advice.

Regards,
Rizal

Tirta

Here is the code example:

Item.Rename("No.","No"+'S');
Item.VALIDATE("No.");
Item.Modify(TRUE);

Your sincerely,
Rizal

Luc_VanDyck

Try this:

Item.GET('70000');
Item.RENAME('70000new');

Tirta

Yuhuuu.. It works!!!
Thanks a lot, Luc!!
Now we can rename 2 million item.

Have a nice weekend

Rizal