Wednesday, 2 November 2011

Global products - Table structure in Ax2012

The structure of product master data in Ax2012 has changed quite a lot from previous versions. This post is a brief overview of some of the technical differences - It covers tables and relationships so is probably aimed more at developers.

Global product setup

Ax2012 now has the concept of global and locally-released items - Product information is setup at a global level, then enabled per-company.



Global product information is defined within the EcoRes family of tables. The main list of products is defined in EcoResProduct, which is further extended as shown below in the type hierarchy browser.


The structure can be a bit confusing on first look. When looking at the "All products and product masters" form, the Product subtype tells you where in the table hierarchy the item lives. A "Product master" is an instance of EcoResProductMaster, and a "Product" is an EcoResDistinctProduct.

The "Product subtype" field indicates which type of record you're looking at (method EcoResProduct.productSubtype) - This just returns the type based on the value of the instanceRelationType, which determines the specific record type within the table hierarchy:



The dimension structure is now split out into separate groups for tracking (batch, serial), product (size, colour), and storage (site, warehouse, location). An overview of the structure for tracking dimensions is shown below:


Dimension group and link to product (tracking dimensions)

There are similarly named tables for the storage and product dimensions, eg:

  • EcoResStorageDimensionGroup 
  • EcoResStorageDimensionGroupProduct
  • EcoResStorageDimensionGroupFldSetup

Global product setup - Variants


Product variants (EcoResDistinctProductVariant) are linked to a product master. The definition of a product variant is controlled by the product dimension group attached to the item.



The following code sample lists all products. For product masters, all attached variants are also shown.

static void ShowProducts(Args _args)
{
    EcoResProduct                   product;
    EcoResDistinctProductVariant    productVariant;
    ;
    while select product
        order by product.DisplayProductNumber
        where   product.InstanceRelationType    == tableNum(EcoResProductMaster)
        ||      product.InstanceRelationType    == tableNum(EcoResDistinctProduct)
    {
        
        switch(product.productSubtype())
        {
            case EcoResProductSubtype::ProductMaster:
            
                info(strFmt("Product master %1",product.DisplayProductNumber));
                
                while select productVariant
                    where   productVariant.ProductMaster    == product.RecId                        
                {                    
                    info(strFmt(" - Variant %1",productVariant.DisplayProductNumber));
                }
            
                break;
                
            case EcoResProductSubtype::Product:                
                info(strFmt("Product %1",product.DisplayProductNumber));                
                break;
        }        
    }
}

Dump all products and master/variant combinations to infolog

There are multiple tables used to track dimension attributes within a variant. The following is an overview:


Definition of product dimension attributes


Linkage between variants and product dimensions

The selectable product dimensions are determined by the product dimension group attached to the master.



The following code takes a product master and displays the variant dimension values. The code is expanded out for the sake of demonstration:

static void ShowProductVariantInfo(Args _args)
{    
    // Show product variant information    
    EcoResProduct                       product;
    EcoResDistinctProductVariant        variant;
    EcoResProductDimensionGroup         productDimGroup;
    EcoResProductDimensionGroupProduct  productDimGroupProduct;
    EcoResProductDimensionGroupFldSetup dimGroupFldSetup;
    
    EcoResProductVariantDimensionValue  variantDimValue;
    EcoResProductDimensionAttribute     dimAttribute;
    RefRecId                            dimValueRecID;
        
    int                                 inventDimFieldID;
    int                                 attrTableID;
    DictTable                           dictTable;
    Query                               query;
    QueryRun                            queryRun;
    Common                              common;
    ;
    
    // Select the product master and product dimension group
    product                 = EcoResProduct::findByDisplayProductNumber('NEWPROD');
    productDimGroupProduct  = EcoResProductDimensionGroupProduct::findByProduct(product.RecId);    
    productDimGroup         = EcoResProductDimensionGroup::find(productDimGroupProduct.ProductDimensionGroup);
    
    setPrefix(strFmt("Product master %1",product.DisplayProductNumber));
    
    // Show all active fields within the product dimension group
    while select dimGroupFldSetup
        where   dimGroupFldSetup.ProductDimensionGroup  == productDimGroup.RecId
        &&      dimGroupFldSetup.IsActive               == true
    {            
        inventDimFieldID = dimGroupFldSetup.DimensionFieldId;            
        info(strFmt(" - %1 is an active product dimension",
            fieldid2PName(tableNum(InventDim),inventDimFieldID)));
    }
    
    // Select the variants
    while select firstOnly variant
        where   variant.ProductMaster   == product.RecId
    {        
        info(strFmt(" - Variant %1",variant.DisplayProductNumber));        
                
        // Show all dimension values for this variant
        while select variantDimValue
            where   variantDimValue.DistinctProductVariant  == variant.RecId              
        {                        
            // Select the attribute - This record points to the table
            // that stores the underlying dimension values.
            dimAttribute    = EcoResProductDimensionAttribute::find(variantDimValue.ProductDimensionAttribute);
            attrTableID     = dimAttribute.DimensionTableId;
            dimValueRecID   = variantDimValue.getDimensionValue();
                                    
            query = new Query();
            query.addDataSource(attrTableID)
                .addRange(fieldName2id(attrTableID,'RecID'))
                .value(queryValue(dimValueRecID));
            queryRun = new queryRun(query);            
            queryRun.next();
            common = queryRun.getNo(1);           
            
            info(strFmt(" - %1 = %2",
                tableId2name(dimAttribute.DimensionTableId),
                common.(fieldName2id(attrTableID,'Name'))));
        }        
    }
}

Show dimension values by querying variant attributes

The system also maintains dimension values in the table InventDimCombination, which links a product variant number to an inventory dimension record (via InventDimID). With that in mind, the below code achieves a similar result (NB this only applies to released products).

static void ShowProductVariantInfo2(Args _args)
{
    // Show product variant information
    EcoResProduct                       product;
    EcoResDistinctProductVariant        variant;
    EcoResProductDimensionGroup         productDimGroup;
    EcoResProductDimensionGroupProduct  productDimGroupProduct;
    EcoResProductDimensionGroupFldSetup dimGroupFldSetup;

    int                                 inventDimFieldID;
    InventDim                           inventDim;
    InventDimCombination                inventDimCombination;
    ;

    // Select the product master and product dimension group
    product                 = EcoResProduct::findByDisplayProductNumber('NEWPROD');    
    productDimGroupProduct  = EcoResProductDimensionGroupProduct::findByProduct(product.RecId);
    productDimGroup         = EcoResProductDimensionGroup::find(productDimGroupProduct.ProductDimensionGroup);

    setPrefix(strFmt("Product master %1",product.DisplayProductNumber));
    
    // Select variant
    while select firstOnly variant
        where   variant.ProductMaster   == product.RecId
    {
        info(strFmt(" - Variant %1",variant.DisplayProductNumber));

        inventDimCombination    = InventDimCombination::findByDistinctProductVariant(variant.RecId);
        inventDim               = inventDimCombination.inventDim();
        
        // Show all active fields within the product dimension group,
        // and the value from InventDimCombination/InventDim
        while select dimGroupFldSetup
            where   dimGroupFldSetup.ProductDimensionGroup  == productDimGroup.RecId
            &&      dimGroupFldSetup.IsActive               == true
        {
            inventDimFieldID = dimGroupFldSetup.DimensionFieldId;
            info(strFmt(" - %1 = %2",
                fieldid2pname(tableNum(inventDim),inventDimFieldID),
                inventDim.(inventDimFieldId)) );
        }
    }
}

Extract variant dimension values using InventDimCombination

Released products

A product or product master cannot be transacted until it has been released to one or more companies. This is done by selecting 'Release products' from the product/product master form, as shown:


Release products

This creates entries in the product table (InventTable), for each selected company. The released item points back to the global product table via field 'Product'. All transactions like sales order/purchase order lines, invoices, inventory movements etc are then based on the InventTable record, via ItemID.

2 comments:

  1. Is it possible to allocate weight and CBN carton sizes against a particular varient e.g. Large Towel?

    ReplyDelete