onsdag den 23. april 2014

Dynamics AX 2012 ValidTimeState tables and form changing view from current to all

Valid Time State tables are new i AX 2012 a gives the developer the possibility to easily create tables that hold e.g. current setup data for various purposes, and at the same time keeping a "history" of the changes of the data in the table.

For more reading:
http://msdn.microsoft.com/en-us/library/gg861781.aspx


I was tasked with doing a setup table with rates for calculating Vendor Bonus and I chose to base this a valid time state table.

The customer asked for a button on the form, where you maintain the vendor bonus calculation setup data, so you could toggle viewing "Current setup" or "All setup" records (changing the view from actual to all records and vice versa in the form).

I found that you can not change the ValidTimeStateAutoQuery property on the form data source in a form at run-time. It simply does not change anything, so I came up with the following solution:
A boolean class member in the classdeclation method of the form:

boolean showCurrent;

containing which "kind" of view is currently used in the form.

A button that changes the view and on the clicked method of the button:

void clicked()
{
    super();
    showCurrent = !ShowCurrent;
    this.text(showCurrent ? "@SYS38980" : "@NDI892");
    this.helpText(showCurrent ? "@NDI893" : "@NDI894");
    VendorBonusTable_ds.executeQuery();
}

And finally on the executeQuery method on the datasource using the valid time state table:

public void executeQuery()
{
    if (showCurrent)
    {
        VendorBonusTable_ds.query().validTimeStateDateRange(systemDateGet(),systemDateGet());
    }
    else
    {
        VendorBonusTable_ds.query().validTimeStateDateRange(dateNull(),maxDate());
    }
    super();
}


I encountered a strange little quirk when I was testing using jobs that made lookups on my table. Microsoft has introduced a keyword for the select command called validTimeState which is used for specifying on which date you want to select records from the table.

Supposing you have a valid time state table called VendorBonusTable and you write a piece of code that get records on a specific date, you can not use:

VendorBonusTable v;

select validTimeState(systemDateGet()) v;

This will simply not compile.

But you can however do:

VendorBonusTable v;
date d = systemDateGet();
select validTimeState(d) v;

which I personally think is a bit weird.