How to apply AOT query ranges in D365FO?

In this article, I'm going to show you how to apply different types of ranges to AOT queries. We'll go through simple and complex ranges.

  • Let's first create a simple query that has "CustTable" inner joined with "CustGroup"

    Create AOT Query

  • If we want the query to return values where the enum "Blocked" field value is Never, then it's either we type the value Never directly or type CustVendorBlocked::Never
    ** It's not recommended to filter on the enum integer value

    Enum range first way

    Enum range second way

  • If we want the query to return values where AccountNum value is "US-001", we can add the string value US-001 directly

    AccountNum single range

  • Now what if you want to filter on AccountNum "US-001" OR "US-002", you can do it in two ways: 
    • you can either use comma to separate the values (comma is considered as an OR)

      AccountNum multiple ranges first way

    • Or, you can do it like this: ((AccountNum = "US-001") || (AccountNum = "US-002"))
      This way will be helpful later when we do complex scenarios. But for simple scenarios you can stick with the first way

      AccountNum multiple ranges second way


  • If we want to add another filter to the previous result, like filtering on the Yes/No field which is InclTax, then we can do the following:
    • It's either we add a new range for this "InclTax" field so that it is considered as an AND to the previous result.
      **You can filter by Yes/No or 0/1

      Enum no yes range

    • Or, you can add the whole filter on AccountNum field like this:
      (((AccountNum = "US-001") || (AccountNum = "US-002")) && (InclTax = 1))
      • Here you need to filter by 1 or 0 for the InclTax field (we can't use Yes/No)
      • You don't have to put the range on the AccountNum field specifically, for example, you can write the whole expression on the InclTax field instead
      • Make sure that each range is in parentheses
      • Again, since the first method works, then we could do it the first way as it's simpler

        Complex range


  • Now what if we want to add ranges on two datasources:
    • If let's say we want to filter where CustTable.CreditMax < 10 AND CustGroup.Name = "Retail Customers", then we can do it as this:

      Credit max less than 10 range


      Customer group name range

    • OR if we want to filter on  CustTable.CreditMax < 10 OR CustGroup.Name = "Retail Customers", then we can do it as this (we'll need to do the range on one field because it's an OR between two different fields):
      • it's either we put range on CreditMax field like this
        ((CreditMax<10) || (CustGroup.Name = "Retail Customers"))
        or like this
        ((CustTable.CreditMax<10) || (CustGroup.Name = "Retail Customers"))
      • Or we can put the range on Name field like this:
        ((CustTable.CreditMax<10) || (Name = "Retail Customers"))
        or like this
        ((CustTable.CreditMax<10) || (CustGroup.Name = "Retail Customers"))

        OR range on fields in different datasources

  • Also when filtering AOT queries, we can utilize methods in "SysQueryRangeUtil" class that have [QueryRangeFunctionAttribute].
    For example, I added a new date field to CustTable called "TestDate". And I wanted to add to the previous filter an AND condition that says "TestDate" field value should be greater than today's date by 2 days. In this case I can use the method "greaterThanDate(2)"
    So if today is 27/9/2025, the condition will be TestDate > 29/9/2025
    **Make sure to put parentheses when applying range value

    Date range filter

  • Also please note that you could use any method that has [QueryRangeFunctionAttribute] in the AOT query range, it doesn't have to be only inside "SysQueryRangeUtil" class.
    For example, in HcmWorkerLookup class, you can use "currentWorker()" method in AOT ranges like this: (HcmWorkerLookup::currentWorker())
    currentWorker() method in AOT query ranges

Comments

Post a Comment

Popular Posts

How to authenticate with D365FO using Postman?

Image
Let's say you are asked to call a certain custom service API in D365FO or maybe call an OData endpoint. To be able to do that, you first need to authenticate. So in this article, I'm going to show you how to do that via Postman:   First you need to make sure that the ClientId is defined in D365FO environment. Go to System Administration - Setup - Azure Active Directory Applications In order to get this clientId, sign in to https://portal.azure.com Go to App registrations and choose your application, where you can find the ClientId In order to get the client secret, click on your app, go to "Certificates & secrets" and take the secret value. Or contact your admin to give you the values
Read more

How to apply database backup from LCS to your D365FO DevBox environment?

Image
Do you want to update your devBox with the latest data from another environment? In this article, we are going to talk about how to apply a database back-up (.bak file) from LCS to your D365FO DevBox environment. Log in to your DevBox where you want to apply the DB back up Sign in to LCS  https://lcs.dynamics.com Select the project that has the DB back-up you want to apply Click on the burger icon and choose Asset Library Click on "Database backup" then choose the database backup you want to download and click on it. The download will start for the backup file of type (.bak)  I saved this file in my devBox in J drive, under a new folder called LCS backup Before proceeding, It's better to close Visual studio and stop the following services: DMF, Batch, Management reporter and SQL reporting services. Open "Microsoft SQL server management studio" and click connect Expand the "databases" node in object explorer, and choose AxDB Before applying the new DB b...
Read more

How to setup Business Events with Azure Service Bus Queue endpoint in D365FO?

Image
Business events allow you to notify external systems when certain operations in D365FO happens. For example, you can notify an external system when a sales order is invoiced or when a customer electronic address is inserted. In D365FO, you can manage the destination where you want your business event to be sent to by configuring endpoints. Business events support multiple types of endpoints and in this article we are going to cover the "Azure Service Bus Queue" endpoint where I'm going to show you how to create an azure service bus from scratch and setup it up in D365FO. In general, "Azure Service Bus" enables applications and systems to communicate with each other and exchange information. And Azure service bus queue is a type that provides a  1-1 communication between the sender and the receiver. First, let's configure D365FO Business event with Azure service bus queue endpoint: Go to System Administration - Setup - Business events - Business events Catalo...
Read more