UtcDateTime in Dynamics AX 2009

In Dynamics AX 2009, Microsoft introduced a new data type, UtcDateTime, that is going to eventually replace the 2 existing types, Date and Time, which are still present in the application right now.
Obviously, the introduction of this new type requires a tutorial on how it can be used on forms, how you can filter on fields of this type, as well as what functions are available out of the box for it.
So I have made such a tutorial, and I hope it will be useful for developers upgrading to AX 2009.

Download the xpo for the tutorial from my SkyDrive

The tutorial consists of a single form, containing the following elements:

• a grid, displaying data from CustTable
• 4 buttons for various filtering actions
• 3 controls that allow specifying the filtering conditions for the data

. In the form, you can see how UtcDateTime based controls are displayed both in a regular group and in a grid.



Below is an explanation of the implemented functionality, in form of a Question/Answer section:

1. Q: Can I filter on the new UtcDateTime type, specifying the Date part only?
   A: Yes. You simply have to specify only the date part when applying the filter, like below. Note, that this also works fine when filtering directly from the UI (Ctrl+F).
   

   qbdsCustTable.addRange(fieldNum(CustTable, CreatedDateTime)).value(queryValue(DateFilter.dateValue()));

   What is interesting is how the kernel processes this range. In the below infolog, you can see that when viewing the query, it displays a "==" condition on a specific dateTime value.
   But in reality, as you can see from the SQL trace, a range ">= && <=" condition is applied to span exactly one day.
   Also note, that the values in the trace are displayed accounting for the TimeZone I am in, as well as for Daylight Saving Time
   
   
2. Q: Can I filter on the new UtcDateTime type, specifying the Time part only?
   A: No, this is not possible with a UtcDateTime type. The range applied when specifying a Time value is the minimum DateTime value, as seen below. Note, that in the SQL trace it is converted to "no range".
   
   
3. Q: Can I use similar query functions for UtcDateTime type?
   A: Yes. All the main existing functions for working with QueryBuildRange also support UtcDateTime. For example, in the infolog below you can see how a range on 2 UtcDateTime dates is applied. Global::queryRange method was used to achieve that. Note, again, that the SQL trace offsets the DateTime by the appropriate number of hours based on my location.
   
   
4. Q: How is the UtcDateTime stored in the database? Is it displayed the same way on forms?
   A: The UtcDateTime fields are in the database always stored in Coordinated Universal time (UTC). Whenever displayed on forms and bound to table fields, the data is converted to the user's preferred timezone. Note, that you need to take care of the conversion yourself, if the control is not bound to a field. For an example, see the init method of the tutorial form.
5. Q: What standard helper functions are present for working with UtcDateTime type in the application?
   A: The main entry point for working with UtcDateTime type is the DateTimeUtil class. It allows adding Days/Months/Years, as well as applying an offset, getting the user's preffered timezone, converting from/to other types, etc. An example from the form init method is posted below:
   

       // getSystemDateTime() returns the current DateTime set in the system, not the current machine dateTime.
       // Note that getSystemDateTime() returns a UTC date and time, not your local date time.
       // In order to receive your local DateTime value, you should use methods applyTimeZoneOffset and specify the preferred time zone.
       utcDateTimeFilter.dateTimeValue(
           DateTimeUtil::applyTimeZoneOffset(
               DateTimeUtil::getSystemDateTime(),
               DateTimeUtil::getUserPreferredTimeZone()));

6. Q: Does this mean that the support for Date and Time types has been removed?
   A: No, Date and Time are still supported. As you can see in the form init method, SystemDateGet(), timeNow(), today() are all still supported
7. Q: I don't see the actual filter values in the SQL log. Instead, all I see are "?"'s. Also, how can I limit the number of data/fields selected from the database?
   A: This is just some extra stuff, not related to UtcDateTime, but still useful to know and pay attention to.
   CustTable has a very large number of fields, and I am only displaying 4 of those in the form, so it would be unwise to always query and return all of the fields. Luckily, the datasource has a property OnlyFetchActive, which controls the query behavior by only selecting the fields actually displayed on the form. Note, that you should avoid using this with editable datasources. See comments to this post for details
   As for "?"'s in the SQL trace - that is happening due to the use of placeholders. This in general optimizes the performance of the queries, by creating a query execution plan and storing it for future use. But it is possible, and is required in some specific cases, to force the use of literals (meaning the actual values of the ranges in the query). This can be done using the literals method on the query. See method init on the form for an example.

Performance optimization: Deleting inventory journal lines

Preamble

As a developer, you should always consider performance implications of the code you write. In an ERP application like Microsoft Dynamics AX, the main focus should be on query execution, since it takes up the overwhelming part of the servers' resources.

You should always write queries that would execute the minimum amount of time and use the minimum amount of resources, at the same time producing the expected output in all cases.

Note, that performance is one of those things you cannot really verify on a 1-box install with a small test dataset. Most query problems show up only when tested with many users concurrently loading the AOS on a large-size database.

Code example

Table method
AOT\Data Dictionary\Tables\InventJournalTrans\Methods\delete
contains the following code:

    if (this.Voucher)
    {
        if (this.numOfVoucherLines() == 0)
            JournalError::deleteVoucher(tablenum(InventJournalTable),this.JournalId,this.Voucher);
    }

The code is logically correct, deleting related records from the JournalError table, which contains error messages generated during validation and posting of journals.
But now let us consider the actual implementation. If we rephrase the conditions under which we delete the error message history, it would sound something like:

If a Voucher number is specified on the line being deleted, and there is no more lines in this journal that use this Voucher number, then we remove the JournalError records.

Deeper code analysis for performance

So, let's go in deeper, and open the code for method numOfVoucherLines:

Integer numOfVoucherLines()
{
    return any2int((select count(RecId) from inventJournalTrans
                      where inventJournalTrans.JournalId == this.JournalId &&
                            inventJournalTrans.Voucher   == this.Voucher).RecId);
}

As you can see, it counts all the journal lines with the specified Voucher number and JournalId.
Does it generate the expected output? Yes.
Is it optimal for the scenario in which it is used? No.

In order to determine if JournalError records can be deleted, we only need to know if at least 1 record with the specified Voucher number exists. So why do we need to search and count all such records? We don't.
Note that this code is being executed for each journal line, magnifying the impact of a non-optimal query N-fold.

Suggested solution

So, if we create a new method like this:

boolean voucherLineExist()
{
    return (select firstfast firstonly forceplaceholders recId from inventJournalTrans
            index hint VoucherIdx
            where inventJournalTrans.journalId == this.journalId &&
                  inventJournalTrans.voucher   == this.voucher).recId != 0;
}

and use it instead of numOfVoucherLines() in delete() method, we will greatly improve the overall performance when deleting journal lines.
It has been measured, that the time it takes to delete a journal with a large number of lines (88000, to be specific) has reduced by 6 times, which is fantastic.

Notes

• forceplaceholders is used in the query explicitly to make sure a query plan is created and reused for this query, which further improves the performance, since the Voucher number most probably changes from line to line (depends on related journal name setup).
• It is generally still a best practice to avoid creating inventory journals with such a large number of rows, because the journal is usually processed in 1 database transaction.

See also

• Link to AxForum, where this problem was originally reported and solution suggested.
• Link to Microsoft Dynamics AX Performance team blog, containing useful information about performance tuning and tools

Lookup form returning more than one value

I have been posted with the question of creating a lookup that would return more than 1 value into the calling record, filling in a number of fields and populating the selected control with the corresponding value at the same time.

I will try to answer that question by an example from the standard application, particularly using one of the forms owned by my (Inventory) team.

Example

If we take a look at the lookup form for one of the item inventory dimensions (Configuration, Size or Color), we'll see the following tab page on it:
(assuming more than 1 item dimension is enabled for this item)



Basically, the user is able to select the configuration that belongs to a specific dimension combination. Note the check-box "" under the grid. If the user sets that check-box and then selects the configuration from a combination, the values for all item dimensions will be copied to the record from the parent form.
So, basically, the lookup returns more than just the configuration dimension value, but also size and color.

How is that implemented?

The lookup is implemented using a custom form, present in AOT, with name ConfigIdLookup.

The methods important for a working lookup are:

• init() method.

In particular, the way the calling control is determined based on the args passed into the form:

callerControl = SysTableLookup::getCallerStringControl(element.args());

• selectMode() method.

(called from setSelectMode() on this form). This method accepts an AX form control as parameter, and informs the kernel about which control should be used as the returning control.

• closeSelect() method.

This method is executed on lookup forms wheneven a selection of a particular value is made. So, obviously, in this method you still have access you all the fields of the datasource with the selected record, as well as to the calling form through element.args(). Here is the code that handles the update of multiple fields:

    if (ctrlTabPageCombination.visible() &&
        selectAllCombi &&
        ctrlTabPageCombination.isActivePage())
    {
        // Genericly determine the InventDim datasource on the calling form
        callerInventDimDS = inventDimFormSetup.callerInventDimFormDatasource();
        if (callerInventDimDS)
        {
        //...
            // Get the current record of that datasource
            callerInventDim = callerInventDimDS.cursor();
            // Set the needed fields on the record based on the selection in the lookup form
            // Basically, this is how you can achieve returning more than one value from a lookup
            // Get the record where the values need to be put in, and put them in from the currently selected record
            if (inventDimCombination_ConfigId.visible() && inventDimCombination.ConfigId)
                callerInventDim.ConfigId = inventDimCombination.ConfigId;
            if (inventDimCombination_InventSizeId.visible() && inventDimCombination.InventSizeId)
                callerInventDim.InventSizeId = inventDimCombination.InventSizeId;
            if (inventDimCombination_InventColorId.visible() && inventDimCombination.InventColorId)
                callerInventDim.InventColorId = inventDimCombination.InventColorId;
            // Refresh the datasource so that values are displayed on the calling form
            callerInventDimDS.refresh();
        }   
    }

If you want to know more

• Spend some time investigating the existing methods on SysTableLookup class, like the method getCallerStringControl, filterLookupPreRun_DS, filterLookupPostRun.


• Look at the methods in InventDimCtrl_Frm_Lookup class. They handle all the logic of looking up inventory dimensions, and contain some nice examples of traversing the calling object.


• Investigate, how the lookup form described above prevents the closing of the form when the check-boxes are clicked or tab pages changed. Hint: take a look at the usage of canSelect form variable

Short posts about AX every day

OK, so here is another one of those "A new blogger in town" posts.

But I couldn't resist, since I really like the idea of posting some little useful things about AX on a daily basis (weekends excluded, thank God!)

So I suggest all of you go and check it out for yourself.

The link is:
http://axdaily.blogspot.com/

Run AX as a different user from Windows Explorer

This is something useful for people who have already switched to Windows Server 2008 or Windows Vista / Windows 7.
As you have probably noticed, the Run as... command is not available in these versions of Windows any longer (it's still available from command line, of course, but that's not as user friendly).

As AX users/developers/etc., we all ofter have to log into AX as a different user, for example to verify security settings for a particular AX role being setup.
Having Run as... command in the context menu really saves time here.

So, Microsoft, namely Mark Russinovich, provided a way to return this useful command back into the standard context menu.

You can download and install ShellRunas from technet.

Now, to install it, simply follow the easy instructions below:

1. Download and unzip ShellRunas by following the link above
2. Copy ShellRunas.exe to your Windows\System32 folder
3. Open a Command Line and run the following command: shellrunas /reg
4. A message box confirming successfull installation should pop up. Click OK
5. Now, holding down the SHIFT key, right-click the AX icon. You will see a Run as different user... item in the context menu

To uninstall the Shellrunas utility, simply execute the following command from a command prompt: shellrunas /unreg