Tuesday, October 4, 2011

Valid Time State/Date Effective Framework - Part 1

AX 2012 features a new framework called the date effective framework, or valid time state tables. There are many easy examples of date effective data. The easy example is some sort of rate table, where rates become effective and expire at certain times. Other examples could be bills of material or revisions of items that become effective or expire at certain dates. In previous versions of AX, it was up to the developer to implement the logic for querying and validating the date ranges. AX 2012's date effective framework takes care of the ground work for you. In this article, we'll walk through the setup, and show you the automatic functionality that comes with it.

Date effective comes in two flavors. One uses regular date fields, the other UtcDateTime data type. Obviously, the UtcDateTime gives you more granularity to the second (and the nice timezone support that comes with UtcDatetime). For this example, we'll just stick with an easy example using regular dates.

First, we create a new table and we'll call it RateTable. We'll give the table a RateID field which identifies the different rates, and a price for each rate.


On the table's properties, we'll set the ValidTimeStateFieldType to "Date". This will automatically create two new date fields called "ValidFrom" and "ValidTo".


Next, we'll add an index on the table, containing our RateID identifier and the two date fields. The index needs to be unique, and set as an alternate key.


On the index' property sheet, we'll set ValidTimeStateKey to "Yes" and ValidTimeStateMode to "NoGap" (should default to NoGap when you set timestatekey to YES). The NoGap value tells the framework we do not allow gaps in the date ranges. For example, for the same RateID, NoGap will not allow one record January 1 to February 1 and a second record of March 1 to April 1, since there would be a gap between February 1 and March 1. We can easily test this once our table is set up.


That is pretty much it. Let's open the table browser by right-clicking and selecting "Open". Create a new record by hitting CTRL+N on your keyboard. Notice how the record defaults to ValidFrom with today's date, and ValidTo set to "never" (if you put your cursor on the field, you'll notice how the "never" value is actually "12/31/2154"). Give it a RateID of "DAXMusings" (yeah!) and save the record (CTRL+S).


Now, if you create another new record (CTRL+N), it will again default in the same date values. If you enter the RateID "DAXMusings" again and try to save the record (CTRL+S), you will get the error "Insert not supported with the values specified for 'Effective' and 'Expiration'. New record overlaps with multiple existing records".


So, it obviously doesn't allow this overlap of the same dates. So, change the ValidFrom field to TOMORROW's date, and save the record (CTRL+S).


If you click yes, you will notice your previously created record will be updated so that its ValidTo date will be changed from never to a date that connects to your new record (if you follow the example, your first record should now contain today's date in both ValidFrom and ValidTo fields).


That was pretty easy. Stay tuned, we'll look at how to query this table next.

5 comments:

  1. Next thing I do is I delete the second record. Will the first record change ValidTo to "Never"? No.

    Next example. I delete all records and start afresh:
    1. create a record valid from 1 October.
    2. create a record valid from 10 October. record 1 will get updated to be valid to 9 October.
    3. create a record valid from 5 October to 6 October. It won't allow you (why? because one record needs to be updated and one inserted and it's too complicated now?)

    I tried in RC and I still could create a gap in this situation by deleting a record in the middle, but now they seem to have fixed it.

    I seriously doubt this concept is right:
    - it's impossible to add your own implementation to deal with the behaviour I demonstrated in the example
    - good developer won't use it, because it's not hard to write proper (and manageable) code to deal with dates
    - not to mention the fact that if you want a real no-gap distribution, you need only one date: a start date.

    Start date Price
    01/Jan/1900 100.00
    12/Oct/2011 120.00
    17/Oct/2011 150.00

    What's the price at the TransDate?
    select firstonly record
    where record.startDate < TransDate
    order by StartDate desc

    And index contains one date instead of 2. And no gaps :)

    ReplyDelete
  2. can we update ValidTo field using select forupdate statement..??

    ReplyDelete
  3. Hi there,

    How is it possible to get the active as well as inactive records in a date range.... ?

    while select validTimeState(fromDate, toDate) rateTable
    where rateTable.RateID == 'DAXMusings'
    {
    info(strFmt("%1: %2 - %3",
    rateTable.RateID,
    rateTable.ValidFrom,
    rateTable.ValidTo));
    }

    It makes sense If I want to grab the active records during this date range, but what If I want active as well as inactive records?

    ReplyDelete
  4. Its a bit of an old question, but creating an index where the validfrom / to is across the entire table, so since adding another field in the index is the dataAreaId. However, the application crashes when updating the record (any other field) without any description apart form a .Net runtime error.
    Would you have any workarounds regarding this?

    ReplyDelete
    Replies
    1. If you can consistently reproduce this crash error you should report this to Microsoft support so they can fix it for everyone! Regardless of your scenario, a crash is never what was intended and should be handled properly.

      Delete