Tuesday, January 17, 2012

Dynamics AX 2012 Products Import using Excel Add-in

Dynamics AX 2012 Excel Add-in – Products Import



Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 Excel Add-in for import of products.

Challenge: Data model changes in Microsoft Dynamics related to high normalization and introduction of surrogate keys made import of products more complex. In order to create a product in Microsoft Dynamics AX 2012 both product definition and released product information will have to be provided. Please note that in Dynamics AX 2012 Rich Client product definition is automatically created when released product is created using decentralized approach.

Solution: Microsoft Dynamics AX 2012 ships with Item AIF Web Service (InventItemService) which can be used in integration scenarios. However Item AIF Web Service can’t be used in Excel at the moment for import of products. In order to import a product with product definition information using Excel the appropriate tables will be used.

Assumption: The assumption is that appropriate reference data such as item groups, etc. was created in advance.

Data Model:

Table Name

Table Description

EcoResProduct

The EcoResProduct table stores products and is the base table in the products hierarchy.

EcoResProductMaster

The EcoResProductMaster table stores product masters.

EcoResProductIdentifier

The EcoResProductIdentifier table contains a product identification that is available for users.

EcoResDistinctProduct

The EcoResDistinctProduct table stores products.

EcoResDistinctProductVariant

The EcoResDistinctProductVariant table stores product variants.

EcoResProductDimensionGroup

The EcoResProductDimensionGroup table contains information about a dimension group.

EcoResProductDimensionGroupProduct

The EcoResProductDimensionGroupProduct table stores information about relationships between products and dimension groups.

EcoResColor

The EcoResColor table stores color names.

EcoResSize

The EcoResSize table stores size names.

EcoResConfiguration

The EcoResConfiguration table stores configuration names.

EcoResProductMasterColor

The EcoResProductMasterColor table stores information about colors assigned to product masters.

EcoResProductMasterSize

The EcoResProductMasterSize table stores information about sizes that are assigned to product masters.

EcoResProductMasterConfiguration

The EcoResProductMasterConfiguration table stores information about configurations assigned to product masters.

EcoResProductVariantColor

The EcoResProductVariantColor table stores information about the colors that are assigned to product variants.

EcoResProductVariantSize

The EcoResProductVariantSize table stores information about the sizes that are assigned to product variants.

EcoResProductVariantConfiguration

The EcoResProductVariantConfiguration table stores information about the configurations that are assigned to product variants.

EcoResProductMasterDimensionValue

The EcoResProductMasterDimensionValue table is the base table in the product model dimension hierarchy.

EcoResProductVariantDimensionValue

The EcoResProductVariantDimensionValue table is the base table in the product variant dimension hierarchy.

EcoResProductDimensionAttribute

The EcoResProductDimensionAttribute table contains definitions of product dimension attributes (categories).

EcoResInstanceValue

The EcoResInstanceValue table contains the definitions of the instances of the components or products.

EcoResProductInstanceValue

The EcoResProductInstanceValue table contains definitions of values for the instances of attributes of a product.

InventTable

The InventTable table contains information about items.

InventTableModule

The InventTableModule table contains information about purchase, sales, and inventory specific settings for items.

InventItemLocation

The InventItemLocation table contains information about items and the related warehouse and counting settings. The settings can be made specific based on the items configuration and vary from warehouse to warehouse.

InventItemSalesSetup

The InventItemSalesSetup table contains the default settings for items, such as site and warehouse. The values are related to sales settings.

InventItemInventSetup

The InventItemInventSetup table contains the default settings for items, such as site and warehouse. The values are related to inventory settings.

InventItemPurchSetup

The InventItemPurchSetup table contains the default settings for items, such as site and warehouse. The values are related to purchase settings.

InventItemSetupSupplyType

The InventItemSetupSupplyType table contains information about the sourcing of items.

InventDim

The InventDim table contains values for inventory dimensions.

InventDimCombination

The InventDimCombination table contains variants of items. The variants are created as product variants that are based on product dimensions such as size, color, and configuration. These variants are replicated to the legal entity.

Data Model Diagram:

Products and Released Products

image


VSD: https://docs.google.com/open?id=0B3rbAZy5q2ExMWUzOGM4ZDEtNzE2OS00Yzk2LWIxYzEtOTkzNTU3M2RiZTMw


Red area highlights tables forming Products and Product Masters data model

Green area highlights tables forming Released Products data models

Blue area highlights tables implementing Product Dimensions data model


Walkthrough:

Connection

image

Add Data

image

Dynamics AX Error

image

Solution

- Add replacement key to DocuValue table

Dynamics AX Error

image

No Solution yet

Add Tables

image

Field Chooser

image

EcoResProduct

Field Name

Field Description

InstanceRelationType

Product number

Product type

Configuration technology

image

EcoResProductIdentifier

Field Name

Field Description

Product number

Products.Product number

image

EcoResProductDimensionGroupProduct

Field Name

Field Description

Product dimension group.Name

Product.Product number


image

InventTable

Field Name

Field Description

Item number

Product.Product number

Item type

Search name

image

Sequence:

1. EcoResProduct – Publish Selected

2. EcoResProductIdentifier – Publish Selected

3. EcoResProductDimensionGroupProduct – Publish Selected

4. InventTable – Publish Selected

Result:

Dynamics AX – Released Product

image

SQL Trace:

Product

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductTranslation (INSERT)

Product Master

- EcoResProduct (INSERT)

- EcoResProductMaster (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductDimensionGroupProduct (INSERT)

- EcoResProductMasterModelingPolicy (INSERT)

- EcoResProductTranslation (INSERT)

Product Master – Product Dimension (Size)

- EcoResProductMasterDimensionValue (INSERT)

- EcoResProductMasterSize (INSERT)

- EcoResSize (INSERT)

Product Master – Product Variant

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResDistinctProductVariant (INSERT)

- EcoResDistinctProductTranslation (INSERT)

- EcoResProductVariantDimensionValue (INSERT)

- EcoResProductVariantSize (INSERT)

Released Product – Product

- EcoResProduct (INSERT)

- EcoResDistinctProduct (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductTranslation (INSERT)

- InventItemSalesSetup (INSERT)

- InventItemInventSetup (INSERT)

- InventItemPurchSetup (INSERT)

- InventTable (INSERT)

- InventTableModule [Sales] (INSERT)

- InventTableModule [Invent] (INSERT)

- InventTableModule [Purch] (INSERT)

- InventItemLocation (INSERT)

- InventItemSetupSupplyType (INSERT)

Released Product – Product Master

- EcoResProduct (INSERT)

- EcoResProductMaster (INSERT)

- EcoResProductIdentifier (INSERT)

- EcoResProductDimensionGroupProduct (INSERT)

- EcoResProductMasterModelingPolicy (INSERT)

- EcoResProductTranslation (INSERT)

- InventItemPurchSetup (INSERT)

- InventItemInventSetup (INSERT)

- InventItemSalesSetup (INSERT)

- InventTable (INSERT)

- InventTableModule [Sales] (INSERT)

- InventTableModule [Invent] (INSERT)

- InventTableModule [Purch] (INSERT)

- InventItemLocation (INSERT)

- InventItemSetupSupplyType (INSERT)

Released Product – Product Dimension

- EcoResProductMasterDimensionValue (INSERT)

- EcoResProductMasterSize (INSERT)

- EcoResSize (INSERT)

Release Product – Released Product Variant

- EcoResProductVariantDimensionValue (INSERT)

- EcoResProductVariantSize (INSERT)

- EcoResSize (INSERT)

- InventDim (INSERT)

- InventDimCombination (INSERT)

Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of products into Microsoft Dynamics AX 2012. Although Item AIF Web Service (InventItemService) can’t be used at the moment in Excel for import of products, appropriate tables can be used instead. Excel template can be created and used for import of products.

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Products.

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.

21 comments:

  1. Hi Alex,

    Thank you for great articles.
    I have question. Did you try to add InventTableModule to update from excel ? i.e. update Unit or Price fields for selected item?

    I tried several times and I was getting error message with no details

    SaModule type Item number Error Messages
    les order ITEM001 Document Generic document could not be updated. Error details:



    Thank you,
    Victor

    ReplyDelete
    Replies
    1. Hi Victor!

      Thank You for very interesting question

      This behavior is caused by the design of AxInventTableModule Class which has to be used in conjunction with AxInventTable Class. However in our scenario we just want to work with InventTableModule table in stand-alone mode.

      Please see detailed explanation of the issue and solution in the document I created: https://docs.google.com/open?id=0B3rbAZy5q2ExZTFmMDk5MDItOGYzOS00YTA0LWIwZDktZGUzNzIyOTgwYjgy

      Best Regards,
      /Alex

      Delete
  2. Hi Alex,
    Thanks for sharing and this is really helpful.
    I got the exact same error on InventTableModule as Victor described - "Document Generic document could not be created. Error details:".
    I also got the same error on EcoResStorageDimensionGroupItem and EcoResTrackingDimensionGroupItem. I need to assign storage dimension group and tracking dimension group on legal entity level instead of on the shared level which I would use EcoResStorageDimensionGroupProduct and EcoResTrackingDimensionGroupProduct.

    I am wondering if I missed anything. Any suggestions would be really appreciated.

    Thanks,
    Jasmine

    ReplyDelete
  3. I try to Import Product with variant ? dont know the exact tables to use ?

    ReplyDelete
  4. Thanks Alex for the blog.
    I have about 50 odd companies in Ax2012 and the release products to each company and then adding the mandatory setup fields will be cumbersome to repeat in all 50 companies for the end user.

    Can i share the released products table and basically any other dependent table that you have referred to at the top. This way when the release products is done one time, it will end up with the product being available in all companies.

    Please let me know your thoughts.
    Thanks
    Mansoor

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. 3 of the tables publish just fine, but on the DimensionGroup table, I get an error (see below) Any ideas?

    EcoResProductDimensionGroupProduct.createList Line=4, Pos=4, Xpath=/EcoResProductDimensionGroupProduct/EcoResProductDimensionGroupProduct[1] The value in field Product is invalid.

    Field 'Product' must be filled in.
    Error found when validating record.
    Field 'Product' must be filled in.
    Error found when validating record.
    Creation has been canceled.

    ReplyDelete
  7. Hi Alex,
    I have added some fields on EcoResDistinctProductVariant table, i am trying to publish data but it gives me below error


    Invalid document schema. The following error was returned: The element 'EcoResDistinctProduct' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResDistinctProduct' has invalid child element 'TPNetWeight' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResDistinctProduct'. List of possible elements expected: 'ProductMaster' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResDistinctProduct'.


    the TPNetWeight is the new field i added. How map this field. which service needs to be modified.

    Regards

    ReplyDelete
  8. Thanks mate! Really good to know.


    Could you please answer me how I can add a category to the product in Excel too? Pretty boring adding a category after you created the product, hehe

    ReplyDelete
  9. Hi Alex,

    When I press on publish select for (EcoResStorageDimensionGroupItem)
    to import the storage dimension, I faced this error:
    Document Generic document could not be created. Error details:

    Kindly advice if you have solution for this error.

    Thanks in Advance
    Amr Esam

    ReplyDelete
  10. i want to import "financial dimensions value" in AX 2012 R2

    Plz help

    ReplyDelete
  11. Hi Alex, when importing EcoResProductDimensionGroupProd, I get the following: Invalid document schema. The following error was returned: The element 'EcoResProductDimensionGroupProduct' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResProductDimensionGroupProduct' has invalid child element 'ProductDimensionGroup' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResProductDimensionGroupProduct'. List of possible elements expected: '_DocumentHash, Product' in namespace 'http://schemas.microsoft.com/dynamics/2008/01/documents/EcoResProductDimensionGroupProduct'.

    I hope you can help me.
    Best regards
    Rodolfo

    ReplyDelete
  12. Replies
    1. hi smrithi shuthi,

      my name is husain i am located in kuwait.

      wanna discuss with you for Dynamics AX related ping me on my skype ID
      hacker_ng

      Thanks & Regards.

      Husain.N.G
      0096550402552

      Delete
  13. Hi Alex,

    we want to upload External Item description which is MFG code from the supplier.

    how do we bulk upload

    ReplyDelete
  14. Dear Mr. Alex,

    can we have full process for uploading items with variant (color,size and style) combination from start to end
    even for the normal items also without variants.

    ReplyDelete
  15. Hi all,

    hope all are doing good.

    i am working in a retail industry where we are not getting any way forward for weighing scale item can any one help us to find the solution we will be very thank full of some one can help us.

    my email address and contact number is mentioned below.

    hgodhrawala@saveco.com
    00965 90004576 Watsapp account
    00965 50402552 Mobile

    ReplyDelete
  16. Hi,

    if anyone can help me with this error.
    Cannot create a record in Items - storage dimension groups (EcoResStorageDimensionGroupItem)
    am trying to amend some info in the InventTable, am not creating any item, am only amended some data like default vendor account.
    what is the reason behind this error?

    how to solve it?

    regards

    ReplyDelete
  17. I have an error message:
    The number sequence does not allow the Item number to be defined. Creation has been canceled.
    Changing number sequance to manual does not help

    ReplyDelete
  18. Thanks for good article!
    Everything works :)

    ReplyDelete