Item Master Import - NAV 2013

anushafdo

I have a text file with around 15,000 items to be imported in Items table. Fields are

Item::No.
Item::Description
Item::Base Unit of Measure
Item::Inventory Posting Group
Item::Item Category Code
Item::Product Group Code
Item::Costing Method
Item::Unit Price
Item::Unit Cost
Item::Replenishment System

When we import Item, the Item Unit of Measure table also need to be populated (automatically) through "onValidate" of "Base Unit Of Measure" field. But, I am getting an error saying

Microsoft Dynamics NAV

---

The field Item No. of table Item Unit of Measure contains a value (100010001) that cannot be found in the related table (Item).

---

OK

---

Failed at

--Base Unit of Measure - OnValidate()

IF "Base Unit of Measure" <> xRec."Base Unit of Measure" THEN
  TestNoOpenEntriesExist(FIELDCAPTION("Base Unit of Measure"));

"Sales Unit of Measure" := "Base Unit of Measure";
"Purch. Unit of Measure" := "Base Unit of Measure";
IF "Base Unit of Measure" <> '' THEN BEGIN
  UnitOfMeasure.GET("Base Unit of Measure");
  ItemUnitOfMeasure.SETRANGE("Item No.","No.");
  IF ItemUnitOfMeasure.ISEMPTY THEN BEGIN
    ItemUnitOfMeasure.INIT;
    ItemUnitOfMeasure.VALIDATE("Item No.","No.");  //********Failed Here....................
    ItemUnitOfMeasure.VALIDATE(Code,"Base Unit of Measure");
    ItemUnitOfMeasure."Qty. per Unit of Measure" := 1;
    ItemUnitOfMeasure.INSERT;
  END ELSE BEGIN
    ItemUnitOfMeasure.GET("No.","Base Unit of Measure");
    ItemUnitOfMeasure.TESTFIELD("Qty. per Unit of Measure",1);
  END;
END;

Is there any other better methods to imports item master in NAV 2013? or a table order in which it has to be updated?

bbrown

The OnValidate for Item."Base Unit of Measure Code" is not there to create the "Item Unit of Measure" record. It is there to verify that one already exist. You need to populate the "Item Unit of Measure" table before the "Item" table. In fact, you need to populate the "Unit of Measure" table before the 'Item Unit of Measure" table.

This is just one example. You'll find many relation like this thru-out the database.

One approach is do design your import process to create the needed records in the supplemental tables as it loads the master tables.

Savatage

On an old dataport I did it like this:

It checks the IUOM table & if it doesn't already exist then it inserts just to get things rolling.
you can do something similar that works for you in an xmlport or as stated above..fill in the appropriate related tables first.

IF NOT "Item Unit Of Measure".GET(Item."No.", Item."Base Unit of Measure")
 THEN BEGIN
  "Item Unit Of Measure".Code := Item."Base Unit of Measure";
  "Item Unit Of Measure"."Item No." := Item."No.";
  "Item Unit Of Measure"."Qty. per Unit of Measure" := 1;
  "Item Unit Of Measure".INSERT(TRUE);
 END;

anushafdo

Thank you for the reply. But
Unit of Measure table is already filled (has all the valid UoM).
When I follow the above suggestion, I met with same kind of error due to the Table Relation and Validation codes in both tables.
I finally extracted IUOM from the Items to be imported and try to import using another XMLPort for IUOM table. Here too failed due to the above validation code reason.
I finally modified IUOM XMLPort to disable "FieldValidate" property of each field, and imported successfully. Then, the Items imported. At last, ran a process to VALIDATE Item No., Code and Qty... fields in IOUM.

Thank you again.