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.

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 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.

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.

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.

Party primary address non-covered cases

There is quite logical feature of party primary address in AX 2012, description of which I was not able to find on technet or msdn. Please find the details below.

Description
Only an address which is valid now and has no expiration can be set as Primary.

It sounds logical: primary address must be valid and usually has indefinite lifetime.
However, there are use cases which do not fit.

Case 1. New primary address in future
A party informs about a new primary address starting from next month. The new address can be added, but can not be marked as primary in future. It means the user must remember the case and set primary on the new address later when it becomes valid.

Case 2. Primary address expiration
A party informs about business suspension for a period required to move to a new location. The case is more theoretical, but it is not possible to change Effective or Expiration on primary address:

I believe it was a trade-off as such cases are rare and a solution is quite complicated. Address Effective and Expiration are defined in LogisticsPostalAddress table. Address Primary setting is defined in DirPartyLocation.IsPrimary and is duplicated in DirPartyTable.PrimaryAddressLocation.

Hints
I would like to suggest to discuss mentioned cases with customers, so everything is clear and there are no surprises.

How to create missing records in another company

Sometimes in multi-company environment it is required to create records which are missing in another company. The first intention can be to use notexists join and crossCompany keyword to find missing records. Such approach has a problem, which is described in the previous post.

Description
Let's assume that the goal is to create records of company DEMF which are missing in company USRT:

An advanced solution will handle a set of source companies and a set of destination companies.

Steps to reproduce
1. Switch to DEMF company
2. Note existing Buyer groups (Inventory and warehouse management > Setup > Inventory > Buyer groups)
3. Switch to USRT company
4. Note existing Buyer groups
5. Run job
6. Compare Buyer groups

Hints
Look for examples in standard AX by searching while select crossCompany text in methods. Continue reading for the solution.

Non-documented feature of cross-company keyword

My colleague asked my advise about cross-company query. The goal was to create missing records of customer specific table in another company. The query looked correct, however the tests failed.

Problem description
To make it simple let's assume that records of InventItemGroup table in company USMF must be created in company USRT (provided the records are missing in USRT company). It seems to be possible to use cross-company query like this:

InventItemGroup inventItemGroup, inventItemGroupJoin; while select crossCompany inventItemGroup where inventItemGroup.dataAreaId == 'USMF' notExists join inventItemGroupJoin where inventItemGroupJoin.dataAreaId == 'USRT' && inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId { // create new record in USRT company }

This approach is wrong though. Are you also curious why? Let's analyse some specifics of cross-company queries.

Steps to reproduce
The following job can be used for tests and analysis:

static void CrossCompanyTest(Args _args) { InventItemGroup inventItemGroup, inventItemGroupJoin; select generateonly forceLiterals crossCompany ItemGroupId from inventItemGroup where inventItemGroup.dataAreaId == 'USMF' notExists join inventItemGroupJoin where inventItemGroupJoin.dataAreaId == 'USRT' && inventItemGroupJoin.ItemGroupId == inventItemGroup.ItemGroupId; info(inventItemGroup.getSQLStatement()); }

Hints
Try to modify predicates of join and use Query class instead of select statement.

How unique index influences T-SQL statement

As it was mentioned in the previous post, T-SQL statement can contain additional fields which are not selected in X++ statement. Let's analyse on InventBatch table what exactly is added and why.

Problem description
Analyse how an unique index influences T-SQL statement.

Steps to reproduce
Run a test job:

static void Test_UniqueIndex_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); }

Hints
Change index properties on the table and re-run test job.

Mysterious 101090 in T-SQL statement

Sometimes Transact-SQL (T-SQL) statement sent form AOS to SQL Server contains mysterious 101090 in a field list, even though select statement in X++ does not contain it. Also curious why?

Problem description
Find out what is 101090 in T-SQL statement.

Steps to reproduce
Run job like:

static void Test_101090_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); }

Hints
Try to select different system fields.

How joins in X++ select statement are translated into T-SQL

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.

How to check if record in valid time state table is valid now

Working on data synchronisation with legacy system I faced interesting question in valid time state table area. Please find the details below.

Problem description
Create a method to check whether a record in valid time state table is valid now.

Steps to reproduce
1. Find or create new valid time state table
2. Create 3 records: valid in past, valid now, valid in future.
3. Test the method on each record

Hints
There are at least two solutions:
1. Try to select the same record from database. Kernel adds Date or DateTime ranges and nothing is returned if the record is not valid now.
2. Compare values of ValidFrom and ValidTo fields with now. Analyse and define now for Date and DateTime.

What Date and DateTime does kernel use for valid time state tables

I had to check whether a record in valid time state table is valid now and I got curious about what Date and DateTime values are actually used by kernel.  The answer seemed to be obvious, but in fact it was not. Please find the details below.

Problem description
There are several cases when kernel generates values for valid time state tables:
- ValidFrom field default value set by kernel when a new record is created
- ValidFrom and ValidTo ranges added by kernel into a select statement.
Let's analyse them.

Questions to analyse
1. Is session date and time used?
2. Is client machine date and time used?
3. Is user time zone used?

Hints
Create two valid time state tables with ValidTimeStateFieldType property set to Date and UtcDateTime. Try to change session date and time, client machine date and time, user time zone and compare default values of new record in different cases.