Part Four Integrate Ecommerce and Dynamics AX 2012

Wednesday, March 17, 2021

Retrieving the Channel Information and Sending Products 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 information.

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 Information …

Retail Channel Product Information

The information I’m interested on in Retail Product is in many different tables. Similar to the category list 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.

So lets get to work on the product information

RetailContosoStore Database Setup

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

CREATE VIEW SYNC_EcommerceProducts

AS

SELECT  DISTINCT

                               HRC.Name HierarchyName

                            ,  ERC.NAME CategoryName

                            , ERC.RECID CategoryRecID

                            , IVT.RECID ProductRecId

                            , PTR.Name ProductName

                            , PTR.LANGUAGEID

                            , PTR.DESCRIPTION

                            , IVT.ItemID

                            , '' RetailPackDescription

                            , IVTM.UnitId 

                            , IMG.MEDIUMSIZE

                            , (SELECT GlobalTradeItemNumber FROM InventItemGTIN Where ItemID = IVT.ITEMID and UnitId =  'UN') Barcode

                            , 1 UnitsPerCase

                            , 1 CostGroupId

                            , getdate() MODIFIEDDATETIME

                            , getdate() IMGCREATEDDATETIME

                            , IVT.RECID

                            , 0 RRPPrice

                            , 0 ProductStatus

                            , IMG.RECID IMGRECID

                            , IMG.DEFAULTIMAGE                     

       FROM [ECORESCATEGORYHIERARCHY] HRC

       JOIN [ECORESCATEGORY] ERC

       ON    ERC.CATEGORYHIERARCHY = HRC.RECID

       JOIN ECORESCATEGORYTRANSLATION CTR

       ON CTR.CATEGORY = ERC.RecId

       JOIN [ECORESPRODUCTCATEGORY] EPC

       ON EPC.CATEGORYHIERARCHY = ERC.CATEGORYHIERARCHY 

       AND EPC.CATEGORY = ERC.RECID 

       JOIN  dbo.ECORESPRODUCT PRD

       ON PRD.RECID = EPC.PRODUCT

       JOIN ECORESPRODUCTTRANSLATION PTR

       ON PTR.Product = PRD.RecId

       JOIN INVENTTABLE IVT

       ON PRD.DISPLAYPRODUCTNUMBER = IVT.ITEMID

       JOIN INVENTTABLEMODULE IVTM

       ON IVTM.ITEMID = IVT.ITEMID

       LEFT OUTER JOIN ECORESPRODUCTIMAGE IMG

       ON IMG.REFRECORD = IVT.RECID

       Where  IVTM.MODULETYPE = 2

 

We are interested in all the products 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.

I’m also only interested in the language ‘en-us’. So I will filter on this. These are all the products approximately 486 products in the Contoso Hierarchy

Ecommerce Database Setup

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

CREATE TABLE [dbo].[SYNCProductData](

       [HierarchyName] [nvarchar](128) NOT NULL,

       [CategoryName] [nvarchar](254) NOT NULL,

       [CategoryRecID] [bigint] NOT NULL,

       [ProductRecId] [bigint] NOT NULL,

       [ProductName] [nvarchar](60) NOT NULL,

       [LanguageId] [nvarchar](60) NOT NULL,

       [DESCRIPTION] [nvarchar](1000) NOT NULL,

       [ItemID] [nvarchar](20) NOT NULL,

       [RetailPackDescription] [nvarchar](20) NOT NULL,

       [UnitId] [nvarchar](10) NOT NULL,

       [MEDIUMSIZE] [varbinary](max) NULL,

       [Barcode] [nvarchar](14) NULL,

       [UnitsPerCase] [decimal](18, 2) NULL,

       [CostGroupId] [nvarchar](10) NULL,

       [MODIFIEDDATETIME] [datetime] NULL,

       [IMGCREATEDDATETIME] [datetime] NULL,

       [RecId] [bigint] NULL,

       [RRPPrice] [decimal](18, 2) NULL,

       [ProductStatus] [int] NULL,

       [ImgRecId] [bigint] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

 

 

Next I would like to alter the existing product table in NopCommerce by adding a RecId to it. This allows me to link the records between the two databases. The RecId will act as a foreign key into the Sync table.

ALTER TABLE Product ADD RecID BigINT

ALTER TABLE Picture ADD RecID BigINT, ImgRecId BIGINT

 

I then need to create a stored procedure to insert from the SYNCProductData into the NopCommerce table if the product 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].[SYNCPRODUCTUpdate]

AS

 

       /* Three stages to Add Products */

       /* Update Products */

       UPDATE PRD

       SET

               [Name] = replace(SYNC.ProductName ,'+',' and ')

             , [ShortDescription]  = SYNC.DESCRIPTION

             , [FullDescription]  = SYNC.DESCRIPTION + ' ( JB Number:' + ItemID + ')'

             , [OldPrice] = SYNC.RRPPrice

       FROM SYNCProductData  SYNC

       JOIN Product PRD

       ON PRD.RECID = SYNC.PRODUCTRECID

      

       /* Deleted Products */

       UPDATE PRD

       SET

               PRD.Deleted = 1

             , PRD.Published = 1

       FROM Product PRD 

       LEFT JOIN SYNCProductData SYNC

       ON PRD.RECID = SYNC.PRODUCTRECID

       WHERE SYNC.PRODUCTRECID IS NULL

      

       /* Unpublish Products Discontinued Product */

       UPDATE PRD

       SET

               PRD.Published = 1

       FROM Product PRD 

       JOIN SYNCProductData SYNC

       ON PRD.RECID = SYNC.PRODUCTRECID

       WHERE  SYNC.ProductStatus = 5

 

       /* Add New Products */

       INSERT [dbo].[Product]

        ([ProductTypeId], [ParentGroupedProductId], [VisibleIndividually], [Name], [ShortDescription]

       , [FullDescription], [AdminComment], [ProductTemplateId], [VendorId], [ShowOnHomePage]

       , [MetaKeywords], [MetaDescription], [MetaTitle], [AllowCustomerReviews], [ApprovedRatingSum]

       , [NotApprovedRatingSum], [ApprovedTotalReviews], [NotApprovedTotalReviews], [SubjectToAcl], [LimitedToStores]

       , [Sku], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId]

       , [OverriddenGiftCardAmount], [RequireOtherProducts], [RequiredProductIds], [AutomaticallyAddRequiredProducts], [IsDownload]

       , [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId]

       , [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring]

       , [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsRental], [RentalPriceLength]

       , [RentalPricePeriodId], [IsShipEnabled], [IsFreeShipping], [ShipSeparately], [AdditionalShippingCharge]

       , [DeliveryDateId], [IsTaxExempt], [TaxCategoryId], [IsTelecommunicationsOrBroadcastingOrElectronicServices], [ManageInventoryMethodId]

       , [UseMultipleWarehouses], [WarehouseId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity]

       , [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions]

       , [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [AllowAddingOnlyExistingAttributeCombinations], [NotReturnable]

       , [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [PreOrderAvailabilityStartDateTimeUtc], [CallForPrice]

       , [Price], [OldPrice], [ProductCost],  [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [BasepriceEnabled]

       , [BasepriceAmount], [BasepriceUnitId], [BasepriceBaseAmount], [BasepriceBaseUnitId], [MarkAsNew]

       , [MarkAsNewStartDateTimeUtc], [MarkAsNewEndDateTimeUtc], [HasTierPrices], [HasDiscountsApplied], [Weight]

       , [Length], [Width], [Height], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc]

       , [DisplayOrder], [Published], [Deleted], [CreatedOnUtc], [UpdatedOnUtc], [RecId])

       SELECT 5, 0, 1, ProductName, DESCRIPTION

       , DESCRIPTION, ItemID, 1, 0, 0

       , ItemId + ',' + DESCRIPTION, DESCRIPTION, ProductName, 1, 0

       , 0, 0, 0, 0, 0

       , Barcode, ItemID, NULL, 0, 0, NULL, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 0, 1, 0, 1, 0, 0

       , CAST(0.0000 AS Decimal(18, 4)), 0, 0, 4, 0, 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, 0, NULL

       , 0, CAST(120.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL

       , 0, CAST(0.0000 AS Decimal(18, 4))

       , CAST(0.0000 AS Decimal(18, 4)), 1, CAST(0.0000 AS Decimal(18, 4)), 1, 0

       , NULL, NULL, 0, 0, CAST(0.0000 AS Decimal(18, 4))

       , CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL

       , 0, 1, 0, getdate(), getdate(), ProductRecID

       FROM SYNCProductData  SYNC

       LEFT JOIN Product PRD

       ON PRD.RECID = SYNC.PRODUCTRECID

       WHERE PRD.RecID IS NULL

 

       /* Create URL For the Product */

       INSERT INTO [UrlRecord]

       (EntityId, EntityName, Slug, IsActive, LanguageId)

       SELECT id,'Product',dbo.fnCreateUrlSlug (id,Name,'PRODUCT'),1,0 from PRODUCT

       WHERE ID NOT IN (SELECT EntityId FROM URLRecord WHERE EntityName ='Product')

 

       /* Insert Missing Pictures */

       INSERT INTO Picture

       ([PictureBinary],[MimeType],[SeoFilename],[AltAttribute],[TitleAttribute],[IsNew],RecID, ImgRecId )

       SELECT CONVERT(varbinary(max),dbo.ConPeek(MediumSize,1)), 'image/jpeg', dbo.fnCreateUrlSlug(1,ProductName,'Product'),dbo.fnCreateUrlSlug(1,ProductName,'Product')

       ,dbo.fnCreateUrlSlug(1,ProductName,'Product'),0,SYNC.ProductRecId , SYNC.ImgRecId

       FROM SYNCProductData  SYNC

       LEFT JOIN Picture PRD

       ON  PRD.RECID = SYNC.PRODUCTRECID

       AND PRD.ImgRecId = SYNC.ImgRecId

       WHERE PRD.RecID IS NULL

       AND MEDIUMSIZE IS NOT NULL

 

       /* Delete RemovedPictures */

       DELETE PRD

       FROM Picture PRD

       LEFT JOIN SYNCProductData SYNC 

       ON  PRD.RECID = SYNC.PRODUCTRECID

       AND PRD.ImgRecId = SYNC.ImgRecId

       WHERE PRD.RecID IS NOT NULL

       AND SYNC.RecID IS NULL

 

       /* Map Picture to the Product */

       INSERT INTO [dbo].[Product_Picture_Mapping]

        (ProductId,PictureId,  DisplayOrder)

       SELECT PRD.ID, PIC.Id, 0

       FROM  Picture PIC

       JOIN Product PRD

       ON PRD.RecID = PIC.RECID 

       LEFT JOIN Product_Picture_Mapping MAP

       ON MAP.ProductID = PRD.ID

       AND MAP.PictureId = PIC.ID

       WHERE MAP.ID IS null

 

 

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 which needs to be added is to clear the SyncProductData 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].[SYNCPRODUCTDelete]

AS

       DELETE FROM SYNCProductData

 

Setting up Power Automate or Flow for Products

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

  1. Call the SYNCProductDelete (Ecommerce database) stored procedure 

Add a execute stored procedure step and populate it with the following parameters

  1. Call the view SYNC_EcommerceProducts (Contoso Channel) to get a list of all my retail categories 
  2. Loop through the rows in the view and insert into the SYNCProductData table (Ecommerce side) 
  4. Call the SYNCProductData stored procedure (Ecommerce side)

to sync products 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 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 products.

 

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 is still categories but still no products in them. Clicking on any of the categories still gives me an empty list.

That’s because we are missing another step Product Categories, let’s address this in the next article. In the next article we will review how the products categories or products per category can be synced.