Tuesday, 22 November 2011

Table structure and code sample for product attributes

The following code sample shows the table structure and relationships for the new product attribute structure. It will display the attribute values for the nominated item.

The setup is found under Procurement and Sourcing / Setup / Categories / Procurement categories, which are then attached to a global product (Product master form / Product categories).

Further comments in the code. As usual, the queries are expanded out for clarity.

static void ShowProductAttributes(Args _args)
{
    // Show all product attribute values for a specified item.
    // (Expanded queries)
    ItemId                          itemID = 'test01';
    InventTable                     inventTable;
    EcoResProduct                   product;
    EcoResProductCategory           productCat;
    EcoResCategory                  category;
    EcoResCategoryHierarchy         catHierarchy;
    EcoResCategoryAttributeLookup   catAttributeLookup;
    EcoResAttribute                 attribute;
    EcoResProductAttributeValue     prodAttrValue;      // view based on EcoResAttributeValue
    EcoResProductInstanceValue      prodInstanceValue;
    EcoResValue                     value;
    ;

    // Find local+global product
    inventTable         = inventTable::find(itemID);
    product             = EcoResProduct::find(inventTable.Product);

    // EcoResProductInstanceValue is another level of indirection between the
    // category/attribute setup and values back to the product number. Not sure
    // why this exists as opposed to just referencing field 'Product' directly.
    prodInstanceValue   = EcoResProductInstanceValue::findByProduct(product.RecId);

    setPrefix(product.DisplayProductNumber);

    // Select all categories that are attached to the product
    while select productCat
        order by catHierarchy.Name
        where   productCat.Product  == product.RecId
    join category
        where   category.RecId      == productCat.Category
    join catHierarchy
        where   catHierarchy.RecId  == category.CategoryHierarchy
    {

        // Select all product attributes attached to the category. NB the
        // category attribute lookup table (EcoResCategoryAttributeLookup)
        // includes entries for attributes inherited from parent levels.
        //
        // In contrast, table EcoResCategoryAttribute only defines attributes attached at
        // each level.
        while select catAttributeLookup
            where   catAttributeLookup.Category     == category.RecId
        join attribute
            where   attribute.RecId                 == catAttributeLookup.Attribute
        {
            // Select the 'value' record for the current attribute. This links
            // a product and attribute reference to an instance of EcoResValue
            // (an inherited table structure for the different data types).
            // Method EcoResValue.value() determines the display value based on the
            // type of that attribute.
            select firstOnly prodAttrValue
                where   prodAttrValue.Attribute == attribute.RecId
                &&      prodAttrValue.Product   == product.RecId
            join value
                where   value.RecId             == prodAttrValue.Value;

            info(strFmt("%1 = %2",attribute.Name,value.value()));

        }
    }

}

NB In later versions of Ax (including 2012R3), you'll need to attach attribute categories to an item via the Retail module.


Thursday, 17 November 2011

Running report via code in Ax2012

This is a quick example of running an Ax2012 report via code - In this example we're outputting the Vendors report (Accounts Payable / Reports / Vendors / Vendors) directly to a PDF file.


static void RunSSRSReport(Args _args)
{    
    SrsReportRunController  reportRunController;
    Map                     queryContracts;
    MapEnumerator           mapEnum;
    Query                   query;
    QueryBuildRange         range;
    ;
    
    // Create the report run controller
    reportRunController = new SrsReportRunController();
    reportRunController.parmReportName('Vend.Report');
    reportRunController.parmLoadFromSysLastValue(false);
    
    // NB call to parmLoadFromSysLastValue must occur before any reference to 
    // parmReportContract, otherwise the previous values (query ranges etc)
    // are defaulted in.
    
    // Set printer settings (print to file, format, filename, etc).
    reportRunController.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    reportRunController.parmReportContract().parmPrintSettings().overwriteFile(true);    
    reportRunController.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    reportRunController.parmReportContract().parmPrintSettings().fileName('c:\\test.pdf');
        
    // Find/enumerate queries in the contract. The return from parmQueryContracts is
    // a map of type <ParameterName,Query(class)>    
        
    queryContracts = reportRunController.parmReportContract().parmQueryContracts();
    mapEnum = queryContracts.getEnumerator();    
    while(mapEnum.moveNext())    
    {
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();   
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));
        range.value('1*'); 
    }    
    
    // Run the report
    reportRunController.runReport();
    
}

Note that this displays a message to the inflog once the file has been written. This may not be desirable if the file generation is part of a background process - If you need more control of this, have a look at adding an additional flag (eg supressInfoLog) to the class SrsReportRunPrinter.

Tuesday, 15 November 2011

Quick code example - Event subscription

This is a very quick run-through of a basic event subscription in Ax2012.

There are two classes:
  • MyPostingRoutine is the class that does the main work.
  • PostingLogger provides an event handler that will be attached and run after the posting has completed.



Class MyPostingRoutine

This is the class that the event is attached to. In real terms this could be something like SalesFormLetter, used to post sales documents. In the picture above, you can see that a new event subscription has been attached to the doPost method (by right-clicking the method and selecting new -> post event handler subscription).

static void main(Args args)
{
    new MyPostingRoutine().run();
}

public void run()
{                
    setPrefix("Starting process");        
    
    this.doPost('PostingTransID00021');
    
    info("Process has been completed");
}

private void doPost(str someParameter)
{            
    setPrefix("Posting");                
    
    info("Transaction A posted");
    info("Transaction B posted");
    info("Transaction C posted");        
    
    info("Posting complete");
}


Class PostingLogger

This defines a method that does some additional work, such as logging information to an extra table, or possibly sending a document electronically.

The _args passed in (of type XppPrePostArgs) allows the event handler to access the parameters passed into the original function - In this example MyPostingRoutine.doPost.

public static void postHandler(XppPrePostArgs _args)
{           
    str paramFromParentArgs = _args.getArg('someParameter');
    
    setPrefix("Post logger - Event handler");        
    
    info(strFmt("Posting information for '%1' logged",paramFromParentArgs));    
}

Because of the event subscription, method postHandler will be called once doPost has completed.

There's a good series of articles covering this in much more detail at http://daxmusings.blogspot.com/2011/07/ax-2012-models-events-part1-model.html

Wednesday, 9 November 2011

Job to dump Ax menu structure to Excel

The following code produces an Excel (CSV) dump of the Ax menu structure. This works with all versions, including 2012.

static void DumpMenuStructure(Args _args)
{    
    // Dump menu structure to temp CSV file. 
    
    TreeNode    rootNode;    
    CommaIo     outFile;
    str         filename;
    container   line;
    
    void recurseNode(TreeNode node)
    {
        int             childIdx;
        TreeNode        childNode;
        int             spacerIdx;
        TreeNodeType    nodeType = node.treeNodeType();
        ;
        
        line += node.AOTName();
        outFile.write(line);                                
        
        // Recurse sub-menus
        childNode = node.AOTfirstChild();   
        if(childNode)
        {
            while(childNode)
            {            
                recurseNode(childNode);            
                childNode = childNode.AOTnextSibling();            
            }
        }
        else
        {
            // Possible menu reference
            childNode = TreeNode::findNode('\\Menus\\' + node.AOTname());   
            if(childNode)
                recurseNode(childNode);
        }
        line = conDel(line,conLen(line),1);
    }
    
    ;
    
    filename    = WinAPI::getTempFilename(winAPI::getTempPath(),'') + '.csv';
    outFile     = new CommaIo(filename,'w');
    
    recurseNode(TreeNode::findNode('\\Menus\\MainMenu'));
    
    outFile = null; 
    WinAPI::shellExecute(filename);
    
}

Job to output menu structure to Excel (CSV)

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.