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: