Querying Date Effective Tables in Microsoft Dynamics AX 2012

Querying Date Effective Tables in Microsoft Dynamics AX 2012

In Microsoft Dynamics AX 2012, valid time state tables (date effective tables) were introduced to help organizations keep track of data that changes over time. For example, currency exchange rates change over time and it is necessary to view exchange rate data at a specific point in time.

For this blog, the exchange rate table will be used as an example.

This blog discusses how developers can query data in date effective tables and examines the results when using forms, queries, views and X++ code. If not properly understood by the developer, incorrect or incomplete data will be returned when querying the database.

Exchange rate table

The exchange rate table holds exchange rate data between two (2) currencies. The standard Microsoft Dynamics AX form can show current, past and future the exchange rate data as show below.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 1

Creating custom forms

A custom screen was created to display the exchange rate table. The exchange rate table was used as a data source and a grid was added to display the data.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 2

The data that is returned only shows data that is current based on a current date of 1/2/2015. Prior and future exchange rates are excluded.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 3

The ExchangeRate datasource property ‘ValidTimeStateAutoQuery’ is used to control what data is returned.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 4

Change this to ‘DateRange’ and all records will be returned.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 5

Querying the data using a query

A custom query was created to query the ExchangeRate table.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 6

The following code is used to run this query:

static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    ;
    query = new query(queryStr(AXMExchangeRateQuery));
    
    queryRun = new QueryRun(query);
    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %1", ExchangeRate.ValidFrom, ExchangeRate));
    }
}

Running the query returns only current records.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 7

It is possible to change the behavior of the query results set to return data at a previous point in time, for example prior exchange rates.

The following code can be used to query data for different date ranges:

static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    FromDate        FromDate = mkDate(1,1,2014);
    ToDate          ToDate   = mkDate(1,6,2015);
    ;
    query = new query(queryStr(AXMExchangeRateQuery));
    query.validTimeStateDateRange(FromDate, ToDate);
    
    queryRun = new QueryRun(query);

    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}

Running the query returns the following data.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 8

Use the validateTimeStateASOfDate method to return data that is valid at a specific date. For example, if you want to know what the exchange rate should be for a transaction with a date of 3/7/2014, run the following code:

static void AXMExchangeRateQuery(Args _args)
{
    QueryRun        queryRun;
    ExchangeRate    ExchangeRate;
    TransDate       TransDate = mkDate(3,7,2014);
    ;
    query = new query(queryStr(AXMExchangeRateQuery));    
    query.validTimeStateAsOfDate(TransDate);
    
    queryRun = new QueryRun(query);

    
    while (queryRun.next())
    {
        ExchangeRate = queryRun.get(tableNum(ExchangeRate));  
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}

The following data is returned.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 9

Querying the data using x++

The following x++ code is used to access the data:

static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate ExchangeRate;
    ;
    while select ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}

The following data is returned, again only current data is returned with a current date of 1/2/2015. It is important to note this as not all records are returned from the databse, despite no filtering applied in the X++ while select statement.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 10

It is possible to return data as of a specific point in time using the validTimeState keyword.

The following code can be used to find data that is valid for a given data range:

static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate    ExchangeRate;
    FromDate        fromDate = mkDate(1,1,2014);
    ToDate          toDate = mkDate(1,1,2016);
    ;
    while select validTimeState(fromDate, toDate) ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}

The following data is returned. By changing the from and to dates, we can control the amount of data being returned.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 11

If you want to know what the exchange rate should be for a transaction with a data of 3/7/2014, run the following code:

static void AXMExchangeRateAccess(Args _args)
{
    ExchangeRate    ExchangeRate;
    TransDate       TransDate = mkDate(3,7,2014);
    
    while select validTimeState(TransDate) ExchangeRate
    {
        info(strFmt("%1 %2", ExchangeRate.ValidFrom, ExchangeRate.ExchangeRate));
    }
}

The following data is returned.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 12

Creating a view

Microsoft Dynamics AX Views can be created in the AOT to return data from data effective tables. This can be used to overcome issues when you need to return all data from a date effective table and cannot use the query object or X++ parameters to control the date effective options.

The following view was created in the AOT.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 13

Using a view in a form data source

A new form was created and the view above was used as datasource.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 14

The following records are shown, the date effective filters do not apply.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 15

Using a view in a query

A custom query was created to query the ExchangeRate table using a view.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 16

The following code is used to run this query:

static void AXMExchangeRateViewQuery(Args _args)
{
    queryRun                queryRun;
    AXMExchangeRateView     AXMExchangeRateView;
    ;
    queryRun = new queryRun(queryStr(AXMExchangeRateViewQuery));
    while (queryRun.next())
    {
        AXMExchangeRateView = queryRun.get(tableNum(AXMExchangeRateView));   
        
        info(strFmt("%1 %2", AXMExchangeRateView.ValidFrom, AXMExchangeRateView.ExchangeRate));
    }
}

Running the query returns all records, the date effective filters do not apply.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 17

Using a view in a x++ while select

The following X++ code is used is used to access data in the view:

static void AXMExchangeRateViewAccess(Args _args)
{
    AXMExchangeRateView AXMExchangeRateView;
    ;
    while select AXMExchangeRateView
    {          
        info(strFmt("%1 %2", AXMExchangeRateView.ValidFrom, AXMExchangeRateView.ExchangeRate));
    }
}

Running the code returns all records, the date effective filters do not apply.

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 18

Modifying the view properties

By default, using a view to display data form a date effective table disables the date effective search results, which is useful when it is necessary to always return all data. However, it is possible to enable the date effective parameters, by changing the view property below

Querying Date Effective Tables in Microsoft Dynamics AX 2012 screen shot 19

About Michael Oakes

Michael Oakes has created 3 entries.

Post A Comment

YOUR CAPTCHA HERE

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Subscribe to our mailing list

* indicates required
 

×
Learn More

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

CRM Test Form

×

Influence

All fields required

First name

Last name

Company

Your Email

Proposed Webinar Date & Time
AMPM

Your Message

×

Contact Us

All fields required
×

Contact Us Here! 

* indicates required

×

We want to hear your feedback

∗ indicates required

×

Let us keep you updated!

 ∗ indicates required

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Contact Us Here! 

* indicates required

×

Contact Us Here! 

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Book Your Time

×

                        RAMPUP SUBSCRIPTION PLANS

                    Choose a plan below, and one of our

              Support Consultants will contact you shortly.

×

                           PACKAGES START AT $4,999

                  Choose a package below, and one of our 

               Support Consultants will contact you shortly.

×

 Performance Review Details

 

Diagnostic Review of DyanmicsAX Server (AOS) & SQL performance and stability.

 

Written summary of findings and recommendations.

 

Up to 15 hours of issue resolution time to target your most immediate performance issues.

 

Review completed by our 100% onshore support team averaging more than 10+ years of DynamicsAX experience.

 

Get the help you need now, with no long-term commitment.

×

Functional/Department Help Pick 3

 

Help with up to 3 functional or department issues such as: invoicing, costing, closing the books, AR, AP, inventory, warehousing, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to define your issues and find recommended solutions.

 

Up to 20 additional hours of issue resolution time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

 

×

Reporting / BI Help

 

Choose up to 3 standard or custom reports for assistance.

 

Solve typical issues like: Report too slow, does not show what we need, need an additional field/column, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to troubleshoot report issues and recommend solutions.

 

Up to 20 additional hours of configuration or development time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

×

Please fill in the form to RSVP

 

×