How unique index join cache works

Unique index join caching is supported in AX 2012, however the description in msdn is not very clear in my opinion. Let's run several tests to find out the truth.

Problem description
Analyse how unique index join is cached by tracing T-SQL statements sent to MS SQL Server and using wasCached method.

Hints
Use the following code and trace RPC:Completed events in SQL Server Profiler.

static void TestJoinCache(Args _args) { CustTable custTable; CustGroup custGroup; select AccountNum, PaymMode from custTable join custGroup, PaymTermId from custGroup where custTable.AccountNum == "US-004" && custGroup.CustGroup == custTable.CustGroup; info(strFmt("CustTable from %1, CustGroup from %2", custTable.wasCached(), custGroup.wasCached())); }

Dynamics AX Trace Parser vs SQL Server Profiler

While I was working on the previous post, I noticed a strange difference between data presented in Microsoft Dynamics AX Trace Parser and SQL Server Profiler. Let's run a couple of tests.

Problem description
Trace the following select statements in Tracing Cockpit and SQL Server Profiler and compare the results:

select AccountNum from custTable; select firstOnly AccountNum from custTable;

The power of firstOnly keyword

Sometimes only one record from a table is required, but firstOnly keyword is not used by a developer. For example:

select AccountNum from custTable where custTable.CustGroup == '10'; if (custTable.AccountNum) { ... }

What difference does it make?

Problem description
Compare select statements with and without firstOnly keyword.

Hints
Use SQL Server Profiler for analysis and trace RPC:Starting and RPC:Completed events:

How to update a caller form when a new record is created in a separate form in AX 2012

Today I want to share a code sample in standard AX application to update a caller form.

Problem description
A form (caller) has a menu item to open a separate form (dialog) to create a new record. After the new record is created and the dialog form is closed, the caller form is updated and the new record is made the current one. What are the ways to achieve it?

Hints
Analyse \Forms\EcoResProductCreate\Methods\updateCallers method.

Top modified EDT properties in AX 2012 R3

Today I am going to analyse EDT properties in standard AX application and find the most and least modified.
Why? Just to have some fun. I also hope to find out something interesting based on the results.

Methodology description
Only properties that can be modified are taken into account. For each property 2 values are calculated:
- Total - number of EDTs where the property can be modified
- Modified - number of EDTs where the property has value different from default.
Modified rate is calculated as the ratio of Modified to Total.
Properties are sorted on Modified rate in a diagram.

Analysis
Modified rate of all properties is presented in the diagram below:

Let's analyse the results.

How to add enum filter with All element

Sometimes customers request a specific filter in a form. The filter is based on an enum and must have All element to display all records regardless of field value. For example, a filter has 3 elements: All, Quotation, Order; although enum has only 2 elements: Quotation, Order.

Problem description
Analyse how All element is added to an enum filter in standard AX.

Hints
To answer the question let's search for enums with All element and Filter suffix in standard AX.

How to make enum a mandatory field on a table

It seems obvious, just set Mandatory property on a field to Yes. Is it enough? No.

Problem description
Null value concept is important for a mandatory field, but null values are not supported in Dynamics AX. Instead there are default values for each data type and they are considered null.
For enum it is an element with value set to 0. An assumption can be made that it shouldn't be possible to select and save enum element with value 0 on mandatory field. MSDN post proves the assumption:
"when the validateField method checks whether a user has entered a value in a mandatory field ...
the first entry is not accepted in an enum type field".
In another MSDN post there is an alternative requirement:
"If you want to make an enum a mandatory field on a table, make the first outcome with the value zero, as none, with the label Not selected"
What is correct? Let's analyse. You can also jump to the conclusion section.