Pieter de Jong Pieter de Jong
Sep 4, 2014 12:00:00 AM

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.

Ax -query -1_497x 290

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.

Ax -query -2_500x 203

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.

Ax -query -3

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.

Pieter de Jong Pieter de Jong
TI_LOGO_TI-Logo-color andAXP_365

have now rebranded to

staedean-logo-teal