Copy, Set, Delete, Get Product Categories and Attributes in AX 2012 X++

Hi there,

I hope you had a great week and that you are ready for a great and restful weekend. In this post I would like to spotlight the work of one of my customers. His name is Colin Mitchell and he is a senior solutions architect for TURCK, a world leader cable manufacturer.

Colin and I worked on a very challenging project together for about 5 months. Colin created a super smart application that would allow TURCK sales and product managers to quickly configure a cable specification in real time. If we think about it, engineering approval processes are the key to a quality product and eventually happy customers. 

However, why do we need to involve “human” interaction if a new product could be analyzed automatically based on certain quality rules? Well, Colin and his team created a software solution that would decide, in real time, if the product that was being created needed to go through a required approval process or not.

So, what was the challenge? The challenge was implementing the same “intelligence” into Microsoft Dynamics AX 2012. For this, TURCK purchased ERPSolutions’ Total Engineering Change Management. 

My role was to modify the ERPSolutions Total Engineering Change Management software by implementing TURCK’s vision into it.

So, what was the outcome? It was a very successful project. And it was successful not only because capable people were working on it, but because Colin was involved each step of the way. On this thought, Colin created a solution to copy, set, delete and get Microsoft Dynamics AX product categories and attributes from one product to another. This might sound easy, but believe me is not. Colin and I spent countless hours working on his vision, but he was the one who came up with the final and working solution.

I need to add that despite Microsoft willingness to find a solution for us, they couldn’t.  This post is about sharing what he created. I got his permission to do so and I thought it would be a great idea and great benefit for all us to get this knowledge and high level of analysis.

The Process

Colin when about creating a table relationship diagram (depicted below) with how product categories and attributes are related to a product. 

Then, Colin wrote a class to achieve the following:

• Copy product attributes and categories
• Delete product attributes
• Get product attributes
• Set product attributes

The code sample is extensive and self-explanatory as Colin included really good comments in each step. 

Note: The following code is to be used at your own risk.

Just as a final note, I would like to thank Colin Mitchel for allowing me to share his work in my blog. In addition, I would like to point out that, successful software implementations still exist, and they are successful because of the customer willingness to learn, share, understand and succeed. TURCK was one of those customers, and Colin is a clear example of discipline, intelligence and willingness to go beyond his comfort zone and master a new language. He has really become an amazing X++ master.   
 
You can contact Colin in his Linked In profile here.

Code: 

        #//© 2013 TURCK, Inc.  All Rights Reserved.
        #/// <summary>
        #///    copies all category hierarchies, categories, attributes and attribute values from one product to another
       
        #server static void copyAttributesToProduct(itemID _sourceItem, itemID _destinationItem)
        #{
        #
        #   RefRecId  sourceProductRecId, destinationProductRecId,      productInstanceRecId_AfterInsert, ecoResTextValueRecId_AfterInsert;
        #
        #   EcoResProduct                      sourceProduct      = EcoResProduct::find(InventTable::find(_sourceItem).Product);
        #   EcoResProduct                      destinationProduct = EcoResProduct::find(InventTable::find(_destinationItem).Product);
        #
        #   EcoResProductCategory              ecoResProductCategory, ecoResProductCategory_ForInsert;
        #   EcoResCategory                     ecoResCategory;
        #   EcoResCategoryAttributeLookup      ecoResCategoryAttributeLookup;
        #
        #   EcoResAttribute                    ecoResAttribute;
        #   EcoResAttributeValue               ecoResAttributeValue, ecoResAttributeValue_ForInsert;
        #   EcoResProductInstanceValue         ecoResProductInstanceValue, ecoResProductInstanceValue_ForInsert;
        #   EcoResTextValue                    ecoResTextValue, ecoResTextValue_ForInsert;
        #
        #   if (sourceProduct && destinationProduct)
        #   {
        #
        #    // our source and destination products
        #    // both exist.
        #
        #    sourceProductRecId = sourceProduct.RecId;
        #    destinationProductRecId = destinationProduct.RecId;
        #
        #    if (sourceProductRecId != destinationProductRecId)
        #    {
        #
        #     // we know that the source and destination products
        #     // aren't the same product.
        #
        #     // the purpose of this code is to copy categories,
        #     // attributes and attribute value from one product to another,
        #     // so we have to delete any existing objects from our
        #     // destination product first. Let's go ahead and do that...
        #
        #     // note that we may use the delete_from statement here, too.
        #     // i used delete() for testing, to look at each value as
        #     // it's being deleted.
        #
        #     // first delete all existing categories, attribute, and values
        #     // if they exist
        #
        #     ProductAttributesManager::deleteCategoriesAndAttributeValues(destinationProductRecId);
        #
        #     // now we need to add our destination product to the ecoResProductInstanceValue
        #     // table. The important thing to know here is that the Product field is indexed
        #     // and doesn't allow duplicates -- that is to say, we add our product ONCE.
        #
        #     // note that ecoResProductInstanceValue extends the ecoResInstanceValue table
        #     // but no worries, we can write all of our values at once. The only trick
        #     // is that the "InstanceRelationType" field is a system field, which means
        #     // we can't call it normally. We need to use the overwriteSystemfields variable
        #     // and incorporate the fieldNum function, passing the ID of the EcoResProductInstanceValue
        #     // table to the field. When we're finished, we turn the overwriteSystemfields off again.
        #
        #     ttsBegin;
        #      select forUpdate ecoResProductInstanceValue_ForInsert;
        #       ecoResProductInstanceValue_ForInsert.Product = destinationProductRecId;
        #       new OverwriteSystemFieldsPermission().assert();
        #       ecoResProductInstanceValue_ForInsert.overwriteSystemfields(true);
        #       ecoResProductInstanceValue_ForInsert.(fieldNum(EcoResProductInstanceValue, InstanceRelationType)) = tableName2id("EcoResProductInstanceValue");
        #       ecoResProductInstanceValue_ForInsert.insert();
        #       ecoResProductInstanceValue_ForInsert.overwriteSystemfields(false);
        #       CodeAccessPermission::revertAssert();
        #     ttsCommit;
        #
        #     if (ecoResProductInstanceValue_ForInsert)
        #     {
        #      // IMPORTANT!
        #
        #      // we've now written our destination product into the ecoResProductInstanceValue table.
        #      // in doing so, we've created an important value which we need to keep
        #      // track of - the value of the RecID field for the new value we've written into
        #      // the ecoResProductInstanceValue table. This is the "InstanceValue" which we'll
        #      // write to the EcoResAttributeValue table. Let's put this value in a variable.
        #
        #      productInstanceRecId_AfterInsert = ecoResProductInstanceValue_ForInsert.RecId;
        #
        #      // the next step is to copy our source product's product categories over to our
        #      // destination product. we do that by writing into the ecoResProductCategory table.
        #
        #      // NOTE that products in AX 2012 can be associated with many category hierarchies,
        #      // so to make sure we copy of all them, we need to use a WHILE loop, which will find each
        #      // category and allow us to interact with them.
        #
        #      // we now need to move into discovering the relationships between categories and
        #      // and attributes. the relationship between products and categories is stored in the
        #      // ecoResProductCategory table. while it has a series of relationships
        #      // to other tables (for example, categories and hierarchies), the one we care about
        #      // is the relationship between the product and the category. additional information
        #      // about the category is located in the ecoResCategory table, so let's join
        #      // them as part of our WHILE loop.
        #
        #      while
        #       select ecoResProductCategory where ecoResProductCategory.Product == sourceProductRecId   // <--- we are asking for the data from our SOURCE product
        #       join ecoResCategory where ecoResCategory.RecId == ecoResProductCategory.Category
        #      {
        #       // now, while we're looping, write each product category from
        #       // our source product  to our destination product. The ecoResProductCategory
        #       // table is indexed on the Product and Category fields, allowing
        #       // us to have multiple categories for each product.
        #
        #       ttsBegin;
        #        select forUpdate ecoResProductCategory_ForInsert;
        #         ecoResProductCategory_ForInsert.Product           = destinationProductRecId;                    // <--- use the RecID of our DESTINATION product
        #         ecoResProductCategory_ForInsert.Category          = ecoResProductCategory.Category;
        #         ecoResProductCategory_ForInsert.CategoryHierarchy = ecoResProductCategory.CategoryHierarchy;
        #         ecoResProductCategory_ForInsert.insert();
        #       ttsCommit;
        #
        #       if (ecoResProductCategory_ForInsert)
        #       {
        #        // we've "copied" the product category information for category X
        #        // from our source product to our destination product. the actual
        #        // structure of the hierarchy isn't something we need to write, but
        #        // in order to write attribute values we need to know more about
        #        // categories and their relationship to the attributes.
        #
        #        // that relationship is maintained in a series of three tables:
        #
        #        //  ecoResCategory                  ->    ecoResCategoryAttributeLookup
        #        //  ecoResCategoryAttributeLookup   ->    ecoResAttribute
        #
        #        // we join these tables together while we're looping through
        #        // each category and create a second loop inside our
        #        // main "category" loop...
        #
        #        while
        #         select ecoResCategoryAttributeLookup where ecoResCategoryAttributeLookup.Category == ecoResCategory.RecId   // <--- ecoResCategory.RecId comes from our main "category" loop
        #         join ecoResAttribute where ecoResAttribute.RecId == ecoResCategoryAttributeLookup.Attribute
        #        {
        #
        #         // we're looping through our second WHILE statement, which will give us every attribute
        #         // for every category for every category hierarchy. now, in reality, AX allows only
        #         // one procurement category - where attributes are defined - so in daily use we will
        #         // be dealing with multiple category hierarchies, categorys, but only one of them
        #         // (the procurement category) will have attributes.
        #
        #         // example: if the product has two category hierarchies, with two categories within
        #         // each category hierarchy, and one attribute within each category,
        #         // the result would be:
        #
        #         // product      categoryhierarchy 1        category 1          attribute 1
        #         // product      categoryhierarchy 1        category 1          attribute 2
        #         // product      categoryhierarchy 1        category 2          attribute 1
        #         // product      categoryhierarchy 1        category 2          attribute 2
        #         // product      categoryhierarchy 2        category 1
        #         // product      categoryhierarchy 2        category 1
        #         // product      categoryhierarchy 2        category 2
        #         // product      categoryhierarchy 2        category 2
        #
        #         // so, at this point we know everything except the attribute values.
        #         // to get that, we need to check the ecoResAttributeValue table. That
        #         // table has three fields:
        #
        #         // Attribute       -- join to the ecoResAttribute.RecID field
        #         // InstanceValue   -- join to the ecoResProductInstanceValue.RecID field where the ecoResProductInstanceValue.Product field is our source product's RecID
        #         // Value           -- join to the ecoResTextValue.RecID field, but this is not a field value we're going to copy (see below)
        #
        #         select ecoResAttributeValue where ecoResAttributeValue.Attribute == ecoResAttribute.RecId
        #          join ecoResProductInstanceValue where ecoResProductInstanceValue.RecId == ecoResAttributeValue.InstanceValue && ecoResProductInstanceValue.Product == sourceProductRecId
        #          join ecoResTextValue where ecoResTextValue.RecId == ecoResAttributeValue.Value;
        #
        #         // the join to the ecoResTextValue table gives us access to the TextValue
        #         // field in that table. We're going to need that.
        #
        #         // So. now we need to copy the attribute values from our source product to our
        #         // destination product. to do that, we do perform the following operations
        #         // in the order listed:
        #
        #         // 1. write a new record into the EcoResTextValue table, and save the resulting RecID into a variable
        #         // 2. write a new record into the EcoResAttributeValue table using the EcoResTextValue.RecID variable as the Value field
        #
        #         // we have the same system field situation that we had above in the ecoResProductInstanceValue
        #         // table, but fortunately we know how to handle it below.
        #
        #         if (ecoResAttributeValue.Attribute > 0)   // <--- make sure that we have a valid attribute
        #         {
        #          ttsBegin;
        #           select forUpdate ecoResTextValue_ForInsert;
        #            ecoResTextValue_ForInsert.TextValue = ecoResTextValue.TextValue;
        #            new OverwriteSystemFieldsPermission().assert();
        #            ecoResTextValue_ForInsert.overwriteSystemfields(true);
        #            ecoResTextValue_ForInsert.(fieldNum(EcoResTextValue, InstanceRelationType)) = tableName2id("EcoResTextValue");
        #            ecoResTextValue_ForInsert.insert();
        #            ecoResTextValue_ForInsert.overwriteSystemfields(false);
        #            CodeAccessPermission::revertAssert();
        #          ttsCommit;
        #         }
        #
        #         if (ecoResTextValue_ForInsert)
        #         {
        #          ecoResTextValueRecId_AfterInsert = ecoResTextValue_ForInsert.RecId;   // <--- REALLY important value here! The returned RecID from our write to ecoResTextValue
        #
        #          // our last step is to wrap up all these values and
        #          // write them to the ecoResAttributeValue table.
        #
        #          ttsBegin;
        #           select forUpdate ecoResAttributeValue_ForInsert;
        #            ecoResAttributeValue_ForInsert.Attribute     = ecoResAttributeValue.Attribute;          // <--- the RecID of the attribute we're dealing with in the loop
        #            ecoResAttributeValue_ForInsert.InstanceValue = productInstanceRecId_AfterInsert;        // <--- the resulting RecID from writing into ecoResProductInstanceValue
        #            ecoResAttributeValue_ForInsert.Value         = ecoResTextValueRecId_AfterInsert;        // <--- the resulting RecID from writing into ecoResTextValue
        #            ecoResAttributeValue_ForInsert.insert();
        #          ttsCommit;
        #
        #          if (!ecoResAttributeValue_ForInsert)
        #          {
        #           throw error('An exception was raised - could not write attribute data to table ecoResAttributeValue. (ProductAttributesCopy/copyAttributesToProduct)');
        #          }
        #         }
        #         else
        #         {
        #          throw error('An exception was raised - could not write destination product attribute text to table ecoResTextValue. (ProductAttributesCopy/copyAttributesToProduct)');
        #         }
        #
        #        } // while
        #
        #       }
        #       else
        #       {
        #        throw error('An exception was raised - could not write destination product category to table ecoResProductCategory. (ProductAttributesCopy/copyAttributesToProduct)');
        #       }
        #
        #      } // while
        #
        #     }
        #     else
        #     {
        #      throw error('An exception was raised - could not write destination product to table EcoResProductInstanceValue. (ProductAttributesCopy/copyAttributesToProduct)');
        #     }
        #
        #    }
        #    else
        #    {
        #     throw error('An exception was raised - the source and destination products are the same. (ProductAttributesCopy/copyAttributesToProduct)');
        #    }
        #   }
        #   else
        #   {
        #    throw error('An exception was raised - the source or destination product does not exist. (ProductAttributesCopy/copyAttributesToProduct)');
        #   }
        #
        #}
     

        #//© 2013 TURCK, Inc.  All Rights Reserved.
        #/// <summary>
        #///  deletes all attribute values from a product. The attributes themselves remain, but the values are cleared
      
        #static void deleteAttributeValues(RefRecId _productRecID)
        #{
        #
        #    EcoResProductInstanceValue    ecoResProductInstanceValue;
        #
        #    ttsBegin;
        #
        #     while select forUpdate ecoResProductInstanceValue where ecoResProductInstanceValue.Product == _productRecID && _productRecID > 0
        #     {
        #      // ecoResProductInstanceValue is not a source of delete actions, but it extends table
        #      // EcoResInstanceValue which DOES have a cascade delete action on EcoResAttributeValue.
        #
        #      // So, we will delete our product out of table ecoResProductInstanceValue (if it exists),
        #      // and our attribute values will be deleted out of EcoResAttributeValue, too. Further,
        #      // EcoResAttributeValue is the source of a cascade delete on table EcoResValue, which is
        #      // extended by table EcoResTextValue. EcoResTextValue is the source of yet another
        #      // cascade delete action on table EcoResTextValueTranslation. So to sum up, when we
        #      // delete our product from table ecoResProductInstanceValue, it cleans up all attribute
        #      // values out there.
        #
        #       ecoResProductInstanceValue.delete();
        #     }
        #
        #    ttsCommit;
        #
        #}
     

        #//© 2013 TURCK, Inc.  All Rights Reserved.
        #/// <summary>
        #///  deletes the product <-> category relationship from a product. Also delete all attribute values.
        #/// </summary>
      
        #static void deleteCategoriesAndAttributeValues(RefRecId _productRecID)
        #{
        #
        #    EcoResProductCategory    ecoResProductCategory;
        #
        #    //first delete all attribute values so we don't leave any broken data
        #    //hanging out there
        #
        #    ProductAttributesManager::deleteAttributeValues(_productRecID);
        #
        #    ttsBegin;
        #     while select forUpdate ecoResProductCategory where ecoResProductCategory.Product == _productRecID && _productRecID > 0
        #     {
        #      // ecoResProductCategory is not a source of delete actions, which allows us
        #      // to remove categories from products without destroying the
        #      // categories and hierarchies within them. We delete the
        #      // product <-> category relationship.
        #       ecoResProductCategory.delete();
        #     }
        #    ttsCommit;
        #
        #}
   

        #//© 2013 TURCK, Inc.  All Rights Reserved.
        #/// <summary>
        #///  returns the value of the desired item's product attribute
        #/// </summary>
       
        #static AttributeValueText getProductAttributeValue(itemID _itemId, str _attributeName)
        #{
        #
        #   RefRecId                           itemRecID;
        #
        #   EcoResProduct                      product = EcoResProduct::find(InventTable::find(_itemId).Product);
        #
        #   EcoResProductCategory              ecoResProductCategory;
        #   EcoResCategory                     ecoResCategory;
        #   EcoResCategoryAttributeLookup      ecoResCategoryAttributeLookup;
        #
        #   EcoResAttribute                    ecoResAttribute;
        #   EcoResAttributeValue               ecoResAttributeValue;
        #   EcoResProductInstanceValue         ecoResProductInstanceValue;
        #   EcoResTextValue                    ecoResTextValue;
        #
        #   if (product)
        #   {
        #
        #    itemRecID = product.RecId;
        #
        #    while
        #     select ecoResProductCategory where ecoResProductCategory.Product == itemRecID
        #     join ecoResCategory where ecoResCategory.RecId == ecoResProductCategory.Category
        #    {
        #      while
        #       select ecoResCategoryAttributeLookup where ecoResCategoryAttributeLookup.Category == ecoResCategory.RecId
        #       join ecoResAttribute where ecoResAttribute.RecId == ecoResCategoryAttributeLookup.Attribute
        #      {
        #       select ecoResAttributeValue where ecoResAttributeValue.Attribute == ecoResAttribute.RecId
        #        join ecoResProductInstanceValue where ecoResProductInstanceValue.RecId == ecoResAttributeValue.InstanceValue && ecoResProductInstanceValue.Product == itemRecID
        #        join ecoResTextValue where ecoResTextValue.RecId == ecoResAttributeValue.Value;
        #
        #       if (strLwr(strLRTrim(ecoResAttribute.Name)) == strLwr(strLRTrim(_attributeName)))
        #       {
        #        return ecoResTextValue.TextValue;
        #       }
        #
        #      }
        #    }
        #
        #   }
        #   else
        #   {
        #    throw error('An exception was raised - the product does not exist. (ProductAttributesCopy/getProductAttributeValue)');
        #   }
        #
        #   return '';
        #
        #}
     
        #//© 2013 TURCK, Inc.  All Rights Reserved.
        #static void setProductAttributeValue(itemID _itemId, str _attributeName, str 1999 _attributeValue)
        #{
        #
        #   RefRecId                           itemRecID;
        #
        #   EcoResProduct                      product = EcoResProduct::find(InventTable::find(_itemId).Product);
        #
        #   EcoResProductCategory              ecoResProductCategory;
        #   EcoResCategory                     ecoResCategory;
        #   EcoResCategoryAttributeLookup      ecoResCategoryAttributeLookup;
        #
        #   EcoResAttribute                    ecoResAttribute;
        #   EcoResAttributeValue               ecoResAttributeValue;
        #   EcoResProductInstanceValue         ecoResProductInstanceValue;
        #   EcoResTextValue                    ecoResTextValue, ecoResTextValue_ForUpdate, ecoResTextValue_ForValidation;
        #
        #   if (product)
        #   {
        #
        #    itemRecID = product.RecId;
        #
        #    while
        #     select ecoResProductCategory where ecoResProductCategory.Product == itemRecID
        #     join ecoResCategory where ecoResCategory.RecId == ecoResProductCategory.Category
        #    {
        #      while
        #       select ecoResCategoryAttributeLookup where ecoResCategoryAttributeLookup.Category == ecoResCategory.RecId
        #       join ecoResAttribute where ecoResAttribute.RecId == ecoResCategoryAttributeLookup.Attribute
        #      {
        #       select ecoResAttributeValue where ecoResAttributeValue.Attribute == ecoResAttribute.RecId
        #        join ecoResProductInstanceValue where ecoResProductInstanceValue.RecId == ecoResAttributeValue.InstanceValue && ecoResProductInstanceValue.Product == itemRecID
        #        join ecoResTextValue where ecoResTextValue.RecId == ecoResAttributeValue.Value;
        #
        #       if (strLwr(strLRTrim(ecoResAttribute.Name)) == strLwr(strLRTrim(_attributeName)))
        #       {
        #        if (strLwr(strLRTrim(ecoResTextValue.TextValue)) != strLwr(strLRTrim(_attributeValue)))
        #        {
        #
        #         ttsBegin;
        #          while select forUpdate ecoResTextValue_ForUpdate
        #          where ecoResTextValue_ForUpdate.RecId == ecoResTextValue.RecId
        #          {
        #           ecoResTextValue_ForUpdate.TextValue = _attributeValue;
        #           ecoResTextValue_ForUpdate.update();
        #          }
        #         ttsCommit;
        #
        #         select ecoResTextValue_ForValidation where ecoResTextValue_ForValidation.RecId == ecoResTextValue.RecId;
        #
        #         if (ecoResTextValue_ForValidation.TextValue != _attributeValue)
        #          throw error('An exception was raised - could not update the attribute value. (ProductAttributesCopy/setProductAttributeValue)');
        #
        #        }
        #       }
        #
        #      }
        #    }
        #
        #   }
        #   else
        #   {
        #    throw error('An exception was raised - the product does not exist. (ProductAttributesCopy/setProductAttributeValue)');
        #   }
        #
        #}

   

SpotLight: How to use X++ Delegates in Dynamics AX 2012

Hi there,

I hope you are ready for a long and restful weekend. Certainly, I'm as this has been a tough week with lots of new challenges and lots of new learning, which is great. 

On this post I would like to point out a very interesting post about using delegates in Microsoft Dynamics AX 2012. The post was written by Marcos Calderon, who works for Microsoft as their SDE Lead. 

In his post he starts by showing us how to create a delegate in Microsoft Dynamics AX 2012, then he explains what delegates are and why they are used. He also explains what Event Handlers are and their relationship to the AX 2012 AOT. 

Finally, he give us an example on how to add an event handler programmatically in X++, and how to publish the subscriber into the same tier for later use.

From the post:

"...X++ delegates expose the publisher -subscriber pattern where a delegate defines a clear contract in a publisher class. This contract is used when an event occurs where the event can be a change of state, where all interested classes receive notification that the event has occurred."

You can access his post from here.

Well folks, that's all for now and until the next post!

Get Product Attribute Values in AX 2012 - Let's Code!

Hi There,

I hope that your weekend went OK and that you are ready for another interesting article about AX 2012.

Today I wanted to talk a bit about Product Attributes in AX 2012. As we all know by now, with the introduction of AX 2012 things got a little bit more complicated when dealing with Product Categories and Attributes. 

Categories in AX2012 are used to classify products, customers, and other type of data for reporting and analysis. In addition, each category must have a parent , and child elements (you can learn more about it here).

On the other hand, Product Attributes in AX 2012 focus on the details that we want to maintain for certain products. Before you can create a product attribute, you must define an Attribute Type. Now the great thing about product attributes is that we can assign them from different modules of AX 2012, and the attribute will belong to that module only and not other (you can learn more about it here).

When trying to get a Product Attribute Value for a Product in AX 2012, it is important to understand that there are three main tables involved.

 EcoResProductAttributeValue
 EcoResAttribute
 EcoResValue 

The following code will help you get the Product Attribute Value:

public static AttributeValueText getItemIdAttributeValue(RefRecId _product)
{
    EcoResProductAttributeValue ecoResProductAttributeValue;
    AttributeValueText          attributeValueText;
    InventTable                 InventTable;
    EcoResAttribute             ecoResAttribute;
    EcoResValue                 ecoResValue;
    ;


    //We only query a level down the inventtable and just used the InventTable.Product RecId as a reference for ecoResProductAtributeValue

    while select ecoResProductAttributeValue
        where ecoResProductAttributeValue.Product == _product
            join Name from ecoResAttribute
            where ecoResProductAttributeValue.Attribute ==    ecoResAttribute.RecId
                join ecoResValue where ecoResValue.RecId == ecoResProductAttributeValue.Value
    {
        attributeValueText = ecoResValue.value();
    }

    return attributeValueText;
}

If you notice, the only parameter we need in the example above is the Inventory Table Product Reference in order to find the correct value within the
EcoResProductAttributeValue View. 

The alternative can be to use the Inventory Table in the query as well, and it will look like this:


public AttributeValueText getItemIdAttributeValue()
{
   
    EcoResProductAttributeValue ecoResProductAttributeValue;
    AttributeValueText          attributeValueText;
    InventTable                 InventTable;
    EcoResAttribute             ecoResAttribute;
    EcoResValue                 ecoResValue;
    ;

    while select InventTable where InventTable.itemid == this.parmItemId()
        join RecId from ecoResProductAttributeValue
        where ecoResProductAttributeValue.Product == InventTable.Product
            join Name from ecoResAttribute
            where ecoResProductAttributeValue.Attribute == ecoResAttribute.RecId
                join ecoResValue
                where ecoResValue.RecId == ecoResProductAttributeValue.Value
    {
        attributeValueText = ecoResValue.value();
    }

    return attributeValueText;

}

In the code above I'm using an Inventory Table Item Id and referring the selected record in the EcoResProductAttributeValue View. 


Until the next post and have a great week.

Spotlight - ERP Evaluation Checklist

Hi There,

I would like to share a great article about choosing the right ERP solution. 

The article was written by Jen Dorsey and she is a Marketing Manager at Microsoft. In her blog, she presents 10 point evaluation items to take into consideration when choosing and/or evaluating an ERP solution.

You can access her blog from here. 

From the post:

"Choosing the right enterprise resource planning (ERP) solution is a strategic investment for your company, so it's well worth your time to carefully examine your options...


...Business solutions from Microsoft feature a consistent look and feel that your people already know and work with. If they already know and use Microsoft Outlook, Word, and Excel, they will find the Microsoft Dynamics ERP user experience familiar and adopt it faster."






Until the next post!

Perform in-place upgrade to Microsoft Dynamics AX - A Summary in Pictures 2012 R2

Hi There,

I hope everyone is having a good week so far and that you are ready for another topic on AX 2012 R2. I was reading the AX 2012 R2 upgrade guide and it seems like a lot of information to process. Therefore, I thought on creating a summary of the complete AX 2012 R2 upgrade process through pictures and just a few words. As the saying says, a picture express a thousand words. 

You can download the original document here, which is a very useful guide when it comes to upgrade to AX 2012, AX 2012 Feature Pack, and AX 2012 R2.

Moving right along, An upgrade from Microsoft Dynamics AX 2012 or Microsoft Dynamics AX 2012 Feature Pack to Microsoft Dynamics AX 2012 R2 is classified as an in-place upgrade. The great thing about this type of upgrade is that it does not requires source-to-target workflow, which is when we needed to upgrade an AX 4.0 instance to AX 2009 before bringing it to AX 2012, instead the upgrade is done directly into the source AX 2012 system, so look for upgrade XPO in your AX 2012 R2 setup files. 


Create a Test System

Duplicate your existing production system to create the test system. You can accomplish this by copying a virtual machine image, or, alternatively, you can build a new system by using Setup from your legacy Microsoft Dynamics AX version, and then copying over the production database

 
Run the Microsoft Dynamics AX 2012 R2 Setup on the Test System

By running Microsoft Dynamics AX 2012 R2 Setup on the test system, you accomplish the three numbered tasks shown in the following picture.

Back up the Test System Model Store

Create a file backup of the model store. This backup is used during code upgrade of the models in the customer layer. i.e. AxUtil.exe exportstore /file:[full path of file and file name]

Back up the Test System Model Store
 
Create a file backup of the model store. This backup is used during code upgrade of the models in the customer layer. i.e AxUtil.exe exportstore /file:[full path of file and file name]

Create Development System

Duplicate the Microsoft Dynamics AX 2012 R2 test system to create the development system, which is used to upgrade customer models

Upgrade Customer Models

Customer models belong to one of the customer layers. In ascending order, these layers include the ISV, VAR, CUS, and USR layers. Code upgrade is performed by layer, meaning that each customer layer is upgraded separately, starting at the lowest layer and working up through the higher layers.

Upgrade Code on the Development System and Export the Upgraded Models

All tasks on the checklist have to be completed for each layer that contains models that require upgrade.

Import the Model Store Backup to the Development System

On the development system, import the backup of the model store that you made in the procedure “Back up the test system model store.” i.e. AxUtil.exe importstore /file:[full path of file and file name] /idconflict:overwrite

Import Upgraded Models into their Layers on the Development System

Import the upgraded model or models into the appropriate customer layers. Start with models from the lowest layer, then work up through the higher layers.

Repeat Code Upgrade for Each Layer

For each remaining layer that contains customized models, repeat the upgrade procedure, moving from the lowest remaining layer to the highest layer.

Export the Upgraded Model Store

Export the fully upgraded model store that you have created on the development system to a file. This file acts as the source of the upgraded customer models that are used to prepare for data upgrade first on the test system and later on the production system. i.e. AxUtil.exe exportstore /file:[full path of file and file name]

Import the Upgraded Model Store into the Test System

On the test system, import the model store file that you made in the procedure “Export the upgraded model store.” i.e. AxUtil.exe importstore /file:[full path of file and filename] /idconflict:overwrite

Perform Data Upgrade on the Test System

Upgrade Production System

On the production system, enter single-user model. All client users other than the administrator will be disconnected form the Microsoft Dynamics AX system at this point. This action starts the downtime window during which new business transactions cannot be processed.

Upgrade the Core Production System

Upgrade on the production system requires that you run Microsoft Dynamics AX 2012 R2 Setup on each computer in your deployment.

Upgrade AOS and Other Components

Run Setup on the production system to upgrade the core server and client components.

Perform Data Upgrade on the Production System

Complete the tasks on the checklist as described earlier in this how-to in the section “Perform data upgrade on the test system.”

Upgrade Additional Server Components

When data upgrade is completed, you can complete the upgrade of server components.

Upgrade Additional Clients

Run Setup on your additional Microsoft Dynamics AX client systems to upgrade the client software.

I hope this summary can serve you well as a snapshot of the complete AX 2012 R2 in-place upgrade. There is a lot to learn with this new release and I would expect lots of customer wanting to upgrade to R2 after Convergence 2013.


Have a great rest of the week and until the next post.