Retail

Sync Custom Table from AX to POS DB – Retail Customization

Issue Raised:

Batch Numbers of the items are not synced with the POS database, I need to sync all the batch number of the retail items  when product job is run. Basically I need to sync INVENTBATCH table from AX to POS database.

Solution:

In retail we have majorly 3 types of tables:

  1. crt (commerce run time)
  2. dbo
  3. ax

Step #1:

Tables which has ax prefix are used to sync data from AX. If you navigate INVENTBATCH table in POS db you can see the dbo type INVENTBATCH is available, to sync this INVENTBATCH create table from script by replacing [dbo] to [ax] as shown below:

inventbatch

after running the above script you will see the POS db has 2 INVENTBATCH one of type ax and other is of dbo. ax.INVENTBATCH  will be used to sync data from ax and dbo is used to safe the transactional data in it.

Step #2:

After the table is created in the POS db, connect the newly created table from Ax by creating the new scheduler subjob (Retail -> Setup -> Retail scheduler -> Scheduler subjobs) of INVENTBATCH:

scheduler-subjobs

when the new subjob is created for the table the “Channel table name” will not get the ax.INVENTBATCH table name to get the name view detail the dropdown and create the new channel table in the AX 2012 R3 table list:

channel-table

After creating the table name with the name given in POS db provide all the Location fields of the respective Table:

location-fields

once the location fields are defined, go back on the scheduler subjobs and select the ax.INVENTBATCH from the dropdown list and in the Microsoft Dynamics AX table name field select the corresponding AX table and click on transfer field list button to map the field list of AX to POS table:

transfer-field-list

when you click on the Match fields it will automatically create a related table field mapping as shown above and also it will create the method in the RetailCDXChannedSpecificData_AX63 class for InventBatch as shown:

retailcdxchannelspecificdata_ax63

you need to add the code on the RetailCDXXmlDataOutput_AX63 class. This class is used by the jobs to transfer the respective subjobs data, in my case I m using AX R3 schema that’s why I m using _AX63 extended class to get the INVENTBATCH :

retailcdxxmldataoutput_ax63

Once done with the code, update the “Retail channel schema” (Retail -> Setup -> Retail scheduler -> Retail channel schema). In my case AX2012 R3:

retail-channel-schema

select all and paste the table distribution to the notepad and add the following line to link the InventBatch table to its respective Item

Step # 3:

Add the InventBatch Subjob to product job (Retail -> Setup -> Retail Scheduler -> Scheduler jobs ):

add-subjob

after the subjob is added on the products job run the Products job on the respective store and navigate the data on the SQL on the ax.INVENTBATCH table data must be populated after the job is succesfully applied from download sessions.

Thanks,

Happy Daxing with Rizz 🙂

 

Advertisements

5 thoughts on “Sync Custom Table from AX to POS DB – Retail Customization

    1. Dear Riz Khan,

      first of all thanks for our effort.
      Secondly, I am facing the issue at the time of generating the schema, system throws error “field Item ID does not exist in table inventabatch” kindly suggest me what I am doing wrong.

      Thanks

      Like

      1. Dear Nasir,

        make sure you have properly added the table in the Channel DB and also you have linked it with the subjobs in AX and Matching fields is also done in the subjobs properly for InventBatch.

        If all the above things are done properly just make sure you are adding the InventBatch in Schema in the below format:

        Retail Schema

        Thanks,
        Rizwan Khan

        Like

  1. thanks for your Customization
    i faced problem with first step i got this error when i try to create that table

    [Msg 1921, Level 16, State 1, Line 8
    Invalid filegroup ‘_’ specified.
    Msg 1750, Level 16, State 0, Line 8
    Could not create constraint or index. See previous errors.]

    this is the script i used

    [use [TestMessageDB]
    Go
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [ax].[INVENTBATCH](
    [INVENTBATCHID] [nvarchar](20) NOT NULL CONSTRAINT [DF__INVENTBAT__INVEN__2ACO4CAA] DEFAULT (”),
    [EXPDATE] [datetime] NOT NULL CONSTRAINT [DF__INVENTBAT__EXPDA__2BB470E3] DEFAULT (‘1900-01-01 00:00:00.000’), [ITEMID] [nvarchar](20) NOT NULL CONSTRAINT [DF INVENTBAT ITEMI 2CA8951C] DEFAULT (”),[PRODDATE] [datetime] NOT NULL CONSTRAINT [DF__INVENTBAT__PRODD__2D9CB955] DEFAULT (‘1900-01-01 00:00:00.000’),
    [DESCRIPTION] [ntext] NULL CONSTRAINT [DF__INVENTBAT__DESCR__2E9ODD8E] DEFAULT (NULL),
    [DATAAREAID] [nvarchar](4) NOT NULL CONSTRAINT [DF__INVENTBAT__DATAA__2F8501C7] DEFAULT (‘DAT’),
    [RECID] [bigint] NOT NULL,
    CONSTRAINT [I_752ITEMBATCHIDX] PRIMARY KEY CLUSTERED
    (
    [DATAAREAID] ASC,
    [ITEMID] ASC,
    [INVENTBATCHID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON _) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    Like

    1. Hi Mohammad,

      it is easy to create the query by using the existing table which is dbo.InventBatch by just changing the prefix of that table to ax.InventBatch

      Thanks,
      Rizwan Khan

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s