Part Five Integrate Product Categories Ecommerce and Dynamics AX 2012

Retrieving the Channel Information and Sending Products Categories to Ecommerce Store

Now we have out setup completed we not need to move onto what we need in our Ecommerce store to make it functional. In the last article we synced Category and product information. In this aricle we will link them up.

At a bare minimum we need Category and Product Information. We also need to know what categories a product is part of.  We need to sync information for the following

1. Category Information
2. Product Information
3. Category / Product Information

To sync the data, I will do the following for each set of information I want to sync from one database to another.

1. Create a View in the Channel to retrieve the information
2. Create a table in the Ecommerce database to contain the information
3. A stored procedure to update the Ecommerce table with the Channel information

Let’s move onto the Product Categories Information …

Retail Channel Product Category Information

The information I’m interested on in Retail Product Categories is in very simple I am going to start the process by creating a view in the Contoso Channel Database to select this information.

So lets get to work on the product category information

RetailContosoStore Database Setup

I start by creating a view [SYNC_EcommerceProductsCategories] in the RetailConstoStore database.

CREATE VIEW [dbo].[SYNC_EcommerceProductsCategories] AS        SELECT  DISTINCT        HRC.NAME HierarchyName,        ERC.RECID CategoryRecID,        IVT.RECID ProductRecId        FROM ax.[ECORESCATEGORYHIERARCHY] HRC        JOIN ax.[ECORESCATEGORY] ERC        ON    ERC.CATEGORYHIERARCHY = HRC.RECID        JOIN ax.ECORESCATEGORYTRANSLATION CTR        ON CTR.CATEGORY = ERC.RecId        JOIN ax.[ECORESPRODUCTCATEGORY] EPC        ON EPC.CATEGORYHIERARCHY = ERC.CATEGORYHIERARCHY         AND EPC.CATEGORY = ERC.RECID         JOIN  ax.ECORESPRODUCT PRD        ON PRD.RECID = EPC.PRODUCT        JOIN ECORESPRODUCTTRANSLATION PTR        ON PTR.Product = PRD.RecId        JOIN INVENTTABLE IVT        ON PRD.DISPLAYPRODUCTNUMBER = IVT.ITEMID

 

We are interested in all the products and the categories for the “Consoto Online Hierarchy”, so filtering the view all follows to see the products which we will be pulling down from the Retail Channel.

Not much to see here only RecId from both the Category Table and the ECOResProductTable

Ecommerce Database Setup

We have created a view in the Channel database to retrieve the product category information. Now we need a place to store this product category information on the Ecommerce side. I need to create a product category table called SYNCProductCategoryData to hold this information. Similar to the category and the product tables Its columns and column type map directly to the view in the channel database.

CREATE TABLE [dbo].[SYNCProductCategoryData](        [HierarchyName] [nvarchar](128) NULL,        [CategoryRecID] [bigint] NULL,        [ProductRecId] [bigint] NULL ) ON [PRIMARY] GO

 

I then need to create a stored procedure to insert from the SYNCProductCategoryData into the NopCommerce table if the product does not exist. Most of the columns in the table I have populated with the default values.  

CREATE PROCEDURE [dbo].[SYNCPRODUCTCategoryUpdate] AS          INSERT INTO [dbo].[Product_Category_Mapping]         (ProductId,CategoryId, IsFeaturedProduct, DisplayOrder)        SELECT PRD.ID, CAT.Id, 0 ,0        FROM SYNCProductCategoryData SYNC        JOIN Category CAT        ON CAT.RecID = SYNC.CategoryRECID         JOIN Product PRD        ON PRD.RecID = SYNC.ProductRECID         LEFT JOIN Product_Category_Mapping MAP        ON MAP.ProductID = PRD.ID        AND MAP.CategoryId = CAT.ID        WHERE MAP.ID IS null

One last procedure which needs to be added is to clear the SYNCProductCategoryData table down. This is a simple stored procedure which deletes all the rows from the Sync Table before we populate it with fresh information.

CREATE PROCEDURE [dbo].[SYNCProductCategoryDelete] AS        DELETE FROM SYNCProductCategoryData

Ok that’s all the scripts and tables setup to synchronise over the product category information. Now lets wire all those scripts up in power automate

Setting up Power Automate or Flow for Products Categories

Now we have our setup and our scripts for Products Categories completed let’s get into Power Automate. For this I will create a new Flow called Integrate Product Categories. Then I will add four main steps to it. The finished product is in the picture below.

1. Set up a recurrence step, once a day is good enough for me to keep the data in sync
2.  
3. Call the SYNCProductDelete (Ecommerce database) stored procedure to clear down my Categories
4. 
5. Call the view SYNC_EcommerceProductCategory (Contoso Channel) to get a list of all my retail categories 
6. Loop through the rows in the view and insert into the SYNCProductCategoryData table (Ecommerce side) 
7. Call the SYNCProductCategoryData stored procedure (Ecommerce side) to sync categories to the NopCommerce side

That the flow completed, lets run the jobs and see what the result is. It ran in about 9 minute and did the following.

1. Deleted the rows from SYNC Table in the Ecommerce side
2. Selected all the retails rows from the Contoso Channel database
3. Inserted each row in the SYNC table on the Ecommerce database
4. Ran the script at the end to create and update the Ecommerce product categories.

Viewing the Final Result

All going well I’m going to run the Ecommerce store again and see what Is different. I can see that there are still categories but now entering the categories I can see products in them. Clicking on any of the categories still gives me a list of products.

So now we have products, categories and product categories. In theory we have everything we need for the ecommerce catalogue.

Now I can add items to the shopping cart and check out. In the next section let’s look at a solution to get these products ordered by a customer and into Dynamics AX as a sales order using AIF.