Microsoft Dynamics AX 2012 Excel Add-in – Questions and Answers (Number Sequences)

Microsoft Dynamics AX 2012

Excel Add-in – Questions and Answers (Number Sequences)
 

Purpose: The purpose of this document is to provide answers to frequently asked questions related to Microsoft Dynamics AX 2012 Excel Add-in.

 

Question: How do I work with Number Sequences in Microsoft Dynamics AX 2012 Excel Add-in?

 

Answer: The way you work with Number Sequences in Excel Add-in varies depending on data model properties for Number Sequence controlled fields and AIF proxy classes implementation for particular business entities. In this document to illustrate main concepts I will provide examples of General journal – Journal number (LedgerJournalTable.JournalNum) and Sales order – Sales ID (SalesTable.SalesID) fields which are controlled by Number Sequences in Microsoft Dynamics AX 2012.

 

Details

 

LedgerJournalTable and SalesTable Tables in Microsoft Dynamics AX 2012 have corresponding AxLedgerJournalTable and AxSalesTable AIF Proxy classes. All AIF Proxy classes extend AxInternalBase class. AxInternalBase class in its turn implements some core methods related to data manipulation.

One of core methods in AxInternalBase class is doSave method. Focusing on Number Sequences in this investigation we'll take a closer look at setTableFields method where the assignment of ID from Number Sequence usually happens.  

 

General journal – Journal number

 

In standard Demo data "Gene_10" Number Sequence is used to assign Journal batch numbers

 

Out-of-the-box LedgerJournalTable.JournalNum field is Mandatory and has AllowEditOnCreate and AllowEdit properties set to "No" which means that the intention is to assign Journal batch numbers automatically from Number Sequence 

 

 

Rich Client

 

Out-of-the-box in Rich Client once you create new General journal header Journal batch number is assigned from Number Sequence right away

 

This is triggered in initValue method of Form Data Source

 

Forms\LedgerJournalTable\Data Sources\LedgerJournalTable\Methods\initValue

 

Excel Add-in UI is different from Rich Client UI in terms of dynamics. In Rich Client once form is opened it is loaded into a memory and client-side Form code can trigger different programmatic actions to implement dynamic behavior (stateful behavior). Excel Add-in implements static behavior (stateless behavior) so every time data is published to Microsoft Dynamics AX 2012 it is done through formal AIF interface.  

 

Application Integration Framework (AIF)

 

Excel Add-in uses AIF Web Services to publish the data to Microsoft Dynamics AX 2012. For "Add Data" scenarios the respective AIF Web Service will be used and for "Add Tables" scenario Generic Document Service will be used.

 

Please see the examples of Request and Response XML messages when using General Journal Web Service

 

Request XML

<![if !vml]><![endif]>

 

Response XML

<![if !vml]><![endif]>

 

Please see the examples of Request and Response XML messages when using LedgerJournalTable Table

 

Request XML

<![if !vml]><![endif]>

 

Response XML

<![if !vml]><![endif]>

 

Please note that even despite the fact that I'm passing JournalNum = "ALEX" as a part of Request XML message, the system will disregard JournalNum value because LedgerJournalTable.JounalNum field has AllowEditOnCreate property set to "No"

 

Classes\AxdBaseUpdate\deserializeTopEntity

 

Call Stack:

<![if !supportLists]>-          <![endif]>Classes\LedgerJournalTableType\defaultJournalNum

<![if !supportLists]>-          <![endif]>Classes\LedgerJournalTableType\defaultRow

<![if !supportLists]>-          <![endif]>Classes\AxLedgerJournalTable\defaultRow

<![if !supportLists]>-          <![endif]>Classes\AxLedgerJournalTable\setTableFields

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\doSave

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\save

<![if !supportLists]>-          <![endif]>…

 

Eventually the system will automatically assign new JournalNum value from Number Sequence in setTableFields method of AxLedgerJournalTable class

 

Classes\AxLedgerJournalTable\setTableFields

 

Classes\LedgerJournalTableType\defaultRow

 

As the result the value of Batch journal number for General journal header will be different from what I originally specified in Excel Add-in

 

What if I don't specify Journal number?

 

In the case if you don't provide the value for Batch journal number before publishing Excel Add-in will treat this record as incomplete and disregard it because LedgerJournalTable.JournalNum field is Mandatory in data model

 

Add Data (LedgerGeneralJournalService)

 

Add Tables (LedgerJournalTable)

 

This means that in any case you have to provide some (dummy) value for Journal batch number in Excel Add-in

 

What if I set up Journal number Number sequence as Manual?

 

 

In the case if you provide the value for Batch journal number before publishing but you have Number Sequence set up as Manual, the system will eventually disregard the value you explicitly passed but it will not be able to assign new value automatically and you will see the following error in Excel Add-in

 

Add Data (LedgerGeneralJournalService)

 

Add Tables (LedgerJournalTable)

 

This means that you have to have Journal batch number Number Sequence setup as Automatic in order to complete data import exercise

 

Sales order – Sales ID

 

In standard Demo data "AR_018" Number Sequence is used to assign Sales order IDs

 

Out-of-the-box SalesTable.SalesID field is Mandatory and has AllowEdit property set to "No" which means that Sales order ID is automatically generated from Number Sequence but based on the data model you could technically change it upon Sales order header creation

 

 

Rich Client

 

Out-of-the-box in Rich Client once you create new Sales order header Sales order ID is assigned from Number Sequence right away

 

Please note that on Create sales order form the decision whether or not to make Sales order field editable is made based on Number Sequence setup (Automatic vs. Manual) 

 

The automatic assignment of Sales order ID is triggered in create method of Form Data Source (assuming Number Sequence setup is Automatic)

 

Forms\SalesOrderCreate\Data Sources\SalesTable\Methods\create

 

 

Sales Order Web Service can't be used for data import at the moment because of Surrogate foreign key expansion error

 

For the purpose of this investigation I'll use SalesTable Table

 

Please note that in this case the value of SalesID = "ALEX" which I'm passing as a part of Request XML message will be carried through deserializeTopEntity method of AxBaseUpdate class

 

Classes\AxdBaseUpdate\deserializeTopEntity

 

Call Stack:

<![if !supportLists]>-          <![endif]>Classes\AxSalesTable\setSalesId

<![if !supportLists]>-          <![endif]>Classes\AxSalesTable\setTableFields

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\doSave

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\save

<![if !supportLists]>-          <![endif]>…

 

Classes\AxSalesTable\setTableFields

 

Classes\AxSalesTable\setSalesId

 

Please note that the system is only supposed to automatically assign Sales order ID in the case it was not provided. This means that the system will keep Sales order ID specified before publishing in Excel Add-in

 

However after you specify Sales order ID and try to publish data into Microsoft Dynamics AX 2012 in Excel Add-in you will see the following error

 

 

This is because there's additional check on Sales order ID in setSalesId method in AxSalesTable class which requires respected Number Sequence to be either Manual or Allow for user changes (To a lower number or To a higher number)

 

Classes\AxSalesTable\setSalesId

 

In this case I'll go with Allow user changes option

 

 

Finally you will need to make sure that Sales order ID value you provide conforms to the format of Number Sequence in order to avoid the following error 

 

Please see the result in Microsoft Dynamics AX 2012 below

 

What if I don't specify Sales ID?

 

In the case if you don't provide the value for Sales order ID before publishing Excel Add-in will treat this record as incomplete and disregard it because SalesTable.SalesID field is Mandatory in data model

 

Add Tables (SalesTable)

 

This means that every time you have to provide a value for Sales order ID in Excel Add-in

 

What if I set up Sales order ID Number sequence as Manual?

 

 

In the case if you provide the value for Sales order ID before publishing and you have Number Sequence set up as Manual, the system will take value you explicitly passed into account as long as the value conforms to Number Sequence format

 

Version: Microsoft Dynamics AX 2012 R2

 

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Question, Answer, Number Sequence.

 

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions. Please also note that you can change Number Sequence setup for data import exercise and then change Number Sequence setup back to its original state.

 

Author: Alex Anikiev, PhD, MCP