Create, Deploy, Troubleshoot and Consume external services in AX 2012 R2

 

 
Hi There!
 
I hope everyone is having a great week so far. Summer is almost over here in the US, and I feel like I haven't taken much advantage of it this year. The good thing, however, is that I have been able to really focus on service development lately, and a ton of other cool AX stuff.
 
On this post I would like to share with you how to Create, Deploy, Troubleshoot and consume an external service in AX 2012 R2. As we all know, this has changed dramatically from AX 2009 services. It used to be very easy to consume services in AX 2009 (you can see an example in my post Consume a Currency Exchange Rates Web Service from AX 2009 - WFC ).
 
In AX 2012 R2, however, this has become somewhat more involved. They are not necessarily harder to create and consume, but they require a few more steps to be setup. Now, the great advantage is that you can resolve the business logic either in the client itself (C# project) or in AX 2012 R2 (Server deployment). This comes handy for business that don't necessarily want to have an AX developer in house and/or large scale integration projects, among other reasons.
 
 
Let's get to it!
 
Open visual studio and create a new Class Library Project. Give it a name and click OK.
 

 
 
Right click the Project Name references folder and click the Add Service Reference button.
 

 
 
 
Paste the http://www.webservicex.net/genericbarcode.asmx?WSDL URL into the Address bar. This is a Barcode Generator service. Give it a name and click OK.
 
 

 
 
This will create a new Service Reference and a new AppConfig file where both the basic and custom bindings are automatically generated for you.
 
 

 
 
Right click the Project Name and choose Add "Service Name" to AOT. This will add the Csharp Project to the AOT under Visual Studio Projects/Csharp Projects.
 

 
 
 
Once the project has been added to the AOT, you can choose the following properties and deploy the service.
 
 

 
 
 
NOTE: If you choose to deploy to the server, you will need to enable Hot Swapping Assemblies on the server configuration file.  See the following for more info (http://msdn.microsoft.com/en-us/library/gg889279.aspx).  If you choose to do this, you will have to restart the AOS.
 
 

 
 

 
 
 
After it is deployed, you would add a code similar to the one below.
 
 
 static void TestBarcodeGenService(Args _args)
{
    Ed_SampleBarcodeGenerator.EdGenBarcode.BarCodeSoapClient service;
    Ed_SampleBarcodeGenerator.EdGenBarcode.BarCodeData barCodeData;
    System.Exception ex;
    System.Type type;
    ;
    try
    {
        service = new Ed_SampleBarcodeGenerator.EdGenBarcode.BarCodeSoapClient();
        service.GenerateBarCode(barCodeData, "0000992882");
    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(CLRInterop::getAnyTypeForObject(ex.ToString()));
    }
}
 
 
 Well ... that's all for now folks. Stay tuned, there is going to be a huge load of useful information in the next few weeks.
 
 

 
 

Create a Transfer Journal using AX 2012 R2 Document Services and C#

 

 
Hi there,
 
On this post I would like to share some C# code to create a Transfer Journal using C#. I have written a few post in the past about services and they will help you understand how to create a service, service groups, deployment, etc.
 
Create Counting Journals
 
How to choose the right service
 
AX 2012 Services and AIF
 
Services Types
 
Creating a service in AX 2012
 
 
Back to the creation of a Transfer Journal with C#, this is an interesting code as we need to instantiate two different instances of the InventDim Table; InventDimIssue and InventDimReceipt.
 
InventDimIssue can be thought as the From values and InventDimReceipt can be thought as the To values (i.e. From Warehouse ==> To Warehouse).
 
In addition, another interesting point is that AX uses the InventJournalTable and InventJournalTrans for all the inventory journal entries, and we specified, in C#, which entity (AXD) will be using.
 
The following is the code:
 
 
private void InventTransferJourTest()
{
            InventTransferJournal.CallContext callContext = new InventTransferJournal.CallContext();

            InventTransferJournal.TransferJournalServiceClient servClient = new InventTransferJournal.TransferJournalServiceClient();

            InventTransferJournal.AxdTransferJournal transjournal = new InventTransferJournal.AxdTransferJournal();

            InventTransferJournal.AxdEntity_InventJournalTable journalheader = new InventTransferJournal.AxdEntity_InventJournalTable();

            //Header
            callContext.Company = "CEU";
            journalheader.JournalNameId = "TransferJourId";
            journalheader.Description = "Transfer Journal";
            //End header

            //Lines
            InventTransferJournal.AxdEntity_InventJournalTrans journalLines = new InventTransferJournal.AxdEntity_InventJournalTrans();

            journalLines.ItemId = "123456";
            journalLines.Qty = 45;
            journalLines.TransDate = DateTime.Now;

            InventTransferJournal.AxdEntity_InventDimIssue inventDimIssue = new InventTransferJournal.AxdEntity_InventDimIssue();

            inventDimIssue.InventBatchId = "RUT";
            inventDimIssue.InventLocationId = "21";
            inventDimIssue.InventSiteId = "1";

            journalLines.InventDimIssue = new InventTransferJournal.AxdEntity_InventDimIssue[1] { inventDimIssue };

            InventTransferJournal.AxdEntity_InventDimReceipt inventDimReceipt = new InventTransferJournal.AxdEntity_InventDimReceipt();

            inventDimReceipt.InventSiteId = "2";
            inventDimReceipt.InventLocationId = "11";
            inventDimReceipt.InventBatchId = "RSR";

            journalLines.InventDimReceipt = new InventTransferJournal.AxdEntity_InventDimReceipt[1] { inventDimReceipt };
            //End Lines

            journalheader.InventJournalTrans = new InventTransferJournal.AxdEntity_InventJournalTrans[1] { journalLines };

            transjournal.InventJournalTable = new InventTransferJournal.AxdEntity_InventJournalTable[1] {journalheader};

            try
            {
                servClient.create(callContext, transjournal);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.InnerException.ToString());
            }
}
           
 
 That's all for today and stay tuned as in the next few weeks I will be talking about TFS and how to work with AX 2012 in a way that we utilize the TFS server to its max capacity.

Have a great weekend!
 
 

Create Counting Journal in AX 2012 R2 using Document Services

Hi There,

It has been a long time since I created my last post. I have been very busy learning new things about AX 2012 R2 and other related technologies such as the Data Import/Export framework, TFS and AX 2012, SharePoint Development for the Enterprise Portal, among other. Everything will come in its own time and I'm planning in sharing a lot in the weeks to come, so stay tuned!

On this post I would like to share some C# code to create a Counting Journal in AX 2012 R2 using the InventCountingJournalService that ships with AX. Let's keep in mind that the AX 2012 R2 document services are a extremely low cost option of providing this features to an external client with no AX development whatsoever.

So, I would like to start from the beginning:

1- Create a Service Group

2- Add the InventCountingJournalService to the Service Group

3- Deploy the Service Group. This will output the following.

 

 

 

4- Get the WSDL URI from the inbound ports form.

5- Go to Visual Studio, create a new windows form project, add a button and double click the button to create a button event.

6- Right - Click the Service References and choose Add Service Reference.

7 - Past the WSDL URI and click GO

8- Give your service a name i.e. InventCountingJournnal

9 - Write the following code and test.

 private void InventCountingJournal()
 {
            InventCountingJournal.CallContext callContext = new InventCountingJournal.CallContext();

            InventCountingJournal.CountingJournalServiceClient servClient = new  InventCountingJournal.CountingJournalServiceClient();

            InventCountingJournal.AxdCountingJournal countJournal = new InventCountingJournal.AxdCountingJournal();

            InventCountingJournal.AxdEntity_InventJournalTable journalHeader = new InventCountingJournal.AxdEntity_InventJournalTable();

            //Header
            callContext.Company = "CEU";
            journalHeader.JournalNameId = "CountJour";
            journalHeader.Description = "Counting Journal";
            //Header

            //lines
            InventCountingJournal.AxdEntity_InventJournalTrans journalLines = new InventCountingJournal.AxdEntity_InventJournalTrans();

            journalLines.ItemId = "12345";
            journalLines.Qty = 50;
            journalLines.TransDate = DateTime.Now;

            InventCountingJournal.AxdEntity_InventDim inventDim = new InventCountingJournal.AxdEntity_InventDim();

            inventDim.InventBatchId = "3";
            inventDim.InventLocationId = "1";
            inventDim.InventSiteId = "3";

            journalLines.InventDim = new InventCountingJournal.AxdEntity_InventDim[1] { inventDim };

            //Lines

            journalHeader.InventJournalTrans = new InventCountingJournal.AxdEntity_InventJournalTrans[1] { journalLines };

            countJournal.InventJournalTable = new InventCountingJournal.AxdEntity_InventJournalTable[1] { journalHeader };

            servClient.create(callContext, countJournal);
 }


You can test this by clicking the button, and calling this method. A new counting journal would be created in AX. Then, you can either have a batch posting all the journals or simply have a user doing it manually.

That's all for now!

SpotLight - From Power View to Cube



 

Hi there!

Today I would like to share a very interesting post written by Brandon George about the BI Semantic Model and its relationship to Microsoft Dynamics AX R2. 



On his post, he presents a broad overview of the BI Semantic Model and give us a full review of it by providing a set of posts to further our knowledge around extending, creating and deploying the solutions. It is just amazing to see how far Brandon has taken the BI Semantic Model, and I really thank him for sharing it with us.

From the post:

"you should see how simple it really is to empower yourself and your company or clients to truly start embracing the full BI Semantic Model. When Microsoft Dynamics AX is your system of record, and acting as your main data mart, then you have a great BI offering built right into the product."

You can access his post from here.



AX 2012 CIL - How does it work?

Hi There,

On this post I would like to talk about a bit more about the CIL and what happens in the background. Recently I had to go through a process to find a CIL error I got after deploying a service. You can read my post solving the error "The CIL generator found errors and could not save the new assembly" for more information on this.

After digging a bit more on the CIL errors, I started researching on how exactly the NetModule files are generated under the XppIL folder and why. In addition, I also was intrigued by the source files and their relationship to debugging services and or batch jobs in Visual Studio.

Just for the record, I’m not an expert on this, and the following information has been taken from different sources and from my own experience in the past few hours being “exposed” to the services virus (it is kind of addictive).

So, what is CIL? CIL stands for Common Intermediate Language and it works together with the CLI or Common Language Infrastructure, which is basically a set of rules on programming languages that will compile with the CIL. I created a diagram to help me understand this concept on a visual way.

Please note that I took the diagram from a book, but I modified it to my own needs.

As you can see, now we have the ability to compile P-Code to CIL, and therefore AX 20212 is able to run X++ code directly into the CIL, which is much faster the P-Code compiler we had before.

As you probably know, we have two types of CIL compilations, the incremental CIL and the full CIL compilation. The major difference between the two of them is that the incremental CIL would compile only the objects that were modified since the last incremental compilation. For what I have learned, the full CIL generation is mandatory when we do modify anything on the XppIL folder.

On the XppIL folder, I noticed that we have a bunch of files there. These files are NetModule type files and they only contain type metadata and compiled code. It is important not to confuse a NetModule type file with .NET assemblies, as these contain and assembly manifest and managed code.

 

Now the really interesting portion of this is that within the XppIL folder there is a folder named “source”, and within this folder we find a bunch of files with the .xpp extension, which have x++ source code and are used to debug CIL code in Visual Studio when working with services and batches.

Further, another interesting point to this is that the “existence” of the source folder is directly related to our server configuration when choosing to enable debugging on the server.

Until the next post!

Solving the error " The CIL generator found errors and could not save the new assembly"

 

Hi There!

On this post I would like to discuss how to solve the CIL compilation error when deploying services for the first time in AX 2012. I have been working on a newly installed AX 2012 CU2 instance this week, and I deployed both basic and enhanced services.

This action was successfully done and I did not experience any issues. However, when I created a new service group and I deployed it, I encounter an error saying “The CIL generator found errors and could not save the new assembly”.



Now, this instance of AX 2012 is using a restored DB backup from another instance as I needed some data and objects already existing. It makes sense to think that this issue is true from the premise that I never went through a full AX and CIL compilation.

In addition, when generating the services through the installation, the references to the .NET Module File and the AX Application DLL were correct, but where they referring to the correct .NET Assembly types for the WFC generated endpoints?  I truly don’t know the answer to this and I asked for help.

My friend Bill Bartolotto (you can contact him here at his LinkedIn profile) went through this problem and he helped me solved it in my instance.  Bill has a vast experience in AX architecture and is really knowledgeable in AX 2012.

So, how do we fix this problem?

Step 1: Stop the AOS

Atep 2: Delete all of the source in the C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\XppIL directory

When you do this, just delete all the files within the XppIL folder that are outside of other folders. Make a backup just in case, and the files would be generated while the full compilation is taking place.



Step 3: Start the AOS

Step 3: Perform a full compile (it will not work without doing this)



Step 4: Perform a full CIL generation



The drawback of this fix is that it takes a long time to complete. However, this fixes the issue, which is the desired outcome, and the services deployment and incremental CIL compilations moving forward would be error free.

As you can see, the service was deployed correctly and if I opened my inbound port I'll see it there.

UPDATE:

After a FULL CIL compilation I got the following errors:

 

AOT > Class> ReleaseUpdateDB41_Basic

 

 

I just went to the object in question and compiled them separately.



 

 

The outcome would correctly compiled all the artifacts, including my new service gorup.

Until the next post!

How to choose the right service in AX 2012?

Hi there!

I hope you are having a great week. On today’s post I would like to discuss the difference between Document Services and Custom Services in AX 2012. Microsoft made a lot of changes from AX 2009 and AX 2012 and it seems to have created a sort of gray area on when to use a Document Service or a Custom Service.

Both document and custom services can handle any business entity. So, how do we know which one to use?

On one hand, the document services framework handles a lot of complexity out-of-the-box. For example, the framework parses the incoming XML, and validates it against a schema (XSD) document, and then the correct service action is called. This is simple as most of the logic that is needed to create, delete, update, etc. already exists, which simplifies the developer’s job. Now, what about sharing data contracts between applications?

On the other hand, custom services are flexible because they use the .NET XML serializer. The issue with this is that no validation is done, which creates a problem because any validation needs to be written in code by a developer.

However, custom services would allow us to share data contacts between applications, which is a plus in today’s world given the complexity of integration we are experiencing. For example, custom services would allow a company that uses AX 2012, SharePoint and .NET applications to share data contracts, making sure that the entities are the same for all the components of the architecture, and this is huge!



So, to summarize and share my own experience with services, I take into consideration the following rules when trying to decide which service to use:

Use Custom Services When	Use Document Services When
Exposing entities that have low complexity	Exposing entities that have a high complexity.
Sharing data contracts between company-wide applications	Data validation is required.
Creating logic that has nothing to do with the AX 2012 structures.	Inheriting logic to create, delete, update and read data from entities.

It is clear that Microsoft has provided us with a lot of new ways to integrate applications by allowing us to expose the AX 2012 business logic very easily and by providing the framework to using attributes to extend our classes, and also by introducing the WCF architecture, which help us move away a bit from solely X++.

Until the next time!

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)');
        #   }
        #
        #}