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);


    // 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

Ax utility function for sending mail via SMTP

The following is a quick utility function that can be used to send an email via a configured SMTP server. See comment in the code for the structure of the 'attachments' parameter. This was adapted from another code-sample, and should work with version 2009 and later.

Note this sends the mail immediately via a direct connection to the SMTP server. The preferred approach should be to create an entry in the SysOutgoingEmailTable (and SysOutgoingEmailData if attachments are required). Entries in that table are periodically scanned by the email distributor process (SysEmailDistributor), and will correctly handle errors/retries etc. See second code sample further down the post.

static public void sendMail(
    str         fromAddress,        // NB single address only
    str         toAddress,          // " "
    str         ccAddress,          // " "
    str         subject,
    str         body,
    container   attachments = conNull())

    // Send mail via SMTP
    // Attachments is a container of format:
    // [ Source file 1, Attachment name 1, Source file 2, Attachment name 2, ... ]

    SysEmailParameters                          emailParams = SysEmailParameters::find();

    System.Net.Mail.MailMessage                 message;
    System.Net.Mail.Attachment                  attachment;
    System.Net.Mail.AttachmentCollection        attachementCollection;
    System.Net.Mail.SmtpClient                  mailClient;
    System.Net.Mail.MailAddressCollection       addressCollection;
    str                                         mailServer;
    int                                         mailServerPort;
    str                                         attachmentFilename;
    str                                         attachmentName;
    int                                         idx;

    System.Exception                            clrException;
    InteropPermission                           perm;

        throw error("From address has not been specified");
        throw error("To address has not been specified");

        perm = new InteropPermission(InteropKind::ClrInterop);

        mailServer      = emailParams.SMTPRelayServerName;
        mailServerPort  = emailParams.SMTPPortNumber;

            mailServerPort = 25;    // default SMTP port

        message = new System.Net.Mail.MailMessage(
            new System.Net.Mail.MailAddress(fromAddress,''),
            new System.Net.Mail.MailAddress(toAddress,''));

        addressCollection = message.get_CC();


        attachementCollection = message.get_Attachments();

        if((conlen(attachments) mod 2) != 0)
            throw error(error::wrongUseOfFunction(funcName()));

        for(idx = 1;idx <= conLen(attachments);idx += 2)
            attachmentFilename  = conPeek(attachments,idx);
            attachmentName      = conpeek(attachments,idx + 1);

            attachment = new System.Net.Mail.Attachment(attachmentFilename);

        mailClient = new System.Net.Mail.SmtpClient(mailServer,mailServerPort);

        clrException = CLRInterop::getLastException();
        if(clrException.get_InnerException() != null)
            clrException = clrException.get_InnerException();
        throw Exception::Error;


And to test it out (assuming the method has been added to a new class called MailHelper):
  'You have just won an email.');

A note for developers - A handy tool for testing mailers or email-related processes can be downloaded at http://smtp4dev.codeplex.com/. It allows you to run a light-weight SMTP server locally for development and testing, and is definitely worth a look.

The following function can be used to send mail via inserting into the outgoing mail queue, utilizing the template functionality in Ax. This is a slightly modified/cleaned-up version of the method sendMail in table SysEmailTable. If specific mappings are required, pass a map<string,string> that contains token names and values. These can then be referenced in the template using the format %TOKENNAME%.

server static void insertMailToQueue(
    SysEmailId      _emailId,
    LanguageId      _language,
    str             _emailAddr,
    Map             _mappings   = null,
    container       attachments = conNull())
    // Adapted from SysEmailTable::sendMail

    LanguageId              languageId;

    SysEmailTable           table = SysEmailTable::find(_emailId);
    SysEmailMessageTable    message;

    str                     messageBody;
    container               data;
    container               embeddedBinaryData;

    Filename                filePath;
    Filename                filename;
    Filename                fileExtension;

    List                    list;
    ListEnumerator          enumerator;
    Filename                htmlDecodedFileName;

    int                     i = 1;
    int                     maxAttachmentSize;

    BinData                 binData;

    SysOutgoingEmailTable   outgoingEmailTable;
    SysOutgoingEmailData    outgoingEmailData;

    SysEmailBatch           batch = SysEmailBatch::construct();
    FileIOPermission        fileIOPermission;

    SysEmailParameters      emailParams = SysEmailParameters::find();
    str                     attachFilename;
    str                     attachAttachmentName;
    int                     attachmentIDx;


    maxAttachmentSize = emailParams.MaxEmailAttachmentSize;

    //maxAttachmentSize in megabytes
    if (maxAttachmentSize < 1)
        maxAttachmentSize = #maxAttachmentSizeDefault;

    if (_language)
        languageId = _language;
        languageId = table.DefaultLanguage;

    message = SysEmailMessageTable::find(_emailId, languageId);

    if (!message)
        message = SysEmailMessageTable::find(_emailId, table.DefaultLanguage);

    if (!message)
        throw error(strFmt("@SYS74260", _language));

    if (message.LayoutType == SysEmailLayoutType::StaticLayout)
        messageBody = message.Mail;
        throw error(strFmt("Layout type '%1' in email %2 is not supported by this function",

    messageBody = SysLabel::resolveLabels(messageBody, languageId);


    outgoingEmailTable.EmailItemId      = EventInbox::nextEventId();
    outgoingEmailTable.TemplateId       = table.EmailId;
    outgoingEmailTable.IsSystemEmail    = NoYes::No;
    outgoingEmailTable.Sender           = table.SenderAddr;
    outgoingEmailTable.SenderName       = table.SenderName;
    outgoingEmailTable.Recipient        = _emailAddr;

    //note: first do the xml transform if needed, params are substitued after that
    messageBody = SysEmailMessage::stringExpand(messageBody, SysEmailTable::htmlEncodeParameters(_mappings));
    messageBody = strReplace(messageBody, '\n', '<br>');
    [outgoingEmailTable.Message, data] = SysEmailTable::embedImages(messageBody);

    list        = List::create(data);
    enumerator  = list.getEnumerator();
    while (enumerator.moveNext())
        htmlDecodedFileName = SysEmailTable::htmlDecode(enumerator.current());

        fileIOPermission = new FileIOPermission(htmlDecodedFileName,'r');
        //BP Deviation Documented
        if (WinAPIServer::fileExists(htmlDecodedFileName) &&
            //BP Deviation Documented
            (WinAPIServer::fileSize( htmlDecodedFileName) < (maxAttachmentSize * 1000000)) &&
            binData = new BinData();
            //BP Deviation Documented
            embeddedBinaryData = binData.getData();

            outgoingEmailData.EmailItemId       = outgoingEmailTable.EmailItemId;
            outgoingEmailData.DataId            = i;
            outgoingEmailData.EmailDataType     = SysEmailDataType::Embedded;
            outgoingEmailData.Data              = embeddedBinaryData;
            [filePath, filename, fileExtension] = Global::fileNameSplit(htmlDecodedFileName);

            outgoingEmailData.FileName          = int642str(outgoingEmailTable.EmailItemId) + '_' + int2str(i);
            outgoingEmailData.FileExtension     = fileExtension;




    outgoingEmailTable.Subject = SysEmailMessage::stringExpand(message.Subject, _mappings);

    outgoingEmailTable.Priority         = table.Priority;
    outgoingEmailTable.WithRetries      = false;
    outgoingEmailTable.RetryNum         = 0;
    outgoingEmailTable.UserId           = curUserId();
    outgoingEmailTable.Status           = SysEmailStatus::Unsent;

    for(attachmentIDx = 1;attachmentIDx <= conLen(attachments);attachmentIDx += 2)
        attachFilename          = conPeek(attachments,attachmentIDx);
        attachAttachmentName    = conPeek(attachments,attachmentIDx + 1);

        fileIOPermission = new FileIOPermission(attachFilename,'r');

        if (attachFilename && WinAPIServer::fileExists(attachFilename))
            if (WinAPIServer::fileSize(attachFilename) < (maxAttachmentSize * 1000000))
                binData = new BinData();
                embeddedBinaryData = binData.getData();

                outgoingEmailData.EmailItemId       = outgoingEmailTable.EmailItemId;
                outgoingEmailData.DataId            = i;
                outgoingEmailData.EmailDataType     = SysEmailDataType::Attachment;
                outgoingEmailData.Data              = embeddedBinaryData;
                [filePath, filename, fileExtension] = Global::fileNameSplit(attachFilename);
                outgoingEmailData.FileName          = attachAttachmentName;
                outgoingEmailData.FileExtension     = fileExtension;



    outgoingEmailTable.LatestStatusChangeDateTime = DateTimeUtil::getSystemDateTime();


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();
    // 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).
    // 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();    
        // Get the query and update the datasource as required
        query = mapEnum.currentValue();   
        range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(VendTable)),fieldNum(VendTable,AccountNum));
    // Run the report

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");        
    info("Process has been completed");

private void doPost(str someParameter)
    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();
        // Recurse sub-menus
        childNode = node.AOTfirstChild();   
                childNode = childNode.AOTnextSibling();            
            // Possible menu reference
            childNode = TreeNode::findNode('\\Menus\\' + node.AOTname());   
        line = conDel(line,conLen(line),1);
    filename    = WinAPI::getTempFilename(winAPI::getTempPath(),'') + '.csv';
    outFile     = new CommaIo(filename,'w');
    outFile = null; 

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)
            case EcoResProductSubtype::ProductMaster:
                info(strFmt("Product master %1",product.DisplayProductNumber));
                while select productVariant
                    where   productVariant.ProductMaster    == product.RecId                        
                    info(strFmt(" - Variant %1",productVariant.DisplayProductNumber));
            case EcoResProductSubtype::Product:                
                info(strFmt("Product %1",product.DisplayProductNumber));                

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",
    // 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();
            queryRun = new queryRun(query);            
            common = queryRun.getNo(1);           
            info(strFmt(" - %1 = %2",

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",
                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.