Bug: SysFormEnumComboBox selection method returns wrong value

The SysFormEnumComboBox class is very useful when only some of enum values must be enabled in a form, but it has a bug.

Problem description
The SysFormEnumComboBox.selection() method returns correct value only when being called after super() of form run method.
Test form is attached.
In form init method SysFormEnumComboBox is initialised with 3 enum values, Invoiced value is set as default and selection method is called:

In form run method selection method is called before and after super():

The results are:
init method, after super, SalesStatus selection is 255.
run method, before super, SalesStatus selection is 255.
run method, after super, SalesStatus selection is 3.

Hints
It appeared that FormComboBoxControl texts are set in form run method. Alternative approach must be used to get enum selection.

Top overridden form methods in AX 2012 R3

Today I am going to analyse form and datasource methods in standard AX application and find the most and the least overridden.

Form
Overridden rate is calculated as the ratio of the number of forms, where a method is overridden, to the total number of forms.

What does the diagram say?
Not surprisingly, init method is almost always overridden. run, close and closeOk methods are often overridden. All other methods are rarely overridden.
The following methods are never overridden in standard application:
addHistory
blockPersonalization
closedCancel
controlMethodOverloadObject
copy
getActiveWorkflowConfiguration
getActiveWorkflowTrackingStatus
getActiveWorkflowWorkItem
initWorkflowControls
loadUserSetting
reload
send
setApply
skipSaveUserSetting
updateWorkflowControls

Datasource
Overridden rate is calculated as the ratio of the number of datasources, where a method is overridden, to the total number of datasources.

Top overridden methods are:
init, active, executeQuery, write, validateWrite, initValue, delete, linkActive, create.
All other methods are rarely overridden.
The following methods are never overridden in standard application:
defaultMark
findValue
markAllLoadedRecords
refreshEx

Conclusion
This standard application statistics reflects what is usually overridden by a developer on a regular project:
- init, run, close methods on a form
- init, active, executeQuery, write methods on a form datasource.

Specifics of update_recordset crossCompany in AX 2012

Have you ever tried to run update_recordset crossCompany statement?
Yes, it is possible. There are only 5 methods in standard AX code with such statement, but it can be very useful for data update jobs in multi company environment.

Tables:
TaxTransGeneralJournalAccountEntry.moveTaxForeignKeyToTaxTrans()
RetailLoyaltyConflictCard.migrateConflictCards()

Classes:
ReqDemPlanForecastChangeTracker.applyAllChanges()
ReleaseUpdateDB63_HRMMinor.updatePositionForecastBudgetAcctLine()
ReleaseUpdateDB63_HRMMinor.updatePositionForecastCompGroupRefPoint()

You must disable update method, database log and alerts, otherwise compiler will throw an error, for example:

transTable.skipDataMethods(true); transTable.skipDatabaseLog(true); transTable.skipEvents(true); update_recordSet crossCompany transTable setting loyaltyCardId = conflictCard.NewCardNumber where transTable.loyaltyCardId == conflictCard.CardNumber && transTable.dataAreaId == conflictCard.Company;

If you are still not convinced, then there is another example below.

What is wrong: crossCompany update job in AX 2012

I want to challenge you today to find an error in the following update job. Do not pay attention to the functional part. I agree, the job can be rewritten, but let's assume it must be done this way.

static void crossCompanyUpdate(Args _args) { CustTable custTable; CustGroup custGroup; PaymTermId oldPaymTermId = 'Net10', newPaymTermId = 'Net11'; ttsBegin; // find all companies with oldPaymTermId while select crossCompany custGroup group by custGroup.dataAreaId where custGroup.PaymTermId == oldPaymTermId { changeCompany(custGroup.dataAreaId) { // disable update method custTable.skipDataMethods(true); // set newPaymTermId update_recordSet crossCompany custTable setting PaymTermId = newPaymTermId where custTable.PaymTermId == oldPaymTermId; } } ttsCommit; }

Post your ideas into comments. I will open comments and post my solution in one week. Have fun :-)

Solution
There is no compile or runtime error, but records are updated only in one company. Table variable must be set to null within changeCompany statement, marked in orange below:

static void crossCompanyUpdate(Args _args) { CustTable custTable; CustGroup custGroup; PaymTermId oldPaymTermId = 'Net10', newPaymTermId = 'Net11'; ttsBegin; // find all companies with oldPaymTermId while select crossCompany custGroup group by custGroup.dataAreaId where custGroup.PaymTermId == oldPaymTermId { changeCompany(custGroup.dataAreaId) { custTable = null; // disable update method custTable.skipDataMethods(true); // set newPaymTermId update_recordSet crossCompany custTable setting PaymTermId = newPaymTermId where custTable.PaymTermId == oldPaymTermId; } } ttsCommit; }

Simply about applyTimeZoneOffset and removeTimeZoneOffset methods of DateTimeUtil

In this post I want to describe simple rules of working with an unbound UtcDateTimeEdit control in a form:
1) from the database to the user -> applyTimeZoneOffset
2) from the user to the database -> removeTimeZoneOffset
Continue reading for the details.

Problem description
Define how to set and store value of an unbound UtcDateTimeEdit control in a form.

Hints
Play with bound and unbound UtcDateTimeEdit controls in a form.

How to copy cross-reference from one environment to another

Cross-reference is a very useful tool in Dynamics AX, but cross-reference update requires a lot of time and resources. I want to share a simple way to copy cross-reference data.

Problem description
Copy cross-reference from one development environment to another, provided environments are identical.

Hints
Use bcp utility for export and import.

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.

Query datasource FirstOnly property

How does query datasource FirstOnly property work and influence SQL Server query? It seems obvious, but in fact it is not.

Problem description
Analyse how a query is translated into SQL Server query based on FirstOnly property.

Solution
Based on the description in msdn FirstOnly property is a hint for database that only one record is required. Let's run several tests to analyse how it actually works. You can also jump to the conclusion section.

Query datasource FetchMode property

FetchMode property sometimes was a magic solution for report development in Axapta 3.0. Since then I wanted to run several tests and describe the feature.

Problem description
Analyse how a query is translated into SQL Server query based on FetchMode property.

Solution
Based on the description in msdn FetchMode property is available on an embedded datasource and determines a relation between parent and child datasources.
FetchMode values are 1:1 and 1:n, but there is no description of them.
The property name and values can lead to an assumption:
1:1 - data for parent and child datasources is fetched simultaneously
1:n - data for parent and child datasources is fetched separately.
Running ahead I must say that the assumption is not precisely correct, but let's analyse it. You can also jump to the conclusion section.