Pieter de Jong
Do you know how to use queries in Microsoft Dynamics AX with dynamic ranges? Today, we look at two approaches to do this, by using the standard flexibility of the queries and by using the custom range option in STAEDEAN Business Integration Solutions for Microsoft Dynamics AX.
Queries in Microsoft Dynamics AX differ from the queries you run with SQL Server. Today I will highlight how we can address different scenarios for exporting data using a dynamic range.
Using the message for a sales order in STAEDEAN Connectivity Studio, we will show you the different samples of queries. In the source document, you can add custom ranges to limit the export. There are two different scenarios for queries possible.
- Use the standard flexibility of the queries.
- Use the custom range option in STAEDEAN Business Integration Solutions for Microsoft Dynamics AX.
Use the standard flexibility of the queries
The standard queries already support a level of flexibility with predefined functions. You can read more about that in this blog post: http://daxmusings.codecrib.com/2013/07/custom-query-range-functions-using.html
The class “SysQueryRangeUtil” contains several predefined methods which can be used to make your query more dynamic. The format is plain and simple: ( some code ), you only need the parentheses around the method.
Just to explain how this can be used, here are some samples:
| Goal | Implementation |
| Get the orders for the last 7 days | dayRange(-7,0) |
| Before today | lessThanDate(0) |
| Get everything from 3 days ago and later | greaterThanDate (-3) |
| Get everything from today and 5 days ahead | dateRange(today(),today+5 |
| Get the current worker based on the active user to filter on sales orders the worker is responsible for | currentWorkerRecId() |
| Filter using the active company (curExt) | currentCompany() |
| Filter on the customer linked to the user | currentCustomer |
| Get the language of the current user | currentUserLanguage() |
| Get the current date | currentDate() |
There are more methods available. You can customize this class to have more options available.
Use the custom range option in STAEDEAN Business Integration Solutions for Microsoft Dynamics AX
In the business document there is an option for custom ranges. This will execute custom code when you execute the query. This method supports all the static methods in Microsoft Dynamics AX, which will eliminate customizations.
This approach to queries supports options such as curExt(), today(), the data time utility, and other platform classes. Some samples are:
| Goal | Implementation |
| Before today | strFmt(‘..%1’, today()) |
| Get the currentCompany | curExt() |
| Filter on the active user | curUser() |
| Get the orders from last month | strFmt("%1..%2", prevMth(today()), today()) |
| Filter on the maximum value on a date / time field | DateTimeUtil::maxValue() |
| Filter on the current date time | DateTimeUtil::utcNow() |
Many other scenarios are possible. This second option gives you the ultimate flexibility to create dynamic queries.
Feel free to comment if you have other useful scenarios in mind or you can contact us.
Schedule a demo to know more about queries in Microsoft Dynamics AX
Pieter de Jong