Integrate Ecommerce Shopping Cart and Dynamics AX 2012 - Categories - Part 3

Introduction 

The purpose of the following articles is to review some of these new additions which Microsoft has introduced to the developer community and how they can be applied to older versions of Dynamics.

In this artcle we will harness Microsoft Power Automate along with the DataGateway which we have set up to access this data. This is something we have completed in part two of these articles. This article builds on the previous ones and we are going to use the previous setup to start sending category information to our Ecommerce store. Lets get going.

Retrieving the Channel Information and Sending Categories to Ecommerce Store

Now we have our setup completed in parts one and parts two we not need to move onto what we need in our Ecommerce store to make it functional.

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 synchronise data from the Dynamics AX Channel data to the Ecommerce Data for the following data entities.

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

To sync the data, I will do the following for each data entity 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

I have called them data entities as they will consist of information from a number of different tables. Not just a simple select.

Let’s start with the Category Information …

Retail Channel Category Information

The information I’m interested on in Retail Categories is in three different tables. I was going to create a stored procedure to pull the information but when you start working in Microsoft Flow tables and views work much better and are easier to manipulate.

RetailContosoStore Database Setup

I start by creating a view [SYNC_EcommerceCategories] in the RetailConstoStore database. This view will select all the information I need to populate our category information on the Ecommerce database.

CREATE VIEW [dbo].[SYNC_EcommerceCategories] AS SELECT    HRC.NAME AS CHANNELNAME              , ERC.RECID              , ERC.CODE              , ERC.NAME              , ERC.PARENTCATEGORY              , ERC.CREATEDDATETIME              , ERC.MODIFIEDDATETIME              , CTR.SEARCHTEXT              , CTR.FRIENDLYNAME              , CTR.DESCRIPTION FROM   ax.ECORESCATEGORYHIERARCHY AS HRC              INNER JOIN ax.ECORESCATEGORY AS ERC              ON ERC.CATEGORYHIERARCHY = HRC.RECID              LEFT OUTER JOIN ax.ECORESCATEGORYTRANSLATION AS CTR               ON CTR.CATEGORY = ERC.RECID

 

We are interested in all the categories for the “Consoto Online Hierarchy”, so filtering the view all follows to see the categories which we will be pulling down from the Retail Channel. Here is an example of selecting from the view and specifying this criteria.

Ecommerce Database Setup

We have created a view in the Channel database to retrieve the category information. Now we need a place to store this information on the Ecommerce side. I create a Category table called SYNCCategoryData to hold this information. To make the transfer of data super simple its columns and column type map directly to the view in the channel database.

CREATE TABLE [dbo].[SYNCCategoryData](        [CHANNELNAME] [nvarchar](128) NOT NULL,        [RECID] [bigint] NOT NULL,        [CODE] [nvarchar](20) NOT NULL,        [NAME] [nvarchar](254) NOT NULL,        [PARENTCATEGORY] [bigint] NOT NULL,        [CREATEDDATETIME] [datetime] NOT NULL,        [MODIFIEDDATETIME] [datetime] NOT NULL,        [SEARCHTEXT] [nvarchar](254) NOT NULL,        [FRIENDLYNAME] [nvarchar](254) NOT NULL,        [DESCRIPTION] [nvarchar](1000) NOT NULL ) ON [PRIMARY] GO

Next I would like to alter the existing category table by adding a RecId to it. This allows me to link the records between the two databases. It also allows me to check for any new categories in the Channel which don't exist on the ecommerce side.

ALTER TABLE CATEGORY ADD RecID BigINT

My final script is the most important one. I will create a stored procedure to insert from the SYNCCategoryData into the NopCommerce table if the category does not exist. Most of the columns in the table I have populated with the default values. There are three separate scripts in the stored procedure

CREATE PROCEDURE [dbo].[SYNCCategoryUpdate]  AS INSERT INTO category             (NAME,              description,              categorytemplateid,              metakeywords,              metadescription,              metatitle,              parentcategoryid,              pictureid,              pagesize,              allowcustomerstoselectpagesize,              pagesizeoptions,              priceranges,              showonhomepage,              includeintopmenu,              subjecttoacl,              limitedtostores,              published,              deleted,              displayorder,              createdonutc,              updatedonutc,              recid) SELECT SYNC.NAME,        SYNC.description,        1,        searchtext,        SYNC.description,        SYNC.NAME,        0,        0,        6,        1,        '6, 3, 9',        NULL,        0,        1,        0,        0,        1,        0,        0,        Getdate(),        Getdate(),        SYNC.recid FROM   Synccategorydata SYNC        LEFT JOIN category CAT               ON CAT.recid = SYNC.recid WHERE  CAT.recid IS NULL        AND parentcategory <> 0

 

The last piece of SQL updates records which are there from previous sync runs and laos figures out the category hierarchy , so if you have multi-level categories they will be assigned the correct parent id.

 

UPDATE CAT   Set CAT.ParentCategoryID = ISNULL((Select Id from Category Where RecId = SYNC.ParentCategory),0) , CAT.Name = SYNC.NAME , CAT.Description = SYNC.DESCRIPTION FROM SYNCCategoryData SYNC  JOIN CATEGORY CAT ON CAT.REcID = SYNC.RECID

 

Finally the last script makes use of that URL procedure we created previously to create URL Records for the categories

/* Create URL For the Category */          INSERT INTO [UrlRecord]          (EntityId, EntityName, Slug, IsActive, LanguageId)          SELECT id,'Category',dbo.fnCreateUrlSlug (id,Name,'Category'),1,0 from Category          WHERE ID NOT IN (SELECT EntityId FROM URLRecord WHERE EntityName ='Category')

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

Getting into Power Automate or Flow for Categories

Now we have our setup and our scripts completed lets get into Power Automate. For this I will create a new Flow called Integrate 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. Call the SYNCCategoryDelete (Ecommerce database) stored procedure to clear down my Categories
3. Call the view SYNC_EcommerceCategories (Contoso Channel) to get a list of all my retail categories
4. Loop through the rows in the view and insert into the SYNCCategory table (Ecommerce side)
5. Call the SYNCCategoyData stored procedure (Ecommerce side) to sync categories to the NopCommerce side

 

 

1. Set up a recurrence step.This is simple step, just set this job to run once a day

2. Call the SYNCCategoryDelete (Ecommerce database) stored procedure. Add a execute stored procedure step and populate it with the following parameters

3. Call the view SYNC_EcommerceCategories (Contoso Channel) to get a list of all my retail categories
4. 
5. Loop through the rows in the view and insert into the SYNCCategory table (Ecommerce side) 
6. Call the SYNCCategoyData 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 40 seconds and did the following.

1. Deleted the 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 categories.

Viewing the Final Result

All going well I’m going to run the Ecommerce store again and see what Is different. Once the shopping cart is up and running I can see that there is now categories in the previously empty stores.

I can also see that the sub categories have been populates for the main categories.

Next article we will review how the products can be synced.