Dynamics AX 2012 - Setup Financial Dimensions and define Dimension data

Financial dimensions is an advanced topic in Dynamics AX 2012. There are various aspects to learn this topic. My original requirement was to setup a new financial dimension and define some data for it. Adding a new financial dimension is as easy as pie. You can create a new financial dimension by following the following path:
GL > Setup > Financial Dimensions > Financial Dimensions

Due to the extra ordinary flexibility built in by Microsoft in Dynamics AX 2012, you can create as much as financial dimensions (aka ledger dimensions) as you wish. However as per the best practices and common usages, pretty fair amount of such dimension types are built in the system. As you can see from the image below, there is an option of 'custom dimension' available with the other pre defined dimension types.

Fig a. Creating new dimension

So adding a financial dimension is really as easy as APIE. What was a surprise for me (due to my own lack of knowledge :D, pretty dumb huh ) was the method of defining data for these new dimensions.

Normally one would expect to have a dedicated form for adding data for a dimension. And this is what the case really is if you see the highlighted area in the following image.

Fig b. Financial dimension values

The surprise is that when the button "Financial dimension values" is clicked to open the concerned form, the new button is disabled. And it remains disabled for each dimension that I add. The question arising in my mind "Where to add data for this dimension ? Where to define the expense purposes ? where to define data for each of these system defined dimensions"

And this is where Google is very handy. See in my scenario, I had to define expense purposes (cause that will enable me import an account structure and add some gen journals from code), so I googled "dynamics ax 2012 new expense purpose" and the first post actually resolved my query :D

Each dimension's value is based on the selection you make in the dimension setup form ( see fig a above ). These are all existing modules of the system and the value forms and menu items already exist. To add new values for a dimension based on these system modules, you have to browse those relevant forms, fill the data there and expect the values to reflect here :)

Take expense purposes for example. If you have added an expense purposes financial dimension into your account structure, you can define the data for this dimension from Travel and expense > Setup > Optional Setup > Expense purposes

I will share a few important ones for you as follows;

• Fixed assets: Fixed assets > Setup > Fixed asset groups
• ProjectContract: Project management and accounting > Projects > Project contract

The 2nd question arises ? Does that Financial dimension values > New button enables ever or is it just dropped there mistakenly. The answer is "Custom Dimension". Yes its the custom dimension whose data is definable here. See the image below please;

Fig c. New financial dimension value

A few more related things

Chart of accounts is one thing, and account structure is another.

Chart of accounts

It is the collection of all the main accounts that we create. Every single account exists in the chart of accounts (table: MainAccount)

Account Structure

Account structure is the combination of main accounts with the defined financial dimensions. This means what financial dimensions will used along with accounts generally for transactions. Consider the following image.

Fig d. Define accounting structure 

As you can see, the fully qualified account structure is composed of the main account followed by department > cost center > expense purpose. And for the explanation of extending the account structure, the  "Add segment" button functionality is shown in which you can select available dimensions to further enhance and granulate the main accounts.

Import data into Dynamics AX 2012 using Microsoft Excel Add-in

Though it has been very boring and time consuming for me in the beginning, yet it ended quite fruitful to me in the end :)

Without further delay, one of the most common and basic task when implementing an enterprise system is to import the legacy system setup data into the new system. The objective of this post is to share all the basic tools and scenarios you will need to import / export data.

This post will specifically provide guidance on importing records from external sources like excel file into Microsoft Dynamics AX 2012. But first of all, lets see how much variety has Microsoft provided in the regards of data import export and migration between the AX and other system;

http://technet.microsoft.com/en-us/library/aa548629.aspx : A worthy resource to begin with. Read the small article and get a good basic idea of all the tools provided by Microsoft and their purposes.

Secondly lets get hands on importing a few records in some setup table of AX, say MainAccount (chart of accounts table). By the way you can import data into any AX table

Quoting from the above mentioned post:

Microsoft Excel Add-in: Excel import is a tool that can be used for data in an Excel format, or in a comma-separated list. You can use built-in or custom templates to help users import data. Use this tool in the following situations:

• Minimal data cleansing by end users is required.
• You are handling low to medium data volumes (< 10,000 records).
• You are working with simple data structures that can easily be flattened.
• No business logic is required outside an existing document service.

Now since this is quite a handy and straight forward tool powerful enough to handle around 10000 records, it would be the pick for us for basic setup data import. Hence we will use excel add in for setup data imports.

Installation
For installation and configuration with AX, see these posts:
http://msdax.wordpress.com/2011/05/14/office-addins-dynamics-ax-2012/

Usage:
The usage as far as import is concerned is fantastically straight forward. And if the tool is rightly configured (i was lucky to got it configured already at my place :) ),  its as easy as PIE literally. All you have to do is to follow the following straight points:

• Create a new document.
• Go to Dynamics AX tab
• Scroll click the Action Pane > Design > Add Data command menu button
  
• Click Add tables menu item
  
• Select the desired table (all tables are provided, in our example we will select the MainAccount table)
• This will enhance the excel UI with a Field chooser left bar (showing all the fields) and a few mandatory fields (typically the PK and FKs) dropped over the worksheet.
  
• Select at least all the mandatory fields required to save a valid record in the selected table. In our example, we will drop the following fields;
• Chart of accounts
• Main account
• Name
• Main account type
• Reference ID
  
• To load data from the table: Close the field bar (which should enable the publish button) and hit Action Pane > Update > Publish
• Start creating records by simple typing and tabbing to next fields and to next records.
  
• To save your work back in AX, hit the publish button and you are done
• Publish button disabled: Close the field bar on the left. You should close this bar as soon as you are done with choosing the fields. Closing the field bar may result in wiping all the newly created records. Make sure you close it as soon as you are done adding the fields

There are quite a lot worthier posts on the internet on the topic. The goal of this post is to bring under 1 umbrella all the basic steps required to start importing data and share / reuse existing worthy resources over the internet on this topic. The most worthy of them I found are the data import series blogs here: https://community.dynamics.com/ax/b/ax2012exceldataimport/default.aspx

Enjoy importing data into Dynamics AX like APIE :)

Class diagram for Number Sequence Framework [partial]

In continuation to a post written several months ago [http://dotnetxperience.blogspot.com/2012/07/number-sequence-framework-in-dynamics.html ], I thought to make a class diagram in order to show all the related objects required for successfully implementing the number sequence framework. The original motive was to write one COMPLETE post depicting all aspects fo the framework however i am not satisfied from my effort. I will improve it for all of you and for myself as well.

At the moment, I am sharing the class diagram. It s not the best of them, but for sure it will assists all of us in better understanding of the framework.

Some tips when using Expressions in X++ Dynamics AX

Expressions come to rescue when you need to code complex joins and constraints in your queries. I always have trouble setting my larger expressions with quotations and parenthesis  It always looked perfect but they always gave error. This was due to no proper Expression specification under my observation untill I found the following (The excerpt is taken from a book on AX: Microsoft Dynamics AX 2012 Development Cookbook )

• The expression must be enclosed within single quotes.
• Inside, the whole expression has to be enclosed in parenthesis.
• Each sub expression must be enclosed in parentheses too.
• String values have to be enclosed within double quotes.
• For enumerations use their numeric values.
• For value formatting use various Dynamics AX functions, such as queryValue(), Date2StrXpp(), or methods from the SysQuery class.

Lookups and drop downs in AX - Part I

Lookups are pretty straight forward, drop the related field and AX should bring a nice drop down to you. However things can get pretty nasty when you apply further behaviour, the simple drop down remains no simple.

As i just wrote the one liner, drop the field and there u go. However there are other methods to achieve better results as well.

Depending on the join, the behaviour and display of the lookup depends. A RecId based master child relation field will drop the RecId of the master record. Se below;

The objects I have used to achieve the above are;

• 2 tables named master and child. 
• Joined child table with master as a 1 to many relations
• Created a new form (no template), added both tables in the data sources. 
• Dropped the relation field on the form.

Thats it, but the result is pretty rough, not helpful to the UI at all, what does that 10 digit typical surrogate means to the end user, its a nightmare for him. The good thing is, there is no nightmare for the devs :D

What MorphX has done is place an integer field on the form bind to the child data source's master table relation field. The existing relation provides the lookup behaviour automatically, no code to override, no nothing, pretty straight forward.

AX is a great RAD platform, it does not give u time to do all the coding for these small repeating things, its already there, u need to know how :D

Well the most usual practice a dev has is to copy an existing behaviour, that will do the job, but I actually went a little deeper into it and played with all the relevant features available. Lets dive in then.

The Table's Autolookup group

There exist a default group for each table, 'Autolookup', lets drop a field into it and see the difference if any;
To achieve this, i placed a data field (any field u can choose) in the Autolookup group of the master table. We have edited the master table for the simple reason that the drop down or lookup is supposed to fill from there :D

The result of this is;

As you can see, the code field has started appearing next to the unwanted RecId. This is at least a little better than the first pure raw result. Of course we still need to remove the unwanted id, but what we learned from this step is that fields present in Autolookup group of a table starts appearing in lookups belonging to that table.

Lets add another field to ensure what we learn is exactly what we just described :D adding 1 more field results in following;

Hence ensured. This means we need to fill the Autolookup group for every setup / master table so that a decent user friendly lookup appears whenever a reference is made to them.

But the unwanted rec id is still bothering, and also 1 more thing is the behaviour of the form when a particular value is selected; see below;

After a selection is made, the same rec id is appearing about which, the user have no hint what has it actually selected.

Lets see if adding an Index have an effect on this. An index is basically created on a table on fields so that searching gets fast. But it is also used as a means to achieve uniqueness on a particular set of fields.

Adding an index on a third filed has no effect on the lookup (no new image required :D)
However, removing each field from Autolookup group with an index on a third field (which was never part of the Autolookup group) brings the following result;

This means that field(s) present in Index(es) appears in the lookup of that table. However, fields in Autolookup group overrides them. We are finding core facts here, no theories, and I really enjoy this :D

The unwanted RecId is still appearing, lets see what another default group AutoIdentification has to do with it

The AutoIdentification group
By default, you can never add any field in this group by dragging your custom selection. The only way to add a field into it is to create an index.

Adding a simple, non unique default index does nothing to the AutoIdentification group, we will need this index set as a replacement key. To do this, the index must be unique (set allow duplicates to No in the property sheet) and select this unique alternate key index as the replacement key for the desired master table. There you go, you got the index field in the AutoIdentification group :D, illustration for you for precise guidance :D

This brings the indexed field 'code' in the AutoIdentification group.

But how will it effect our lookup ? Well the answer is, now AX knows your master child relationship more better, so Drag the same field once again, and to your surprise, this time it wont be a simple integer field bind to the child table, its a whole new 'Reference group' control.

Now we have 2 lookups on the form actually, the one with the previous incomplete table configuration (integer field bind to child table's master referencing field) and the new Reference group with more properties to explore :D

This has get us rid of the unwanted RecId, now we have quite a decent lookup appearing at our screen. The user will be delighted to see this as compared to the previous ones. And as we have already learned, adding more fields in a lookup can make lookups more detailed if required.

Scenario: Show more fields in lookup but select specific
Yes, how can we achieve this? That is, show some filed in the lookup , but when selected, a different field gets selected in the textbox area. What I am trying  to say is a typical code description scenario in which user opens a lookup that shows code and descriptions both, but  once a selection is made, only code is selected. How to achieve that ? Well again as so many times you have read that AX is fast RAD platform, nothing takes long time here. To achieve our this goal, we need to look at some of the properties on the reference group control.

Reference group control - Details
The key properties of this control are as follows;

• ReferenceField: A reference field on the bound data source to use as the physical binding. This field is not displayed to the user
  In short, this is the field which will be used behind the scenes and the record will be populated with this value. In our example, the reference field is the 'master' field in the child table, the rec id of the master table's record the user selected in the drop down.
• ReferenceFieldGroup: A field group on the primary key table to use as the logical binding. These fields are displayed to the user.
  In our example, the group selected is the AutoIdentification group, which was populated when we created a unique index, used it as alternate key and set it as master table's replacement key.

Other fields of course contains data source and data field, whenever dropping a lookup form master table, drag the field from the child table, not the master table since the changes are to be made in child table no the master one :D

Now to have multiple values displayed when lookup is opened and select one of them in the drop down, we will do the following

add multiple as many fields you want to display to the user when lookup is opened, we will add code and descr fields. And already in the AutoIdentification group, we have the code field ( the indexed field), so this will bring us the desired results. The following is the table level filed group configuration and then its result:

This is the way to do the simple lookups in AX. To conclude all this, i have written concluding points below. For more advance topic, I will write the Part II tomorrow إن شاء الله‎ 

Conclusion:

1.) Autolookup overrides index

2.) Fields from each index are fetched in lookups along with PK (RecId) when no field exists in autolookup group of primary table.

3.) When multiple indexes used on table ( & no field in autolookup), first field of each index is brought, depends on their order in index.

4.) Use Reference group control for better user friendly lookups. Reference group provides two properties 'ReferenceField' and 'ReferenceFieldGroup' which fetch relevant data in the lookups as required by the user

Modify AOT items from code - AX 2012

The Problem

Hello guys, at times you may require a particular AOT object, like a menu item or a table, or form etc to be modified from code. Consider a task given to you to update the User CAL settings on each  menu item. Now depending on your solution, it can require hundreds of menu items to be modified one by one.

Another example would be to update the configuration key of each AOT object in your solution. In these bulk updates you need to have a means of updating each object and set its specific properties to a particular setting. Such a task was assigned to me, and yes I could have been a labor mule updating each item and going for the other, but I remembered that I am a knowledge worker, that means, I have to use my brain and work smartly with key decisions.

So I realized that there has to be a means of bulk updating each AOT item object and setting its properties to save time and labor :)

So lets take the real time example here. I have been asked to update the User Cal settings of each menu item  and set it to Task. And since I am a smart knowledge worker, I will avoid the very lengthy manual approach and will look for some smart code and already provided interface to modify existing AOT Item.

My case is specific to Menu items, but the code and approach below will work for all objects within the AOT.

TreeNode Class [AX 2012]

As per the very helpful one liner at msdn,

"The TreeNode class is used to get a handle to any node in the AOT. This class also contains methods that are used to maneuver in the tree."

This class can make you do wonders, like adding new objects (all kinds of), compiling them, and much more. This post will be specific to the problem defined in the above paras :)

So now we need to have each menu item in an iterative lop so that we modify them one by one. The question arises, how we do start with this class to modify any existing AOT item. The answer is this class's static FindNode method :)

TreeNode::findNode Method [AX 2012]

One liner from msdn to start with,

"Gets a specified node in the Application Object Tree (AOT)."

Syntax

client server public static TreeNode findNode(str path)

The idea is to pass a starting path to the path parameter. The node the path will point to can work as a parent node as well as sibling node meaning once on a valid node, we can browse both its siblings or pears as well as its sub nodes or child nodes (if it has).

In our eexample, we will give the path of Display menu item to be treated as a  parent node. From there, we will start looping all the sub nodes (display menu items). Consider the following line;

objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");

Now the object objTreeNode is referencing the node shown in the image below;

Now we can loop its sub nodes from the following code:

    TreeNode    objTreeNode;

    objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");

    if (objTreeNode)
    {
        objTreeNode = objTreeNode.AOTfirstChild();
        /*
        change objTreeNode object properties
        */
        while (objTreeNode)
        {
            info(objTreeNode.treeNodeName());
            objTreeNode = objTreeNode.AOTnextSibling();
        }
    }

The above will print in the info log each display menu item one by one.

Updating the properties of an AOT Item

The TreeNode class's method AOTsetProperties can be used to do this. Syntax is as follows;

public void AOTsetProperties(str properties)

The above example is modified as follows to set the View user license and maintain user liccense to Task for all display menu items found in the AOT under the provided path.

    
    TreeNode    objTreeNode;

    objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");

    if (objTreeNode)
    {
        objTreeNode = objTreeNode.AOTfirstChild();
        /*
        change objTreeNode object properties
        */
        while (objTreeNode)
        {            
            objTreeNode.AOTsetProperties("PROPERTIES\n ViewUserLicense  #" + 'TASK' + "\n  MaintainUserLicense  #" + 'TASK' + "\n ENDPROPERTIES\n");
            objTreeNode.AOTsave();
            objTreeNode = objTreeNode.AOTnextSibling();
        }
    }

Happy AOT coding :)

Links

• X++ code to modify/set properties of AOT elements in Dynamics AX
• Changing properties of AOT objects in code
• Creation of MenuItem using X++ code

Filtering radio button items out

Radio buttons or option buttons are used to provide user with single option selectable interface.

In AX, the usual practice is to bind a base enum with the enum type property of the radio button so that it renders all elements of the base enum as available options. When making general data forms, all base enum fields of a table render as radio button on the forms.

This is all normal and known. What I want to share in this post is a specific scenario with me and can happen with any one else as well.

Take a look at the Gender base enum.

When adding a worker, or a person in DirPerson, you specify the gender of the person which is this field 'Gender' in the AOT. Now it has the two obvious Male and Female items with a "Not so required" Unknown as well.

Ironically, the 'Unknow' option has no label defined as well. This results in a UI rendered as follows.

This yields to a common problem statement, "We may require to load specific values of a base enum in the responsible UI control (drop down / radio button)". In my case, I want to get rid of this extra "No label" option which has no significance in my simple requirement.

I searched on the internet and nothing was up to the exact mark. Further smart searching revealed this very very useful link . What the link asks you is to AutoDeckare your control, override form's run method, and there, use the control's methods to get your work done :). The code is shared as follows;

public void run()
{
   super(); 
   radioCode.delete(''); //This line removes the option whose text is equal to ''
}

And my form loads only Male and Female genders. Thats all I want. I will attempt this solution on drop down as well and will update the results here.

One other way to achieve this is leave the binding method and add custom items in the radio button.

Add custom items in Radio button control

I was unaware of the possibility of adding / removing custom items in a radio button list control until today. A There are 3 properties providing interface to add custom items as follows;

1. Items - number of custom items to add in the radio button
2. Item - the 1 based index of the current item you are working at the design time
3. Text - the text of the item currently being edited to display

Binded radio button controls have the first two properties disabled. If you drop a new radio button list on the form or remove the enum type property, these 2 properties will enable.

You can add as many custom items you want. The question rises, "How to tackle custom items in code".

Handling custom items of radio button in code

Well this is not difficult either. The following code demonstrates how you gonna do it.

Happy coding.