Primary Key Values not getting Modified

chandrurec

Hi all,

I am trying to modify the Primary Key Values present in a tables named "Import Sales Header"

In this table , the primary Key is Type,Integration Partner Code,File Name

The coding written to modify is as follows

IF ModifyImportSalesHeader.GET(ImportSalesHeader.Type,ImportSalesHeader."Integration Partner Code",
ImportSalesHeader."File Name") THEN
BEGIN
ModifyImportSalesHeader.RENAME(ModifyImportSalesHeader.Type = ModifyImportSalesHeader.Type::Archived,
ModifyImportSalesHeader."Integration Partner Code", ModifyImportSalesHeader."File Name");
// ModifyImportSalesHeader.MODIFY;
MESSAGE('%1',ModifyImportSalesHeader);
CurrForm.UPDATE(TRUE);
END;

I want to change the primary key values of Type filed form "To Import" to "Archive".Type is a option field in Import sales header table.

Kindly tell me the procedure to change the value of Primary key field having option datatype or if you have some sample coding , kindly reply me to this .

Thanks in advance.

Regards,
Chandru.

FDickschat

I usually go with the following:
Rec1.get
Rec2 := Rec1
Rec2.PrimField := New Value
Rec2.Insert
Rec1.delete

But the whole table structure seems to be strange to me. It seems to be some kind of import table where you keep additionally to the data a status field and a file name. In these cases I usually use a simple EntryNo as PrimKey (with AutoIncrement) and have all your fields as simple fields which can just be changed.

matteo_montanari

chandrurec wrote:

Hi all,

I am trying to modify the Primary Key Values present in a tables named "Import Sales Header"

In this table , the primary Key is Type,Integration Partner Code,File Name

The coding written to modify is as follows

IF ModifyImportSalesHeader.GET(ImportSalesHeader.Type,ImportSalesHeader."Integration Partner Code",
ImportSalesHeader."File Name") THEN
BEGIN
ModifyImportSalesHeader.RENAME(ModifyImportSalesHeader.Type = ModifyImportSalesHeader.Type::Archived,
ModifyImportSalesHeader."Integration Partner Code", ModifyImportSalesHeader."File Name");
// ModifyImportSalesHeader.MODIFY;
MESSAGE('%1',ModifyImportSalesHeader);
CurrForm.UPDATE(TRUE);
END;

I want to change the primary key values of Type filed form "To Import" to "Archive".Type is a option field in Import sales header table.

Kindly tell me the procedure to change the value of Primary key field having option datatype or if you have some sample coding , kindly reply me to this .

Thanks in advance.

Regards,
Chandru.

Hi

don't do an "Update(true)" after a modify/rename.
And also, "ModifyImportSalesHeader.Type = ModifyImportSalesHeader.Type::Archived" is ambiguos because this instruction produce a boolean result...

try something like:

      CurrForm.UPDATE(TRUE);
      COMMIT;
      IF ModifyImportSalesHeader.GET(ImportSalesHeader.Type,ImportSalesHeader."Integration Partner Code",
      ImportSalesHeader."File Name") THEN BEGIN
         ModifyImportSalesHeader.RENAME(ModifyImportSalesHeader.Type::Archived,
         ModifyImportSalesHeader."Integration Partner Code", ModifyImportSalesHeader."File Name");
         COMMIT;
         CurrForm.UPDATE(FALSE);
      END;

bye

Matteo

chandrurec

Hi FDickschat/Matteo,

Thank you so much. I tried the way you two have coded in the previous posts and it is working perfect.

Hats off to you both.

Regards,
Chandru.

julkifli33

i want to try rename item no in item table
yes it can changed
but why not looping
so i must press the button one by one
can anyone fix my code?
thanks

Item.SETFILTER(Item."No.",'1000..2000');
IF Item.FIND('-') THEN
REPEAT
      BEGIN
          Item.RENAME('OLD' + Item."No.");
         Item.RESET;
      END;
UNTIL Item.NEXT =0;

mohana_cse06

try

Item.RESET;
Item.SETFILTER("No.",'%1..%2','1000','2000');
IF Item.FINDSET THEN
  REPEAT
    Item2.GET(Item."No.");
    Item2.RENAME('OLD' + Item."No.");
  UNTIL Item.NEXT = 0;

julkifli33

mohana_cse06 wrote:

try

Item.RESET;
Item.SETFILTER("No.",'%1..%2','1000','2000');
IF Item.FINDSET THEN
  REPEAT
    Item2.GET(Item."No.");
    Item2.RENAME('OLD' + Item."No.");
  UNTIL Item.NEXT = 0;

[/quote]
[/code]

i am using cronus database as trial
but without the filtering

Item.RESET;
IF Item.FINDSET THEN
  REPEAT
    Item2.GET(Item."No.");
    Item2.RENAME('OLD' + Item."No.");
  UNTIL Item.NEXT = 0;

but why when it reach Item LS-MAN-10,it always failed
it was said

overflow under type conversion of text to code
OLD OLD OLD OLD LS-MAN-10

mohana_cse06

Try this

Item.RESET;
IF Item.FINDSET THEN
  REPEAT
    IF STRPOS(Item."No.",'OLD') = 0 THEN BEGIN
      Item2.GET(Item."No.");
      Item2.RENAME('OLD' + Item."No.");
    END;
  UNTIL Item.NEXT = 0;

julkifli33

mohana_cse06 wrote:

Try this

Item.RESET;
IF Item.FINDSET THEN
  REPEAT
    IF STRPOS(Item."No.",'OLD') = 0 THEN BEGIN
      Item2.GET(Item."No.");
      Item2.RENAME('OLD' + Item."No.");
    END;
  UNTIL Item.NEXT = 0;

WOW !!
it works...
big thanks Mohana

mohana_cse06

Welcome

julkifli33

one more question
for cronus database it's okay
under 1 minute is finished
but using live database, validating alll the table
is there any way to make it faster?
maybe set a key?
thanks

mohana_cse06

It depends upon the No. of Items in database.

In cronus we hardly have 150 Items..

Do you have any code in OnRename() or No. - OnValidate() field of Item Table except standard coding?

julkifli33

OnRename()
"Last Date Modified" := TODAY;

[b]IF WebSite.FIND('-') THEN
  SynchMgt.RenameItem(Rec,xRec);[/b]

No. - OnValidate()
IF "No." <> xRec."No." THEN BEGIN
  GetInvtSetup;
  NoSeriesMgt.TestManual(InvtSetup."Item Nos.");
  "No. Series" := '';
END;

after i compare with the standard NAV item table
i found

IF WebSite.FIND('-') THEN
  SynchMgt.RenameItem(Rec,xRec);

is it affecting so much?

julkifli33

for your information, i have almost 25000 items

mohana_cse06

julkifli33 wrote:

for your information, i have almost 25000 items

:shock:

Obviously it will take time and

IF WebSite.FIND('-') THEN
SynchMgt.RenameItem(Rec,xRec);

I hope it is not a standard Codeunit so you ahve to check what is there in that codeunit..

julkifli33

mohana_cse06 wrote:

julkifli33 wrote:

for your information, i have almost 25000 items

:shock:

Obviously it will take time and

IF WebSite.FIND('-') THEN
SynchMgt.RenameItem(Rec,xRec);

I hope it is not a standard Codeunit so you ahve to check what is there in that codeunit..

this one is not codeunit
this one is table Web Site (table 6217)
i think this one is from nav 4
because when i check using nav 2009 R2, this table is not available

mohana_cse06

I hope those objects belongs to Commerce Portal Status functionality.

julkifli33

mohana_cse06 wrote:

I hope those objects belongs to Commerce Portal Status functionality.

this table is empty
i think it will not affect anything

julkifli33

after 1 hour waiting, and then my NAV is not responding
or can I do it every 5000 records?

julkifli33

i think it's no use
i try for 1 record only
rename it manually... it takes time about 5 minutes

but i try using native database
is it any different if I use SQL server?

Sog

Renaming an item means also that the system will modify every record that has the item No. in it. And that's a lot: item ledger entry, value entry are amongst those.
But you should build in a dialog that let's you track process of your renaming, that way NAV will keep responding (even if it's just that dialog).
I suggest you rename a couple of items maybe 500 or so, and extrapolate the time it took to the 24500 remaining records.
Then you can plan it in to have the renaming of those items running through the night or weekend.

julkifli33

Sog wrote:

Renaming an item means also that the system will modify every record that has the item No. in it. And that's a lot: item ledger entry, value entry are amongst those.
But you should build in a dialog that let's you track process of your renaming, that way NAV will keep responding (even if it's just that dialog).
I suggest you rename a couple of items maybe 500 or so, and extrapolate the time it took to the 24500 remaining records.
Then you can plan it in to have the renaming of those items running through the night or weekend.

i already add a dialog
1 record 5 minutes... i think i must use another way
besides renaming these items

Sog

1 item is not representative for 25000 items. Top selling items will have a longer time to rename than unpopular items.
Oh and a rename on SQL vs. Native will be different, but I can't say which one is faster.
It's best practice to have the DEV db running on the same db-type as the live db. That way you won't have problems you can't replicate on your dev db.

FDickschat

Check your Object Cache Setting In Tools/Options. Set it to 100.000 or more and rename will be much faster.

Renaming an item on a large customers DB (230 GB database with 75000 items, 6,5 Mio Item Ledger Entries and 9 Mio Value Enries) takes approx. 30 seconds for the first item and just 2-3 seconds per next item (which is still 20+ hours for all 25000 items).