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.


1 comment: