Automated Microsoft D365 Finance Integrations with AMCS
Microsoft Dynamics 365 Finance integrations are essential for enterprise organizations aiming for efficiency and accuracy. With the Advanced Multi-Company Solution (AMCS) by
Selection Resources
Solution Areas
Product Selection Tools
Evaluation Guides
Readiness Overview
Identifying Stakeholders
Managing Expectations
Prepare to be Prepared
AI Services
Testing Automation
Upgrade Services
Project Management
Roadmap Services
Data Migration
Data & Analytics
Licensing Support
Train & Optimize
Local Support
Resources
Avantiico Industry Solutions
Explore by Business Need
Microsoft Business Apps
Avantiico Industry Solutions
Microsoft Platforms
Licensing & Pricing Options
Partner Program
Our Partners
Careers
Avantiico Academy Program
Browse Roles
About Avantiico
Events & Webinars
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.
Senior Consltant
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.
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.
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.
The ExchangeRate datasource property ‘ValidTimeStateAutoQuery’ is used to control what data is returned.
Change this to ‘DateRange’ and all records will be returned.
A custom query was created to query the ExchangeRate table.
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.
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.
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.
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.
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.
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.
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.
A new form was created and the view above was used as datasource.
The following records are shown, the date effective filters do not apply.
A custom query was created to query the ExchangeRate table using a view.
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.
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.
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
Microsoft Dynamics 365 Finance integrations are essential for enterprise organizations aiming for efficiency and accuracy. With the Advanced Multi-Company Solution (AMCS) by
Automated freight reconciliation in Microsoft Dynamics 365 Finance & Supply Chain Management, as part of the 3PL Automation Cloud by Avantiico, streamlines
Dive into Microsoft Fabric’s capabilities in streamlining data operations across your business. This blog explores the platform’s unified data management, advanced analytics, and real-time data processing, demonstrating how Fabric can empower businesses to optimize data strategies and drive insightful decision-making. Learn how seamless integration and scalability make Microsoft Fabric essential for future-proofing your data ecosystem.
Book a free meeting and let us have a look at your opportunities with Microsoft Solutions
Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.
Discover how Avantiico helps you improve business processes, provide customers with a seamless experience and transform the way you do business.