Tag Archives: Date effective framework

Date effective framework – Retrieve Data using Query

Introduction:

In order to cater the scenarios where organizations need to keep track of or analyze data which keeps changing over time, Date effective framework was introduced in Microsoft Dynamics AX 2012.

One of the main requirements of an organization could be to keep track of an exchange rate.  What is the exchange rate today? What was the exchange rate on the day a particular invoice was posted? What was the exchange rate a month ago? In order to cater such needs of an organization, Date effective framework is used which provides ease and consistency for date effective scenarios.

Terminology:

Term Definition
Valid Time State Table A table that tracks the records over time using the fields ValidFrom and ValidTo
Valid Time State Key An Alternate key that enforces the valid time state semantics
Gap A condition in which a record can have a gap between its’ certain date ranges
Overlap A condition where a record has more than one occurrence
Current Record A record effective at the present time
Past Record A record effective in an earlier time
Future Record A record effective in a future time
Create New Time Period Creates a new time period updating the date effective table and closing the initial record
Effective Based When updating a valid time state table in Effective Based mode, if the record being updated is a current record, the record is updated in CreateNewTimePeriod mode. If the record being updated is a future record, the record is updated in Correction mode. If the record is a past record, the record cannot be updated. 

Tutorial – Retrieve Data using Query

Retrieving Data:

Current Get current exchange rate, as in today’s exchange rate
Specific Date Get exchange rate of a specific date such as yesterday’s exchange rate
Date Range Get exchange rate active within the specified date range e.g. valid from January and valid till March

Date Effective framework Methods:

Microsoft Dynamics Ax has introduced some methods in the Query API for the Date effective framework. The methods are mentioned below:

Query Description ValidTimeStateFieldType
Query::ValidTimeStateAsOfDate(Date)  Query records that are effective at the present Date  Date 
Query::ValidTimeStateDateRange(fromdate, todate)  Query records that are effective during the date range: fromdate and todate  Date 
Query::ValidTimeStateAsOfDatetime(DateTime)  Query records that are effective at the present Date and Time  UtcDateTime 
Query::ValidTimeStateDateTimeRange(fromDateTime, toDateTime)  Query records that are effective during  the Datime and Time range:fromdatetime and todatetime

 

UtcDateTime 

 

The following code sample is used to query the data that is effective on 1/1/2014:

static void ValidTimeStateAsOfDate (Args _args)

{

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryRun queryRun;

Date currdate = 1\1\2014;

SL_ExchangeRate exchangeRate;

query = new Query();

queryBuildDataSource = query.addDataSource(tableName2id(“SL_ExchangeRate “));

query.ValidTimeStateAsOfDate(currdate);

queryRun = new QueryRun(query);

while (queryRun.next())

{

exchangeRate = queryRun.get(tableName2id(“SL_ExchangeRate “));

info(strFmt(“%1, %2, %3 %4″, ExchangeRate.CurrencyPair, ExchangeRate.exchangeRate, exchangeRate.ValidFrom, exchangeRate.ValidTo));

}

}

 

 

The following code samples is used to query the data that is effective during 2014-01-01T00:00:00 and 2014-01-31T00:00:00:

static void validTimeStateDateTimeRange (Args _args)

{

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryRun queryRun;

UtcDateTime fromDateTime = 2014-01-01T00:00:00;

utcDateTime toDateTime = 2014-01-31T00:00:00;

SL_ExchangeRate exchangeRate;

query = new Query();

queryBuildDataSource = query.addDataSource(tableName2id(“SL_ExchangeRate “));

query. validTimeStateDateTimeRange (fromDateTime, toDateTime);

queryRun = new QueryRun(query);

while (queryRun.next())

{

exchangeRate = queryRun.get(tableName2id(“SL_ExchangeRate “));

info(strFmt(“%1, %2, %3 %4″, ExchangeRate.CurrencyPair, ExchangeRate.exchangeRate, exchangeRate.ValidFrom, exchangeRate.ValidTo));

}

}

This was the second part of the date effective tutorial. If you are interested in reading the first part of the date effective framework tutorial, please follow the link: 

http://thedynamicsblog.com/date-effective-framework-creating-a-new-table-with-a-valid-time-state-key/

The final and the last part of the date effective framework tutorial will be posted shortly.

Date effective framework – Creating a new table with a valid time state key

Introduction:

In order to cater the scenarios where organizations need to keep track of or analyze data which keeps changing over time, Date effective framework was introduced in Microsoft Dynamics AX 2012.
One of the main requirements of an organization could be to keep track of an exchange rate. What is the exchange rate today? What was the exchange rate on the day a particular invoice was posted? What was the exchange rate a month ago? In order to cater such needs of an organization, Date effective framework is used which provides ease and consistency for date effective scenarios.

Terminology:
Term Definition

  • Valid Time State Table A table that tracks the records over time using the fields ValidFrom and ValidTo
  • Valid Time State Key An Alternate key that enforces the valid time state semantics
  • Gap A condition in which a record can have a gap between its’ certain date ranges
  • Overlap A condition where a record has more than one occurrence
  • Current Record A record effective at the present time
  • Past Record A record effective in an earlier time
  • Future Record A record effective in a future time
  • Create New Time Period Creates a new time period updating the date effective table and closing the initial record
  • Effective Based When updating a valid time state table in Effective Based mode, if the record being updated is a current record, the record is updated in CreateNewTimePeriod mode. If the record being updated is a future record, the record is updated in Correction mode. If the record is a past record, the record cannot be updated.

Tutorial – Creating a new table with a valid time state key:
Step 1:
Create a table “SL_ExchangeRate”. In order to make the table date effective, it’s property ValidTimeStateFieldType needs to be set.
Date vs. UTCDateTime

  • Date Considers Date only. It can only cater one record per day
  • UtcDateTime Considers Date and Time. It can cater multiple records per day

For this tutorial, the ValidTimeStateFieldType will be set to Date.

Setting ValidTimeStateFieldType  to Date
Setting ValidTimeStateFieldType to Date

After setting the property to Date, it can be noticed that two fields ValidTo and ValidFrom are automatically added to the fields of the table.
Step 2:
The scenario requires the exchange rate to change over time. In order to do so, two fields will be added to the table.
• CurrencyPair (The currency pair value such as USD and PKR)
• Exchange Rate
After adding, the table will have these 4 fields:

Final look of the date effective table
Final look of the date effective table

Step 3:
An Alternate key index “CurrencyPairIdx” needs to be created. The index will contain the field Validfrom and another field which is date effective. In this case, the currencypair is date effective, as its value keeps changing over time. The properties of the Index should be as follows:

Properties of index of date effective table
Properties of index of date effective table

Here, it can be noticed that ValidTimeStateMode property has been set to NoGap. The property can be set to two values:
Gap Gap is allowed in a date range
No Gap No Gap is allowed in a date range. If the exchange rate for a particular currency pair is valid from January and the next exchange rate value is valid from March, then the exchange rate value valid from January will be valid till February.

Outcome:
After setting up the table, let’s add few records in the table and analyze the outcome.

Adding records in date effective table
Adding records in date effective table

Let’s see what happens when another exchange rate is added for the Currency pair # 2.

By clicking on Yes, the Date effective framework automatically sets the ValidTo value for the first date range of the currency Pair # 2.

Adding another exchange rate in our date effective table
Adding another exchange rate in our date effective table

This was the first part of the date effective  framework. I will be posting the next tutorial of the date effective framework tomorrow! Happy Knowledge sharing :)