NAV 3.70 Item Re-numbering

Kayewain

Does anyone have a solution for re-numbering a large number of items, in excess of 7000. Currently via the manual process its taking approx 8 minutes per item and blocks all users. Therefore not enough downtime to run as a job out of hours.

Regards
Kayewain

nunomaia

It takes times to rename. Navision must go to all tables with relationships and also rename those tables.

if you can't run a batch job after work hours, I can’t see other way around.

themave

We had our solution center program a report for us, to do it.

our particular need was to change a string within the part number to a new string. Our part numbers all follow a specific format, there is a series of letters, a dash and a series of numbers or letters. The first section represents a manufacture. and in our case that is the section we need to change some times. We enter the filter in the report request form and the report runs through the items renumbering them, there is an option to commit after each rename. It doesn't speed up the renumbering time, but it allows us to start it at night and renumber as many as posible during off hours. unfortunately it only does about 150 a night, so your 7000 would take a long time to complete.

the report has two data items, one is the item table, the second is the integer

here is the code on the item table section

IF GETFILTERS = '' THEN
ERROR('Please filter your selection of Items to be renumbered.');

IF SectionDivider = '' THEN
ERROR('Please specify a Section Divider.');

IF (SectionNo < 1) OR (SectionNo > 9) THEN
ERROR('Please enter a Section No. between 1 and 9.');

IF ReplaceWithStr = '' THEN
ERROR('Please enter a Replace with String value.');

Item - OnAfterGetRecord()
TempItem.INIT;
TempItem."No." := "No.";
TempItem.INSERT;

Here is the code on the integer section


SETRANGE(Number, 1, TempItem.COUNT);

Window.OPEN(
'Renumbering items...\\' +
'#1######################################');

Integer - OnAfterGetRecord()
IF Number = 1 THEN
TempItem.FIND('-')
ELSE
TempItem.NEXT;

CLEAR(Section);
CurrSection := 1;
i := 0;
WHILE (i <= STRLEN(TempItem."No.")) AND (CurrSection <= 9) DO BEGIN
i += 1;
IF COPYSTR(TempItem."No.", i, 1) = SectionDivider THEN
CurrSection += 1
ELSE
Section[CurrSection] := Section[CurrSection] + COPYSTR(TempItem."No.", i, 1);
END;

NewItemNo := '';

IF Section[SectionNo] <> '' THEN
FOR i := 1 TO 9 DO BEGIN
IF i = SectionNo THEN
Section := ReplaceWithStr;
IF Section <> '' THEN
IF i = 1 THEN
NewItemNo := Section
ELSE
NewItemNo := NewItemNo + SectionDivider + Section;
END;

IF NewItemNo <> '' THEN BEGIN
Window.UPDATE(1, STRSUBSTNO('%1 -> %2', TempItem."No.", NewItemNo));
_item.GET(TempItem."No.");
IF _item.RENAME(NewItemNo) THEN BEGIN
RenumCount += 1;
IF CommitEachItem THEN
COMMIT;
END;
END;

Integer - OnPostDataItem()
Window.CLOSE;
COMMIT;
MESSAGE('%1 out of %2 items renumbered', RenumCount, TempItem.COUNT);


the request form has space to enter the divider we use in our part number, the section of the part number we want to replace, and the code we want to be replaced with

nunomaia

themave:

Therefore not enough downtime to run as a job out of hours

I think Kayewain is asking a way of improve item rename performance. In huge databases it's takes time and there isn't a way to improve performance.

themave

nunomaia wrote:

themave:

Therefore not enough downtime to run as a job out of hours

I think Kayewain is asking a way of improve item rename performance. In huge databases it's takes time and there isn't a way to improve performance.

I thought one reason there wasn't enough down time is he couldn't complete the whole batch in one night or weekend, this would allow him to do it in phases. but that may not help him.

The only way I got it to speed up any, was to change a few primary keys, run the rename and then change the keys back. I don't have access to code units so I couldn't change the rename code to pick the best key.

When you run the rename process on a single item, watch as it processes each item, it goes through a list and says searching sales invoice header table, ect, through all the tables, there will be a few that takes really long, on those tables you need to change the primary key start with the item number. this speeds up the process a lot. But you can't leave it that way because the rest of your regular activity will be slowed way down.

Now if you are a developer maybe you can change the code to select the best key.

Slawek_Guzek

Hi,

Try this:

1. Export all tables to fob.
2. Make a new database, restore backup to it
3. In all tables disable all keys except primary
4. Export modified tables to fob, then import it to prodution database
5. Run rename process
6. Import fob with original tables.

Disabling keys will speed up rename, as system does not have to rebuild indexes and SIFT information every renamed item. Instead it will rebuild SIFTs in step 6.

Disabling keys *may* slow down the process, so it migth be better to leave some keys turned on. You will have to do some tests - turn off all non primary keys and rename one item - when you see that system start to counting records in some table - that means you turned off some necessary key on that table.

Instead of turning off keys you may clear SIFT fields (or tur all SIFT levels maintenance) in every key that has SIFT defined.

Another method is to unload all necessary data to external file, rename them using SED for example, and reload the data.

Regards
Slawek.

nunomaia

Slawek Guzek wrote:

Hi,

Try this:

1. Export all tables to fob.
2. Make a new database, restore backup to it
3. In all tables disable all keys except primary
4. Export modified tables to fob, then import it to prodution database
5. Run rename process
6. Import fob with original tables.

Disabling keys will speed up rename, as system does not have to rebuild indexes and SIFT information every renamed item. Instead it will rebuild SIFTs in step 6.

Disabling keys *may* slow down the process, so it migth be better to leave some keys turned on. You will have to do some tests - turn off all non primary keys and rename one item - when you see that system start to counting records in some table - that means you turned off some necessary key on that table.

Instead of turning off keys you may clear SIFT fields (or tur all SIFT levels maintenance) in every key that has SIFT defined.

Another method is to unload all necessary data to external file, rename them using SED for example, and reload the data.

Regards
Slawek.

if it takes 8 minutes to rename a item, probably it's a large database. Disabling keys the in a system that don't have no stop hours can create a serious performance problem.

davmac1

Are you running the item renumbering client/server or as a single client on the server with the Navision database and a huge database cache?

Slawek_Guzek

Hi,

As I understood there are stop hours but the whole process (7000x8min) will take too long to fit that time.

Disabling keys will not cause performance problems - it will kill Navision - all funcionality based on SIFT or non primary keys will fail. Every single SETCURRENTKEY will fail. Every form displaing anything based on SIFT field won't open. Every form using different key than primary.

Regardless of that in my opinion renaming using described method is possible - but requires some tests. Of course it is possible to perform only in stop hours. All table object have to be prepared in a copy, tests should be run in a copy, and if everything went fine modified tables can be imported to production server, and have to be restored to the original state after rename, before system go online again

Regards,
Slawek