General Setup
Now we have our basic installation completed we not need to move onto what we need in our Ecommerce store to make it functional and also set up connections within our Microsoft Flow.
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 the following articles I will aim to take use these new technologies and combine them with AX 2012. We’ll look at using an ecommerce example. In the previous examples I set up an Ecommerce Store and also the Retail Channel database for the Contoso store. In the past it was difficult to get at this information in these databases without making sure they were visible via the internet.
In this artcle we will harnes Microsoft Power Automate along with the DataGateway which we have set up to access this data.
Setting up Microsoft Power Automate or Flow
Now that the databases have been configured in the previous article let’s start to put our Flows together. Before the flows can be useful they need to able to see the database. To do this I will create two connections in Flow to link to our on premise Databases.
First let’s create a new connection to the Retail Channel database and complete the connection details to the Retail Contoso Database.
Click on connections on the right hand side and select SQL as the connection type. Then select add connection. A dialog like the picture below will be displayed. We will be using SQL Server Authentication so fill in the Server Name the Database name and the username and password used to connect to the Retail Channel database.
Then press create. Simple as that.
Second let’s create another new connection and fill in the connection details to the NopCommerce database. Again Click on connections on the right hand side and select SQL as the connection type. Then select add connection details. We will be using SQL Server Authentication again so fill in the Server Name the Database name and the username and password used to connect to the Retail Channel database.
Then press create.
All going well we now have two databases connections configured in Power Automate. One connection pointing to the Retail Channel Database and the other pointing to the NopCommerce Database. Now we are ready to connect and synchronise data between these databases using Microsoft Power Automate.
The aim of what are doing is to move data from the Channel database into our Ecommerce store and create information like categories and products. Before we start this process lets set up some simple scripts which will help with the process.
Setting Up Ecommerce Store SQL Scripts
Before we start sending information into our ecommerce store I would like to create two small scripts for the ecommerce database. These scripts will help us transform the data from the channel database into the ecommerce database.
Create URL or WebLink Script
The first script is very specific to NopCommerce and is for creating URL records for the store to function. Products and Categories need a URL to navigate to and NOPCommerce stores these URL records in a separate table called URLRecord. I will create a stored procedure to create these URL records. This procedure also accesses a simple function which removes non alpha records from a string, this is useful for creating URL records as web browsers don’t like non alphanumeric characters.
it works by passing in the ID of the Product or Category I am creating a web link for. The name of the Product or Category. Finally the type i.e. Product or Category.
I will describe the non alpha function in the next section.
|
CREATE Procedure [dbo].[CreateUrlSlug] ( @inID int ,@inName varchar(1000) ,@inType varchar(100) ) AS DECLARE @Slug varchar(200)
SELECT @Slug = dbo.RemoveNonAlphaCharacters(lower(@inName)) SET @Slug = replace(@Slug,'+','')
IF EXISTS(Select * from UrlRecord Where Slug = @inName and EntityName =@inType) BEGIN SET @Slug = @Slug + '_' + rtrim(ltrim(str(@inID))) END
INSERT INTO UrlRecord ([EntityId] ,[EntityName] ,[Slug] ,[IsActive] ,[LanguageId]) VALUES (@inID ,@inType ,@Slug , 1 ,0)
|
Remove Non Alpha Characters Script
This function removes characters which are non Alpha’s numerics, this is very useful if you are creating URL,s which work a lot better without non alpha numeric characters. Alternatively your get lots of strange letters like %12 etc..
|
CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000)) Returns VarChar(1000) AS Begin
Declare @KeepValues as varchar(50) = '%^a-z0-9%' While PatIndex(@KeepValues, @Temp) > 0 Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
SET @Temp = replace(@Temp,'/','') SET @Temp = replace(@Temp,'&','') Return @Temp End |