mibuso.com
Home› NAV/Navision Classic Client

Copy data from one table to another

stormcandistormcandi Member Posts: 27
edited 2015-07-03 in NAV/Navision Classic Client
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

Answers

  • MBergerMBerger Member Posts: 413
    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.
  • lvanvugtlvanvugt Member Posts: 774
    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)
    Luc van Vugt, fluxxus.nl
    Never stop learning
    Van Vugt's dynamiXs
    Dutch Dynamics Community
  • bbrownbbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • stormcandistormcandi Member Posts: 27
    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/
  • matthiasclaesmatthiasclaes Member Posts: 18
    And use FINDSET instead of FIND('-') for a case like this.
Sign In or Register to comment.