This post is a follow-up to the quote below, from this post:
…But imagine, you will probably share the file you had in step #3 and your client will fill it, send it back to you at 2 in the morning! Either the ZoneID or LocationProfileID or even the warehouse will not exist in the environment. Then what? You can’t reach them in the middle of night, so you have to wait for the next day for them to explain and fix the file. Isn’t this time-consuming and unsustainable? This is why you better use SQL together with DIXF functionalities. Just create a new database to keep and manage your master data.
Create your data source table
Before we begin, we should create a separate DB to keep our client’s master data. And after that, first thing we will do is to create a table to use, instead of a source file.
However, in the source file in my previous post we had many columns that had values of zero all the time:
These fields must exist when we are using a source file but, when we move our process to SQL we don’t need such columns since we can add them through query, as I will also share in this post. Now, as the first step: Just fix the commented lines in the following query, according to your needs and execute it in your master data database:
USE –YourMasterdataDBNameHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LAX-WmsLocations](
[WAREHOUSEID] [nvarchar](10) NOT NULL,
[WAREHOUSELOCATIONID] [varchar](10) NOT NULL,
[WAREHOUSEZONEID] [nvarchar](20) NULL,
[SORTORDERCODE] [int] NOT NULL,
[WAREHOUSELOCATIONPROFILEID] [nvarchar](20) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
CONSTRAINT [PK_LAX-WmsLocations] PRIMARY KEY CLUSTERED
(
[WAREHOUSEID] ASC,
[WAREHOUSELOCATIONID] ASC,
[DATAAREAID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LAX-WmsLocations] ADD CONSTRAINT [Def_SortCode] DEFAULT ((0)) FOR [SORTORDERCODE]
GO
See the below screen and celebrate! But, not much. We are not done yet. We only got our first table, for now:
Create your reference tables
Now we have our data source table ready but, we do not want inconsistent data to be inserted here. What we will do next is creating reference tables in our SQL database; And validating values from there.
Get your location profiles from the application, create a table and insert this data into it under your master database with following SQL script:
SELECT DISTINCT
LOCPROFILEID,
DATAAREAID
INTO –[YourMasterDataDB].DBO.[LAX-RefWhsLocationProfileIDs]FROM –[YourAxDB].DBO.WHSLOCATIONPROFILE
We did it!
Let’s do it for our ZoneIDs too, with the following script below:
SELECT DISTINCT
ZONEID,
DATAAREAID
INTO –[YourMasterDataDB].DBO.[LAX-RefZoneIDs]FROM –[YourAxDB].DBO.WHSZONE
Then, for WarehouseID’s, also known as “InventLocationIds”:
SELECT DISTINCT
INVENTLOCATIONID,
DATAAREAID
INTO –[YourMasterDataDB].DBO.[LAX-RefWarehouseIDs]FROM –[YourAxDB].DBO.INVENTLOCATION
The last one, DataAreaIds:
SELECT DISTINCT
ID
INTO –[YourMasterDataDB].DBO.[LAX-RefDataAreaIDs]
FROM –[YourAxDB].DBO.DATAAREA
Now set all the fields in your reference tables to Allow Nulls=No. Also set all 7 fields in these tables as primary indexes:
Create table relations
We created all the tables we need and the last step we will do is creating the validations. Here is how I do it. Right click on the table that keeps your WMSLocation master data, select “Design” and then hit “Relationships” button:
Limit your client to use warehouses only for the ones that exist in your reference table:
And do it for ZoneIDs, too:
Next one, WarehouseLocationProfileIDs:
And the last one: The relation below will make sure the values in the DataAreaID field are consistent, too:
Now, save your source table.
Conclusion
Now we are all set. Just tell your client to fill this table with their master data. You won’t need to reach them at 2am in the morning because, there won’t be a bad value in your table.
Dynamics 365 DIXF with SQL
To get this data into Dynamics AX with DIXF, you should have an ODBC setup and this will let your DIXF module to connect your master data DB. Then you can easily paste the query I will share at the end of this paragraph. AX will select your data and insert into staging. However, there is a bug in Dynamics 365 and I am also waiting for it to be fixed to explain how I do my ODBC setups and how the import process works step-by-step. Hopefully this issue will be fixed soon!
For now, we can still use the file option. All we should do is executing the query below when the table is full of client’s data, and copying the result into an excel. Then save it as a file and use like I explained in my previous post: Import your data without any customisation: Warehouse locations through DIXF | Dynamics 365
Select query
The below code needs single quotation marks to be replaced because of WordPress formatting but, you can view the fixed code in txt format here.
SELECT
[WAREHOUSEID]
,[WAREHOUSELOCATIONID]
,[WAREHOUSEZONEID]
,[SORTORDERCODE]
,[WAREHOUSELOCATIONPROFILEID]
,[DATAAREAID]
,[WAREHOUSELOCATIONID] [DESTINATIONWAREHOUSELOCATIONID]
,’0′[BINID]
,’0′[GENERATECHECKDIGITS]
,CASE [SORTORDERCODE]
WHEN 0 THEN ‘0’
ELSE ‘1’
END AS [ISSORTORDERCODEMANUAL]
,’1′ [ISWAREHOUSELOCATIONIDMANUAL]
,’0′ [PHYSICALDEPTH]
,’0′ [PHYSICALHEIGHT]
,’0′ [PHYSICALHEIGHTABOVEGROUND]
,’0′ [PHYSICALMAXIMUMSTORAGEVOLUME]
,’0′ [PHYSICALMAXIMUMSTORAGEWEIGHT]
,’0′ [PHYSICALWIDTH]
,’0′ [RACKID]
,’0′ [SHELFID]
,’–‘ [WAREHOUSEAISLEID]
,’0′[WAREHOUSELOCATIONTYPE]
,’0′[ISDEFAULTCREDITONLYRETURNWAREHOUSELOCATION]
,’0′[ISDEFAULTISSUEWAREHOUSELOCATION]
,’0′[ISDEFAULTKANBANFINISHEDGOODSWAREHOUSELOCATION]
,’0′[ISDEFAULTPRODUCTIONFINISHEDGOODSWAREHOUSELOCATION]
,’0′[ISDEFAULTPRODUCTIONINPUTWAREHOUSELOCATION]
,’0′[ISDEFAULTRECEIPTWAREHOUSELOCATION]
,’0′[ISDEFAULTRETAILSTORERETURNWAREHOUSELOCATION]
,’0′[ISDEFAULTRETAILSTOREWAREHOUSELOCATION]
,’0′[ISDEFAULTSHIPMENTMAINTENANCEWAREHOUSELOCATION]
FROM [AxMasterData].[dbo].[LAX-WmsLocations]