In the Part 1 of this article, we went through creating a new table with a valid time state key. You saw how it protects from date overlap and closes gaps automatically. In this article, we’ll see how easy it is to query the table to retrieve the valid record for a given time-frame.

First thing to know is that, AX will by default, without any special keywords, only select the records valid for the current time. So, if we select for our RateID of “DaxMusings” which we created records for in the previous article, we expect to only see one record returned. And that is what happens:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    
    while select rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

Your infolog should only output 1 record, regardless of how many records you have in your table. Basically, the system attaches the date ranges with today’s date to the where clause of your query automatically. So how do we query for a different date than today? Using the ValidTimeState keyword:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    date        rateDate = systemDateGet() + 1;
    
    while select validTimeState(rateDate) rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

This will still only give you 1 result in the infolog. There is one way to get multiple records from this query, and that is by quering for a date range. In our example from yesterday, we added a rate for today and one for tomorrow. So if we query for a date range between today and tomorrow, we should get both records, as such:

static void ValidTimeStateTest(Args _args)
{
    RateTable   rateTable;
    date        fromDate = systemDateGet(), toDate = systemDateGet() + 1;
    
    while select validTimeState(fromDate, toDate) rateTable
        where rateTable.RateID == 'DAXMusings'
    {
        info(strFmt("%1: %2 - %3",
            rateTable.RateID,
            rateTable.ValidFrom,
            rateTable.ValidTo));
    }
}

</code>

In these examples we’ve been using the Date field type (property on table - see previous article). The same statements will work for the UTCDateTime type, the compiler will check at compile time that the type you’re using for the validTimeSate keyword matches the setting on the table. Note that for UTCDateTime, AX will take into account the timezone of the currently logged in user.

All of these features are available in the query objects as well. By default, the query will behave the same way in that it will automatically filter on the valid time state of today’s date. Same as with the select statement, you can override this behavior with an as-of date or a date range, by setting the options on the query object:

query.ValidTimeStateAsOfDate(rateDate)

query.ValidTimeStateDateRange(fromDate, toDate)

</code>

There are similar methods for UTCDateTime type:

query.ValidTimeStateAsOfDatetime(rateDate)

query.ValidTimeStateDateTimeRange(fromDate, toDate)

</code>

So to re-write the job from earlier to use the query and queryRun objects, your code should look something like this:

static void ValidTimeStateTest(Args _args)
{
    Query       query;
    QueryRun    queryRun;
    RateTable   rateTable;
    date        fromDate = systemDateGet(), toDate = systemDateGet() + 1;

    query = new Query();
    query.addDataSource(tableNum(RateTable)).addRange(fieldNum(RateTable, RateID)).value(queryValue('DAXMusings'));

    query.validTimeStateDateRange(fromDate, toDate);

    queryRun = new QueryRun(query);
    
    if(queryRun.prompt())
    {
        while(queryRun.next())
        {
            rateTable = queryRun.getNo(1);
            info(strFmt("%1: %2 - %3",
                rateTable.RateID,
                rateTable.ValidFrom,
                rateTable.ValidTo));
        }
    }
}

</code> The query dialog that now comes up, will automatically have an extra tab after Range and Sorting, called “Date Options”.

<a href="

Here you can change the date ranges used, or flip to as-of date selection, on top of your usual filters. If you want the one active record, your job should look like this:

static void ValidTimeStateTest(Args _args)
{
    Query       query;
    QueryRun    queryRun;
    RateTable   rateTable;
    date        rateDate = systemDateGet();

    query = new Query();
    query.addDataSource(tableNum(RateTable)).addRange(fieldNum(RateTable, RateID)).value(queryValue('DAXMusings'));

    query.validTimeStateAsOfDate(rateDate);

    queryRun = new QueryRun(query);
    
    if(queryRun.prompt())
    {
        while(queryRun.next())
        {
            rateTable = queryRun.getNo(1);
            info(strFmt("%1: %2 - %3",
                rateTable.RateID,
                rateTable.ValidFrom,
                rateTable.ValidTo));
        }
    }
}

So notice how the query dialog shows both option (as-of as well as date range) so you can flip between the two, you basically provide a "default" in your query object (just like ranges, sorts etc). That's it for querying. Next article, we'll look at UI (Forms) and how they behave with the date effective framework.

There is no comment section here, but I would love to hear your thoughts! Get in touch!