AOT query for all currency exchange rates per company and month

(0) Share

Report

Posted on by Community Member

I'm quite new to this so this might be a really simple question. I was looking to put together a simple query for all exchange rates over time. When I look in the ExchangeRate table in AOT it looks like this holds the 3000+ rows that I would need, but when I put this in a query and pull it out in Excel I get only 100 rows - only the most recent dated lines for each exchange rate type. How can I change the query so that I get all the rows that I see when I read the ExchangeRate table directly with a Ctrl T? I saw some complex union query solution that does what I need, but I would rather keep it really simple since my data connection will complain if I have more than one root element to a query...

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (6)

Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (1)

Report

AX 2012 has a special support for table with records that are valid in certain time period. When querying them, you get the currently valid records, but you can ask for a different point in time or period. Look at Valid Time State Tables and Date Effective Data for details.

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (1)

Report

Hi,

Please use the following job to get the data from ExchangeRate table.

static void ExchRate(Args _args)

{

ExchangeRate exchangeRate;

ExchangeRateCurrencyPair exchangeRateCurrencyPair;

date dateFrom, dateTo;

dateFrom = mkDate(01,01,1900); // Supply the date range

dateTo = mkDate(31, 12, 2154);

// Exchange rate information

While select

validTimeState(dateFrom, dateTo)

* from exchangeRate

order by exchangeRate.ValidFrom

join exchangeRateCurrencyPair

where exchangeRateCurrencyPair.RecId == exchangeRate.ExchangeRateCurrencyPair

{

info(strfmt("From currency: %1, To Currency: %2, ExchangeRate: %3, ValidFrom: %4, ValidTo:%5",

exchangeRateCurrencyPair.FromCurrencyCode,

exchangeRateCurrencyPair.ToCurrencyCode,

exchangeRate.ExchangeRate,

exchangeRate.ValidFrom,

exchangeRate.ValidTo));

}

}

Hope this helps you.

Thanks,

Chaitanya Golla

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

These is very helpful. Now I have a bit better understanding. If you don't mind pointing me in the right direction also for the last few steps; how do I get the job into a query so I can have it in my data sources and access it from my Excel Add-in? Many thanks!

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report
The Query class offers several methods for this purpose, such as validTimeStateDateRange().

For your scenario with Excel, try adding the following code to init() method (below super()) of your AOT query:

this.query().validTimeStateDateRange(dateNull(), maxDate());

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That sounds simple enough. But I'm still struggling with where to put this piece of code. In the AOT Query I have a Method node that contains only "classDeclaration" which seems very empty apart from "public class QueryRun extends ObjectRun{}". Is this where this piece of code should go? I clearly expose my lack of knowledge here...

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (1)

Report

You can right-click the Methods node and choose Override method > init.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I just figured that out this very moment and your code works like a charm! Many thanks!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I just realized that this works really well if I use the Dynamics Add-In in Excel to pull the data out. If I want to use Power BI Desktop with an OData connection it still only gives me the current records. Is there any way to do the same thing in a way that agrees with the OData connection? The same problem occurs also in Excel if using the OData connection.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report

How the OData query service handles date-effective tables is a different discussion, therefore it should go to a separate thread. Nevertheless here is a quick thought - use a custom query and check if the service isn't calling AifUtil::getODataCustomQuery(). If you find it does, try calling init() there.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Perfect. Just using a custom query instead of a query reference solved it. Many thanks!

Was this reply helpful? Yes No