Finance | Project Operations, Human Resources, ...

Advanced Query && ?

(3) Share

Report

Posted on by SU-22040800-0

2,459

When using the Advanced Filter on the "All Customers" form, adding a deaper table like "Responsibilities", and fitering where Worker is XXXX, YYYY the filter will give me all customers that have a responsibility worker as either XXXX OR YYYY.

Is it possible to change the OR to an AND ?

In short, I want to see all customers where any responsibility have the people XXXX and YYYY allocated as workers.

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (1)

Report

No, it's not possible. A customization is required to achieve this.

Was this reply helpful? Yes No
Adis 6,048 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

I dont get your request 100%...maybe I am just slow today.

However, it works in such a way that every row that has a value is connected with an AND. So in your example, you should get records where

Lead Sales person = XXXX AND Dept. Head is YYYY AND Account Manager is ZZZZ.

Kind regards, Adis

If this helped, please mark it as "Verified" for others facing the same issue

Keep in mind that it is possible to mark more than one answer as verified

Was this reply helpful? Yes No
SU-22040800-0 2,459 on at

Like (0)

Report

Thanks for the responses.. I have looked at the MS documentation:

https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/advanced-filtering-query-options

((AccountNum LIKE "US*") && (DirPartyTable.Name LIKE "Cont*"))

And have even tried the D365 SQL Syntax, but unfortunately that did not work either.

Does anyone know if there is a way (in the SQL Syntax) to not use "LIKE" and to use something like "GREATER THAN" or "LESS THAN" ?

Was this reply helpful? Yes No
Suggested answer

Saalim Ansari 666 on at

Like (3)

Report

Hii,

No, it's not possible to change the OR condition to an AND condition across multiple child records like Responsibilities directly in the Advanced Filter of the All Customers form in Dynamics 365 Finance. This limitation exists because the filtering logic in the UI evaluates conditions per row. So when you filter by Responsibilities.Worker == XXXX OR YYYY, it checks each responsibility line, not all of them together. It cannot apply AND logic to check if both workers are present across different records for the same customer.

To achieve this requirement—showing customers that have both workers XXXX and YYYY assigned in the Responsibilities—you’ll need to implement a workaround. One approach is to create a custom X++ query or job that uses exists logic to check if both workers are assigned to the same customer. Alternatively, you can use a temporary table or view that gathers all customers who meet both conditions, and then use that for further processing or display.

Unfortunately, advanced SQL-style conditions like GREATER THAN or AND across multiple rows also do not work in the Advanced Filter UI for deeply related tables. These types of complex logic require custom development or reporting to achieve the desired results.

Was this reply helpful? Yes No
CU13020846-1 12 on at

Like (2)

Report

Thanks all for the responses!

Was this reply helpful? Yes No