Saturday, October 20, 2012

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
 

1 comment: