Importing Retail Transactions from POS to AX

Hi, regarding the synchronization of retail transaction from stores to HO, I received some inquiries what are the alternatives when there is an issue in the store that will prohibit Async Client from sending data to HO – for example, network issues, hardware failures.

Well, there is always the ever helpful DMF entities. It is limited – however, the 3 main tables needed to successfully Calculate Statements every end of day are all provided as standard entities.

If you are using the demo data from Contoso, chances are you will not be able to see these entities. However, go to Data import export framework à Setup à Target entities, just click New and drop down to Entity field and select as what has been provided above. This should automatically populate the columns Staging table, Entity Class and Target.

Note: If you have a legacy POS with transactions which you want to integrate with AX, you can just use this and just make sure that it will follow the template that you created for these entities in the Data import export framework à Common à Processing Group.

So, let’s do a quick sample for these entities. Won’t be detailing the actual creation of Processing Groups of DMF though.

Transaction header

Once you have already created a processing group for RetailTransactionTable, it should look something like this:

Generate a source file and select the fields that matters to you. The fields available here is limited but it’s very easy to customize. In this example, I will be using all the fields available except transTime and ValidFrom:

I did not include transTime because the field type of it in DMF is time, and in Channel DB, its integer – it can be converted though; ValidFrom is not available in the ChannelDB at all (if I’m not mistaken heh).

So what I usually do is, after I generate the sample file, I just copy the field names and add it in my SELECT statements. This is the select statement that I used:

SELECT   FORMAT(SHIPPINGDATEREQUESTED, 'MM/dd/yyyy') as SHIPPINGDATEREQUESTED,   STORE,   TERMINAL,   TRANSACTIONID,   BATCHID,   BATCHTERMINALID,   FORMAT(BUSINESSDATE, 'MM/dd/yyyy') as BUSINESSDATE,   COMMENT,   COSTAMOUNT,   CURRENCY,   CURRENCY AS CURRENCYCODE,   CUSTACCOUNT,   DISCAMOUNT,   ENTRYSTATUS,   GROSSAMOUNT,   NETAMOUNT,   NUMBEROFITEMLINES,   NUMBEROFITEMS,   NUMBEROFPAYMENTLINES,   PAYMENTAMOUNT,   SHIFT,   FORMAT(SHIFTDATE, 'MM/dd/yyyy') as SHIFTDATE,   STAFF,   FORMAT(TRANSDATE, 'MM/dd/yyyy') as TRANSDATE,   TYPE FROM AX.RETAILTRANSACTIONTABLE

You can modify according to your template, and add a where clause to select which record you want. I formatted here the dates so it’s easier to copy. :D If you run this statement in your Channel DB via SSMS, you can arrive into something like below:

If you right click from here, you can Save result as CSV or copy it with header – it’s up to you. I usually just save it into a file so that it can be directly imported to AX via DMF. After that, it’s just the normal process of Get Staging Data and Copy Data to Target in the Process Group.

Note: During staging data execution, if you save the result set as a CSV, you might want to uncheck the First row header. This is because the saved csv file doesn’t come with a header. If you want this checked, the first row should be the header name. Also, make sure that there is no extra space after the records you want to import.

Anyway, if you are successful, you should be able to see your transaction in Retail à Inquiries à Retail Store Transactions.

Retail sales transactions

Same process of creation of Process Group applies for this. I will already skip those, but here’s a sample select statement:

SELECT   LINENUM,   FORMAT(SHIPPINGDATEREQUESTED, 'MM/dd/yyyy') as SHIPPINGDATEREQUESTED,   STORE,   TERMINALID,   TRANSACTIONID,   FORMAT(BUSINESSDATE, 'MM/dd/yyyy') as BUSINESSDATE,   COMMENT,   COSTAMOUNT,   CUSTACCOUNT,   DISCAMOUNT,   INVENTTRANSID,   ITEMID,   LINEWASDISCOUNTED,   NETAMOUNT,   NETAMOUNTINCLTAX,   PRICE,   QTY,   STAFFID,   TAXAMOUNT,   TRANSACTIONSTATUS,   FORMAT(TRANSDATE, 'MM/dd/yyyy') as TRANSDATE,   UNIT,   UNITQTY FROM AX.RETAILTRANSACTIONSALESTRANS

Now, if you import this directly via DMF, you might encounter an issue of overwriting records. I tried this, and after Copy data to target my Infolog says, Data written to target (‘0’ records created, ‘3’ records updated). This is because of the relationship between DMFRetailTransactionSalesTransEntity and RetailTransactionSalesTrans. To fix this, I set the EDT relations to No – the related fields between the two tables are all identified anyway so you can actually do this.

If successful, you should be able to see your transaction in Retail à Inquiries à Retail store transactions à [Transaction] à Sales transaction. Know that the list of fields in DMF for Sales transaction is limited, like below, you might want to consider adding the Receipt number and Variant Id (if you are using Color, Style and Size) fields in the staging and target table 

Payment Transaction

Still the same process, here’s a sample select statement:

SELECT   STORE,   TERMINAL,   TRANSACTIONID,   AMOUNTCUR,   AMOUNTMST,   BUSINESSDATE,   AMOUNTTENDERED,   EXCHRATE,   EXCHRATEMST,   LINENUM,   QTY,   SHIFT,   SHIFTDATE,   STAFF,   TENDERTYPE,   TRANSACTIONSTATUS,   TRANSDATE FROM AX.RETAILTRANSACTIONPAYMENTTRANS

You might encounter an overwriting issue related to the relationship between DMFRetailTransPaymentTransEntity and RetailTransactionPaymentTrans, same thing should be done for the DMF table, set the EDTRelation to No.

If successful on the importation, you should be able to see your payment transaction in Retail à Inquiries à Retail store transactions à [Transaction] à Payment transaction. Know that the list of fields in DMF for Payment transaction is limited, like below, you might want to consider adding the Card details as a customization.

If you would like to download the sample text files for DMF, it’s available here. This contains sample data as well, just be mindful of the transaction number as it might already exist in your environment.

Hope this helps!

KR, Lyka