Basics of working with date effective framework in AX 2012 – Part 3

The difference between querying normal and date effective table via X++ select statement is ValidTimeState keyword added to a query, which allows to fetch record effective on a specific date or fetch all record effective in period of time.

• Fetch all record within a period from dateFrom to dateTo (AsOfDateRange mode):
  select firstOnly  validTimeState(dateFrom, dateTo) myExampleTable;
  
• Fetch record that will be effective on dateCurrent (AsOfDate mode):
  select firstOnly  validTimeState(dateCurrent) myExampleTable;
  

If we don't add any keyword it will work in Default mode, which will search for records active on current date. Dates in brackets has to be type of ValidFrom and ValidTo fields - UtcDateTime or Date.

Example 1

For table that I was using in previous part of blog I will use both type of validTimeState:

First using AsOfDateRange mode in range from 16/06 to 12/10:

void recordTwoValidFromChange()

{

    MyTestTable testTable;

    date        dateFrom, dateTo;

    dateFrom    = str2Date("16/06/2015", 123);

    dateTo      = str2Date("12/10/2015", 123);

    while select validTimeState(dateFrom, dateTo) testTable

    {

        info(strFmt("RecId: %1", testTable.RecId));    

    }

}

In result we receive 3 records that are within the given period:

Now using AsOfDate mode for a 16/08 date:

void recordTwoValidFromChange()

{

  MyTestTable testTable;

    date        dateFrom, dateTo;

  dateFrom    = str2Date("16/08/2015", 123);

    while select validTimeState(dateFrom) testTable

    {

        info(strFmt("RecId: %1", testTable.RecId));    

    }

   }

In the result we can see only one record, which is active in a given date:

For fetching data with query, four new methods were added in order to achieve the same functionality:

• Query::ValidTimeStateAsOfDate(asOfDate)
• Query::ValidTimeStateDateRange(fromdate, todate)
• Query::ValidTimeStateAsOfDatetime(asOfdatetime)
• Query::ValidTimeStateDateTimeRange(fromdatetime, todatetime)

First two of this methods are used for Date type values and two next are for UtcDateTime values. As it easy to spot, the methods are equivalents for the AsOfDate and AsOfDateRange modes from X++ select statements.

Example 2: Statements used in query

In this example I will repeat search from previous example but this time I will use query methods. First for range from 16/06 –12/10:

void recordTwoValidFromChange()

{

    Query                   query;

    QueryBuildDataSource    qbds;

    QueryRun                qr;

    MyTestTable             testTable;

    date                    dateFrom, dateTo;

    dateFrom    = str2Date("16/06/2015", 123);

    dateTo      = str2Date("12/10/2015", 123);

  query       = new query();

    qbds        = query.addDataSource(tableNum(MyTestTable));

    query.validTimeStateDateRange(dateFrom, dateTo);

    qr          = new QueryRun(query);

    while (qr.next())

    {

        testTable = qr.get(tableNum(MyTestTable));

        info(strFmt("RecId: %1", testTable.RecId));    

    }

}

As we can see the result is perfectly the same as for X++ select statement used before.

And now for AsOfDate method:

void recordTwoValidFromChange()

{

    Query                   query;

    QueryBuildDataSource    qbds;

  QueryRun                qr;

  MyTestTable             testTable;

    date                    dateFrom, dateTo;

    dateFrom    = str2Date("16/08/2015", 123);

    query       = new query();

    qbds        = query.addDataSource(tableNum(MyTestTable));

    query.validTimeStateAsOfDate(dateFrom);

    qr          = new QueryRun(query);

    while (qr.next())

    {

        testTable = qr.get(tableNum(MyTestTable));

        info(strFmt("RecId: %1", testTable.RecId));    

    }

   }

Again we can see that the result is exactly the same as with an X++ select statement:

There is no way to use AOT query with AsOfDate or AsOfDateRange although this can be managed programmatically or by SysQueryForm.

Date effective table can be used as a part of a view. If it is, by default it fetches all records from this table. But it can be changed by setting ValidTimeStateEnabled property and view fields contain ValidFrom and ValidTo fields of the valid time state table in the view data source. Then current records are returned.

In fourth and last part I will write about using valid time state tables in forms development.