How EntireTable cache works in AX2012 R3

The description of EntireTable cache on msdn seems ambiguous. On the one hand, all the records in the table are placed in the cache after the first select. On the other hand, the SELECT statement WHERE clause must include equality tests on all fields of the unique index. Let's run several tests to analyse it.

Problem description
Analyse how EntireTable cache works by tracing T-SQL statements and using wasCached method.

Hints
Run select statements with and without where clause on server and client.

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.

Statistics on CacheLookup property in AX 2012 R3

I got inspired by msdn blog post to collect statistics on CacheLookup property in standard AX 2012 R3.

Problem description
Actually, there is no problem in this post, just some entertainment.

Solution
I analysed CacheLookup property on all tables in standard AX 2012 R3 excluding temporary tables and derived tables (CacheLookup property can't be set on a derived table).

The distribution of CacheLookup property is presented below

The majority of tables have Found and NotInTTS. Caching is not enabled on 20% of tables or 1226 tables - there is room for improvement.

When is it best to use Table::find().Field or select Field from Table?

In this post I am going to analyse the question and define rules for each option.

Problem description
On the one hand, it is a common best practice to select only required information from a database, but quite often an entire record is fetched from the database, even though only one field is actually used. It can be compared to driving a car always on the 1st gear - the engine is fully used, but the car cannot drive fast.
On the other hand, there is a static find method design pattern - the method must be used whenever a record is selected by its key. Find method returns the entire record. Is it contradictory to the common best practice? Let's find out.

Select statement on field

A field select is a special select statement in X++. The description is available on msdn, but what is actually executed on SQL Server? Can the performance be improved? Let's analyse it.

Hints
Run field select statement on several tables and track actual T-SQL statements in SQL Server Profiler. You can also jump to the conclusion section.

How to restrict view datasource fields

A view is specified by a query and there are two ways to define the view query - add existing query or add datasources to Metadata node:

Both options deal with datasources. It is recommended to restrict datasource fields and return only the fields which are actually used. Unused fields generate useless traffic and decrease performance. Is such recommendation relevant for the view Metadata?

Hints
Analyse the view CREATE script in SQL Server Management Studio. You can also jump to the conclusion section.