daxmusings: Valid Time State/Date Effective Framework - Part2

[Blog bot]

Источник: https://community.dynamics.com/produ...ork-part2.aspx
==============

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)); }}

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)); }}

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)); }}

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)

There are similar methods for UTCDateTime type:

query.ValidTimeStateAsOfDatetime(rateDate)query.ValidTimeStateDateTimeRange(fromDate, toDate)

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)); } }}
The query dialog that now comes up, will automatically have an extra tab after Range and Sorting, called "Date Options".

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.





Источник: https://community.dynamics.com/produ...ork-part2.aspx

[S.Kuskov]

Интересно.

Quote:

First thing to know is that, AX will by default, without any special keywords, only select the records valid for the current time

Т.е. если у меня уже есть функционал,который не знает/не умеет хранить/использовать настройки, изменяющиеся во времени, то я могу просто на таблице параметров включить данный механизм, ввести в таблицу новые значения параметров, как действующие лишь с первого числа следующего месяца. И спокойно сидеть на попе. А в час икс карета превратиться в тыкву автоматически? Прям, машина времени, какая-то

[S.Kuskov]

И ещё интересно есть ли специальный синтаксис выбора сразу всех параметров, без ограничения по периоду действия. Или если такая необходимость вдруг появится прийдётся писать что-то вроде validTimeState(dateNull(), maxDate()) ?

[belugin]

http://msdn.microsoft.com/en-us/library/gg843767.aspx

static void SelectVtsJob10(Args _args)
{
    TabEmplProjVts xEP;
    date dateFrom, dateTo;
    str strTemp;

    // Specify the largest possible date range
    // so that all ValidFrom value qualify.
    // Here 1\1\1900 is not treated as null by validTimeState().
    dateFrom = 01\01\1900;
    dateTo   = 31\12\2154;

    Global::info("Will display rows, if any exist.");

    While SELECT
        validTimeState(dateFrom, dateTo)
        * from xEP
        order by xEP.EmplID, xEP.ProjID, xEP.ValidFrom
    {
        strTemp = xEP.EmplID
            + " , " + xEP.ProjID
            + " ;  " + date2str(xEP.ValidFrom, 321, 2,3,2,3,4)
            + " -- " + date2str(xEP.ValidTo, 321, 2,3,2,3,4)
            + " ;  " + xEP.EPPrimaryKey;
        info(strTemp);
    }
}

[S.Kuskov]

А конструкций update_recordset и delete_from это тоже касается? Т.е. по умолчанию delete_from без дополнительных параметров удаляет только действующие записи?

И ещё один вопрос. Можно ли в качестве параметров для validTimeState использовать значения из текущего или приджойненного курсора? Т.е. как-то так:

select Trans
join validTimeState(Trans.TransDate) Parameters

?

[belugin]

про update там же в конце. про джоин надо экспериментировать - с ходу не скажу

[S.Kuskov]

Quote:

Originally Posted by belugin

про update там же в конце. про джоин надо экспериментировать - с ходу не скажу

На сколько я смог понять, там про то как будут скорректированы значения ValidFrom и ValidTo, в соседних записях, что тоже само по себе интересно.