AX2012 : Tip when using date effective tables as a reference data source

Hi Friends,
Reference data sources is a powerful feature in AX 2012. A reference data source enables you to add the replacement fields for a surrogate foreign key replacement to a form design. It is used extensively on standard AX forms.

It becomes more interesting, when we reference date effective tables . In such scenarios we need to perform some steps to make sure that the date effective tables are queried correctly.

We can find a good example on sales table form. Let us have a look:

1. The date effective data sources are added under the sales table datasoure node as reference data sources.

 

2. The below piece of code on form init() method to check the validity:

 

 

 

3. The below piece of code is added on sales table datasource  >> init() method, to set the valid time state range criterions:

 

If you doing some development task and plan to add date effective tables as reference data source, then make sure to model it the same way. Not doing so will cause an issue that the expired records will display a "Unknown" value in the reference data source record. A similar problem has been reported on Microsoft dynamics AX community forum. Detail can be found at this link. The below image reference is taken from the link:

Whitepaper to use date effective framework can be downloaded from here, however this information is not mentioned there.

Thanks for reading the blog.
 

AX2012 : Adding text translations to entities using X++ [Technical walk-through]

Hi Friends,

Microsoft dynamics AX provides text translation possibilities on some standard entities like products, ledger accounts, financial dimension and few more. Text translations are displayed on documents where a language code is applied for example packing slips and invoices. Also, when the system language for the user corresponds to the translations, the translations are displayed in Enterprise Portal for Microsoft Dynamics AX.

A simple way to store the translations is to open the translations form , select the translation language and then save the translated text. For example to store translations for a product name, open the translations screen from product list page as shown below :

Select the language from the drop down which comes on clicking on the + button

Enter and save the translated text.

There can be instances where you want to extend this capability to other existing or new entities in the system to make your solution flexible and rich. From technical point of there is a standard AX class called SysTransalationHelper which provides capabilities to achieve this. In order to understand how we can use this class, let's do a quick walk-through and extend the standard AX's Questionnaire entity to have ability to set-up text translation.

The standard AX form for questions can be found at Home >> Common >> Questionnaires >> Design >> Questions

PS --> For demonstration purpose, I am just creating the minimum required methods and objects. In real time make sure you follow development best practise recommendations. I will be prefixing new objects with DEM_ to distinguish from standard AX objects.

Step 1 : Create a new table. I called it DEM_KMQuestionTranslation. Now add 2 foreign key relations as shown below, one relation with KMQuestion table and another with LanguageTable. KMQuestion table stores the Questions information and the Language table is used to store list of all languages available on the system. Additionally add a Description field. The table structure should look as shown below:

Add the below 2 methods in this table:

createOrUpdateTransalation() --> This method is used to create or update a question translation record. The implementation is quite straightforward. Refer to standard AX method \Data Dictionary\Tables\EcoResProductTranslation\Methods\createOrUpdateTranslation() to view a similar implementation.

findByQuestionLanguage() --> This method is used to find the specified record in the DEM_KMQuestionTranslation table by using the specified question language. Similar standard AX method implementation can be found at \Data Dictionary\Tables\EcoResProductTranslation\Methods\findByProductLanguage()

Step 2: Now we need to add below line of code in standard AX KMQuestion table insert() method. This is to create a default translation record in our new table whenever a new question record is created in the system.

Step 3 : Now we need to create a new class. Let's call this class DEM_QuestionnairesTranslationHelper  and add couple of methods as shown below:

Class declaration , declare two variables as shown below

Create parm methods to get and set the values

Now here comes the interesting part. Create a method to construct a new object of SysTransalationHelper class and use the tablenum of the main entity table and the new table which is storing the language translation details as shown below

Secondly create a method to launch the translation detail form. To do this we need to create a method as shown below which uses SysTranslationHelper class object as a parameter and calls it’s launch translation form method:

The last method required in this class is to instantiate the class . To do this create a new method as shown below:

Step 4 : Now we create a new menu item for this class

Step 5 : Plug this menu item on the questions form as shown below:

We are done and ready to test drive this. Now on opening the Questions form we can see the transalations button . When we click it,system launches the translation helper form where we can store our translated texts using all standard AX functions as shown below:

You can also download the XPO from  HERE to have quick access to code.

So in this post we saw how we can make our solutions flexible and more intuitive by adding the translation texts capabilities to any existing or new entities.

Thanks for reading the blog.

An encounter with Microsoft Dynamics AX Office Add Ins error

Hi Friends,
Recently when configuring options for Microsoft Dynamics AX Office Add Ins, we were getting the below error: "Columns 'OfficeAddinAccountStructureView.ChartOfAccountsId' does not belong to table OfficeAddinAccountStructureView" 

The AOS was moved to a different machine for this client resulting in original installed Microsoft Dynamics AX client configuration to point to wrong AOS.

We tried creating a new client configuration pointing to right AOS/Refresh WCF/Generated full CIL but this also did not helped. So we needed to find a way to fix the AOS name in the default client configuration (which is uneditable from front end).

In order to change the default client configuration, we updated the corresponding registry value for that user. First check the user SID .I my case the SID was \S-1-5-21-861567501-1637723038-839522115-2627, then navigate to the registry editor at the below path:

HKEY_USERS\S-1-5-21-861567501-1637723038-839522115-2627\Software\Microsoft\Dynamics\6.0\Configuration\Original (installed configuration) 

Here we noticed, aos2 key was referring to old AOS. We changed the value of  aos2 key by editing the key. To do this, right click on the AOS2 key and select modify.

Enter the correct AOS, click OK 

Close the registry editor and then open the local client configuration: Notice that the AOS is pointing to correct machine.

After this we were able to configure the Dynamics AX add-ins options and able to carry out our further activities.

So from this encounter we assume* (as we could not find any documentation from Microsoft confirming this), Dynamics AX office Add Ins use default client configuration to first time connect and configure the options. 
Additionally, when we move AOS to different machine, the registry values might still point to old AOS and there can be some situations, like this,where it can cause trouble. 

PS --> Changing registry values is not recommended unless you have proper backup and understanding of how it works. The above approach helped me in the mentioned scenario. In case you are facing the same error then before making any changes in the registry please ensure you have proper backups are place.

AX2012 R3: Mashup - Computed columns + table inheritance in views

Hi Friends,
Dynamics AX has amazing technical offerings. The real benefit of such good features is leveraged when they are combined together to create an amazing output.

In this post, let me walk-through a scenario where we create a computed column whose logic is based on a bunch of inherited tables. A basic understanding of Table inheritance, Queries, Joins, Views, Computed columns and Product attributes will make this post easy to understand.

In standard AX we can create/define product attributes applicable on a product. Technically the attributes data is stored in a set of tables which use table inheritance. EcoResValue is the parent table of these tables as shown below:

All the attribute values of string types are stored in EcoResTextValue,  Integer values are stored in EcoResIntValue table and so on. Each child table stores it's relevant data in there relevant fields.

Goal : Create a view with a computed column to show the attribute values of different data types in a single text column.

There is a standard AX display method [\Data Dictionary\Tables\EcoResValue\Methods\getValueAsText()] which fetches the value from the respective child table. We will actually be simulating the same in our computed column code. In this method, switch is on the basis of InstanceRelationType and case on tableId. Value is returned from that respective table:

To do this we need to first model a query having all the above dataSources linked to parent table. So the root table will be EcoResValue and then all the other tables with the link type of outer join.

Once the query is created let's create a new view and add the above query as data-source.

Next we create a new method for the computed column field. In that we have to:

• Define the switch based on field Instance relation type from EcoResValue
• Cases will be on the tableID of each child table
• Return the value from the respective field of the child table.
• In case there are more then one field storing the values in the child table, then we concatenate those fields.
• We need to typecast the field values to string values
• Where units are used, we need to concatenate the unit text from the respective table.

In one of my previous post, I showed how to write Switch, Add and Cast methods using a SysComputedColumn class. It can be referred here.

Each value in the map is inserted for a particular tableID. Sample code to return that value from it's corresponding table is shown below:

We define the switch on InstanceRelationType field as shown below:

When all the switch cases are combined we get the below method:

Now lets add a computed string column field in the view and check out the computed values.

EcoResIntValue record value:

EcoResFloatValue record value :

EcoResDateTimeValue  record value: (Here I have not applied any time zone offset. Hence the time is different.)

EcoResCurrencyValue record value :

EcoResBooleanValue record:

We see that we have retrieved values from various tables of different data types, all coming together, in one column using the computed column concept. Now we can use this view throughout the system. Some possibilities:

• Effective use in reports. No need to write heavy joins in data provider class.
• Can be used to set-up document types.
• Can be used as a query and then extended outside AX using Dynamics AX Office AddIns.
• Can be used as display methods on forms where we want to show attribute values.

There are many more possible solutions where such views can be extended and can deliver awesomeness to customers using Dynamics AX. 

AX2012 R3 : Use of SysComputedColumns to build smart AX views

Hi Friends,
View is a powerful feature in AX which can benefit development and business modelling. Once created, you can reuse it without need for writing/modelling complex queries and joins again and again. Adding to it's beauty is the ability to create computed columns using X++ code. Now that's a deadly combo!!

Code for computed column depicts a SQL view query definition. Hence the way of writing X++ code inside a computed column method is bit different. To help us tackle this challenge and model the code effectively, Microsoft provides us with an out of the box class: SysComputedColumn. In this post, lets take a look at some of the basic operations which we can perform using this class.

Thumb rule is, the methods written for computed column computation should be declared as static and in order to access a field we need to use SysComputedColumn::returnField() method.

For example if we have to access the value of field CustGroup from CustGroup table, then a normal X++ code is custGroup.custGroup. But inside a computed column method we need to use the SysComputedColumn::returnField() method to access this field. This is shown later in this post.

Below is the returnField method definition. Note, it takes three parameters:

So lets play with CustGroup table and create a simple view out of it:

• Right click on AOT >> DataDictionary >> Views and create a new view
• Add datasource "CustGroup"
• Add 2 fields, CustGroup and PaymTermId

Browse the view just to make sure the values coming in the table browser.

Now let's create our first simple string computed column and return the field custGroup,just  to get a feel.

Create a new method in the view as shown below:


Now we add a new string computed column field in the view and link it to this method

To do this, Right click on the fields >> New >> String computed column

In the properties for this new field >> View method >> Select the method we created above

We are all set to see the glimpse of our first and very simple computed column which returns the custGroup.  

Now we will change the textValue method to explore other methods available in SyscomputedColumn class:

ADD : This method is used to add 2 string values. It accepts two string parameters. Modify the method to add the CustGroup and PaymtermId fields as below:


Let's open the view and check the way the strings have been added below:










IF: This method accepts three parameters as shown below and returns the equivalent SQL view design script

Let's create a condition: if the CustGroup is 10 then return the value of CustGroup , else return the value as 0. The modified method looks as below:

Lets open the view, notice that the value is shown only in the record having custGroup as "10" as per the condition:

SWITCH: Below is the syntax of the function. It accepts a Case expression , Map (for storing the values) and the default expression

Lets modify our method to Switch on the basis of CustGroup and populate a map based on the case condition.






Below is the code template:
public static server str textValue()
{
    tableName       viewName = tableStr(CustGroupView);
    Map             valueMap = new Map(Types::String , Types::String);
    valueMap.insert("10", SysComputedColumn::returnLiteral('Value 10'));
    valueMap.insert("20", SysComputedColumn::returnLiteral('Value 20'));
    valueMap.insert("30", SysComputedColumn::returnLiteral('Value 30'));
 
    return SysComputedColumn::switch(
        SysComputedColumn::returnField(viewName, identifierStr(CustGroup_1) , fieldStr(CustGroup,CustGroup)),
        valueMap,
        SysComputedColumn::returnLiteral('BadCase')
        );
}

CAST : This method is used for conversion between data types. Below is the syntax of the method:

 

Let's modify the method to return the Boolean value of the field PriceIncludeSalesTax as a string with the help of cast method. The code and the result set is shown below

This is just a sneak peek into the methods available in SysComputedClass.There are more methods in this class using which you can make powerful SQL views using X++ and deliver amazing customer experiences.

More interesting references on the using SysComputedColumns :
1. MSDN
2. Joris de Gruyter's blog
3. Martin Drab's Blog
4. Krishna Reddy's Blog