Copy data from one table to another

stormcandi

Hello all,

I am still relatively new to Dynamics NAV so am having trouble with this. I created a duplicate table of the Item Journal Line table. I am using that table to archive the data from the original table. I am trying to copy the necessary records from the regular Item Journal Line table to the new archive table but having no luck. This is my code:

Answer := DIALOG.CONFIRM('Are you sure you want to archive this data?');
IF Answer THEN BEGIN
  "Item Journal Line".SETFILTER("Journal Template Name", 'PHYS. INVE');
  IF "Item Journal Line".FIND('-') THEN
    REPEAT
    WITH "Item Journal Line Archive" DO BEGIN
      INIT;
      "Journal Template Name" := "Item Journal Line"."Journal Template Name";
      "Line No." := "Item Journal Line"."Line No.";
      "Item No." := "Item Journal Line"."Item No.";
      "Posting Date" := "Item Journal Line"."Posting Date";
      "Entry Type" := "Item Journal Line"."Entry Type";
      "Source No." := "Item Journal Line"."Source No.";
      "Document No." := "Item Journal Line"."Document No.";
      Description := "Item Journal Line".Description;
      "Location Code" := "Item Journal Line"."Location Code";
      "Inventory Posting Group" := "Item Journal Line"."Inventory Posting Group";
      "Source Posting Group" := "Item Journal Line"."Source Posting Group";
      Quantity := "Item Journal Line".Quantity;
      "Invoiced Quantity" := "Item Journal Line"."Invoiced Quantity";
      "Unit Amount" := "Item Journal Line"."Unit Amount";
      "Unit Cost" := "Item Journal Line"."Unit Cost";
      Amount := "Item Journal Line".Amount;
      "Discount Amount" := "Item Journal Line"."Discount Amount";
      "Salespers./Purch. Code" := "Item Journal Line"."Salespers./Purch. Code";
      "Source Code" := "Item Journal Line"."Source Code";
      "Applies-to Entry" := "Item Journal Line"."Applies-to Entry";
      "Item Shpt. Entry No." := "Item Journal Line"."Item Shpt. Entry No.";
      "Shortcut Dimension 1 Code" := "Item Journal Line"."Shortcut Dimension 1 Code";
      "Shortcut Dimension 2 Code" := "Item Journal Line"."Shortcut Dimension 2 Code";
      "Indirect Cost %" := "Item Journal Line"."Indirect Cost %";
      "Source Type" := "Item Journal Line"."Source Type";
      "Journal Batch Name" := "Item Journal Line"."Journal Batch Name";
      "Reason Code" := "Item Journal Line"."Reason Code";
      "Recurring Method" := "Item Journal Line"."Recurring Method";
      "Expiration Date" := "Item Journal Line"."Expiration Date";
      "Recurring Frequency" := "Item Journal Line"."Recurring Frequency";
      "Drop Shipment" := "Item Journal Line"."Drop Shipment";
      "Transaction Type" := "Item Journal Line"."Transaction Type";
      "Transport Method" := "Item Journal Line"."Transport Method";
      "Country/Region Code" := "Item Journal Line"."Country/Region Code";
      "New Location Code" := "Item Journal Line"."New Location Code";
      "New Shortcut Dimension 1 Code" := "Item Journal Line"."New Shortcut Dimension 1 Code";
      "New Shortcut Dimension 2 Code" := "Item Journal Line"."New Shortcut Dimension 2 Code";
      "Qty. (Calculated)" := "Item Journal Line"."Qty. (Calculated)";
      "Qty. (Phys. Inventory)" := "Item Journal Line"."Qty. (Phys. Inventory)";
      "Last Item Ledger Entry No." := "Item Journal Line"."Last Item Ledger Entry No.";
      "Phys. Inventory" := "Item Journal Line"."Phys. Inventory";
      "Gen. Bus. Posting Group" := "Item Journal Line"."Gen. Bus. Posting Group";
      "Gen. Prod. Posting Group" := "Item Journal Line"."Gen. Prod. Posting Group";
      "Entry/Exit Point" := "Item Journal Line"."Entry/Exit Point";
      "Document Date" := "Item Journal Line"."Document Date";
      "External Document No." := "Item Journal Line"."External Document No.";
      Area := "Item Journal Line".Area;
      "Transaction Specification" := "Item Journal Line"."Transaction Specification";
      "Posting No. Series" := "Item Journal Line"."Posting No. Series";
      "Reserved Quantity" := "Item Journal Line"."Reserved Quantity";
      "Unit Cost (ACY)" := "Item Journal Line"."Unit Cost (ACY)";
      "Source Currency Code" := "Item Journal Line"."Source Currency Code";
      "Document Type" := "Item Journal Line"."Document Type";
      "Document Line No." := "Item Journal Line"."Document Line No.";
      "Order Type" := "Item Journal Line"."Order Type";
      "Order No." := "Item Journal Line"."Order No.";
      "Order Line No." := "Item Journal Line"."Order Line No.";
      "Dimension Set ID" := "Item Journal Line"."Dimension Set ID";
      "New Dimension Set ID" := "Item Journal Line"."New Dimension Set ID";
      "Assemble to Order" := "Item Journal Line"."Assemble to Order";
      "Job No." := "Item Journal Line"."Job No.";
      "Job Task No." := "Item Journal Line"."Job Task No.";
      "Job Purchase" := "Item Journal Line"."Job Purchase";
      "Job Contract Entry No." := "Item Journal Line"."Job Contract Entry No.";
      "Variant Code" := "Item Journal Line"."Variant Code";
      "Bin Code" := "Item Journal Line"."Bin Code";
      "Qty. per Unit of Measure" := "Item Journal Line"."Qty. per Unit of Measure";
      "New Bin Code" := "Item Journal Line"."New Bin Code";
      "Unit of Measure Code" := "Item Journal Line"."Unit of Measure Code";
      "Derived from Blanket Order" := "Item Journal Line"."Derived from Blanket Order";
      "Quantity (Base)" := "Item Journal Line"."Quantity (Base)";
      "Invoiced Qty. (Base)" := "Item Journal Line"."Invoiced Qty. (Base)";
      "Reserved Qty. (Base)" := "Item Journal Line"."Reserved Qty. (Base)";
      Level := "Item Journal Line".Level;
      "Flushing Method" := "Item Journal Line"."Flushing Method";
      "Changed by User" := "Item Journal Line"."Changed by User";
      "Cross-Reference No." := "Item Journal Line"."Cross-Reference No.";
      "Originally Ordered No." := "Item Journal Line"."Originally Ordered No.";      
      "Originally Ordered Var. Code" := "Item Journal Line"."Originally Ordered Var. Code";
      "Out-of-Stock Substitution" := "Item Journal Line"."Out-of-Stock Substitution";
      "Item Category Code" := "Item Journal Line"."Item Category Code";
      Nonstock := "Item Journal Line".Nonstock;
      "Purchasing Code" := "Item Journal Line"."Purchasing Code";
      "Product Group Code" := "Item Journal Line"."Product Group Code";
      "Planned Delivery Date" := "Item Journal Line"."Planned Delivery Date";
      "Order Date" := "Item Journal Line"."Order Date";
      "Value Entry Type" := "Item Journal Line"."Value Entry Type";
      "Item Charge No." := "Item Journal Line"."Item Charge No.";
      "Inventory Value (Calculated)" := "Item Journal Line"."Inventory Value (Calculated)";
      "Inventory Value (Revalued)" := "Item Journal Line"."Inventory Value (Revalued)";
      "Variance Type" := "Item Journal Line"."Variance Type";
      "Inventory Value Per" := "Item Journal Line"."Inventory Value Per";
      "Partial Revaluation" := "Item Journal Line"."Partial Revaluation";
      "Applies-from Entry" := "Item Journal Line"."Applies-from Entry";
      "Invoice No." := "Item Journal Line"."Invoice No.";
      "Unit Cost (Calculated)" := "Item Journal Line"."Unit Cost (Calculated)";
      "Unit Cost (Revalued)" := "Item Journal Line"."Unit Cost (Revalued)";
      "Applied Amount" := "Item Journal Line"."Applied Amount";
      "Update Standard Cost" := "Item Journal Line"."Update Standard Cost";
      "Amount (ACY)" := "Item Journal Line"."Amount (ACY)";
      Correction := "Item Journal Line".Correction;
      Adjustment := "Item Journal Line".Adjustment;
      "Applies-to Value Entry" := "Item Journal Line"."Applies-to Value Entry";
      "Invoice-to Source No." := "Item Journal Line"."Invoice-to Source No.";
      Type := "Item Journal Line".Type;
      "No." := "Item Journal Line"."No.";
      "Operation No." := "Item Journal Line"."Operation No.";
      "Work Center No." := "Item Journal Line"."Work Center No.";
      "Setup Time" := "Item Journal Line"."Setup Time";
      "Run Time" := "Item Journal Line"."Run Time";
      "Stop Time" := "Item Journal Line"."Stop Time";
      "Output Quantity" := "Item Journal Line"."Output Quantity";
      "Scrap Quantity" := "Item Journal Line"."Scrap Quantity";
      "Concurrent Capacity" := "Item Journal Line"."Concurrent Capacity";
      "Setup Time (Base)" := "Item Journal Line"."Setup Time (Base)";
      "Run Time (Base)" := "Item Journal Line"."Run Time (Base)";
      "Stop Time (Base)" := "Item Journal Line"."Stop Time (Base)";
      "Output Quantity (Base)" := "Item Journal Line"."Output Quantity (Base)";
      "Scrap Quantity (Base)" := "Item Journal Line"."Scrap Quantity (Base)";
      "Cap. Unit of Measure Code" := "Item Journal Line"."Cap. Unit of Measure Code";
      "Qty. per Cap. Unit of Measure" := "Item Journal Line"."Qty. per Cap. Unit of Measure";
      "Starting Time" := "Item Journal Line"."Starting Time";
      "Ending Time" := "Item Journal Line"."Ending Time";
      "Routing No." := "Item Journal Line"."Routing No.";
      "Routing Reference No." := "Item Journal Line"."Routing Reference No.";
      "Prod. Order Comp. Line No." := "Item Journal Line"."Prod. Order Comp. Line No.";
      Finished := "Item Journal Line".Finished;
      "Unit Cost Calculation" := "Item Journal Line"."Unit Cost Calculation";
      Subcontracting := "Item Journal Line".Subcontracting;
      "Stop Code" := "Item Journal Line"."Stop Code";
      "Scrap Code" := "Item Journal Line"."Scrap Code";
      "Work Center Group Code" := "Item Journal Line"."Work Center Group Code";
      "Work Shift Code" := "Item Journal Line"."Work Shift Code";
      "Serial No." := "Item Journal Line"."Serial No.";
      "Lot No." := "Item Journal Line"."Lot No.";
      "Warranty Date" := "Item Journal Line"."Warranty Date";
      "New Serial No." := "Item Journal Line"."New Serial No.";
      "New Lot No." := "Item Journal Line"."New Lot No.";
      "New Item Expiration Date" := "Item Journal Line"."New Item Expiration Date";
      "Return Reason Code" := "Item Journal Line"."Return Reason Code";
      "Warehouse Adjustment" := "Item Journal Line"."Warehouse Adjustment";
      "Phys Invt Counting Period Code" := "Item Journal Line"."Phys Invt Counting Period Code";
      "Phys Invt Counting Period Type" := "Item Journal Line"."Phys Invt Counting Period Type";
      "Original Qty." := "Item Journal Line"."Original Qty.";
      "Tag No." := "Item Journal Line"."Tag No.";
      "Qty. Counted" := "Item Journal Line"."Qty. Counted";
      "Qty. Counted (Phys. Inv.)" := "Item Journal Line"."Qty. Counted (Phys. Inv.)";
      "Item Counted" := "Item Journal Line"."Item Counted";
      MonthYear := "Item Journal Line".MonthYear;
      "Total Cost (Calculated)" := "Item Journal Line"."Total Cost (Calculated)";
      "Total Cost (Physical)" := "Item Journal Line"."Total Cost (Physical)";
      "Variance (Cost)" := "Item Journal Line"."Variance (Cost)";
      "Variance (Qty)" := "Item Journal Line"."Variance (Qty)";
      "Variance Exist" := "Item Journal Line"."Variance Exist";
      "Input Journal Only" := "Item Journal Line"."Input Journal Only";
      "Location Filter" := "Item Journal Line"."Location Filter";
      "Overhead Rate" := "Item Journal Line"."Overhead Rate";
      "Single-Level Material Cost" := "Item Journal Line"."Single-Level Material Cost";
      "Single-Level Capacity Cost" := "Item Journal Line"."Single-Level Capacity Cost";
      "Single-Level Subcontrd. Cost" := "Item Journal Line"."Single-Level Subcontrd. Cost";
      "Single-Level Cap. Ovhd Cost" := "Item Journal Line"."Single-Level Cap. Ovhd Cost";
      "Single-Level Mfg. Ovhd Cost" := "Item Journal Line"."Single-Level Mfg. Ovhd Cost";
      "Rolled-up Material Cost" := "Item Journal Line"."Rolled-up Material Cost";
      "Rolled-up Capacity Cost" := "Item Journal Line"."Rolled-up Capacity Cost";
      "Rolled-up Subcontracted Cost" := "Item Journal Line"."Rolled-up Subcontracted Cost";
      "Rolled-up Mfg. Ovhd Cost" := "Item Journal Line"."Rolled-up Mfg. Ovhd Cost";
      "Rolled-up Cap. Overhead Cost" := "Item Journal Line"."Rolled-up Cap. Overhead Cost";

      INSERT;
    END;
    UNTIL "Item Journal Line".NEXT = 0;
    MESSAGE('Data archived.');
END;

However when I run the codeunit that contains this code I get the following error:
'The Item Journal Line Archive already exists. Identification fields and values: Journal Template Name = PHYS. INVE'

There are duplicate values in the Journal Template Name field however that is allowed. I am not sure what I am doing wrong. Any help is greatly appreciated.

Thanks in advance!

Candi

MBerger

the primary key of your archive table is set to just one ( i assume the first ) field, that is the cause of the error you are getting. But even if you change the key to the same as the original table, this would not work : Over time there will be more than one Item Journal Line with Template X, Batch Y and Line Z ( the key of table 83, if i am not mistaken ), so you need to add something to be aboe to distinguish between those ( a date or time, maybe )

Also.....you could have reduced that wall of code with just one TRANSFERFIELDS.

lvanvugt

MBerger wrote:

the primary key of your archive table is set to just one ( i assume the first ) field, that is the cause of the error you are getting. But even if you change the key to the same as the original table, this would not work : Over time there will be more than one Item Journal Line with Template X, Batch Y and Line Z ( the key of table 83, if i am not mistaken ), so you need to add something to be aboe to distinguish between those ( a date or time, maybe )

True, however I would archive it as is and add a new field called Entry No. (data type Integer) like on ledger entry tables and making this the only PK field.

MBerger wrote:

Also.....you could have reduced that wall of code with just one TRANSFERFIELDS.

Agree. Makes live easier.
Next to that from a best practice point of view I would say: you 'should'. Have a look at how archiving is done in NAV and also how posted documents are created (i.e. copied form the unposted version)

bbrown

lvanvugt wrote:

True, however I would archive it as is and add a new field called Entry No. (data type Integer) like on ledger entry tables and making this the only PK field.

I would also make that "Entry No." field an AutoIncrement integer. Then set secondary keys based on how I intended to view or report the data. The keys from the original table would be a guide, but only create what is needed.

stormcandi

Everyone, Thank you for your comments. I was able to make it work following your suggestions. I also was not aware of the TRANFERFIELDS function. I will look into that and start using it!


\:D/

matthiasclaes

And use FINDSET instead of FIND('-') for a case like this.