web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Advanced Query using S...
Supply chain | Supply Chain Management, Commerce
Answered

Advanced Query using SQL Statement

(0) ShareShare
ReportReport
Posted on by Aparisi82 2,188

Hello dear experts

this topic has always been difficult from my side.

I would like to build advanced queries using SQL statements , however I was never able to use it properly.

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

pastedimage1649581896112v1.png

For example, I would like to filter all Sales Orders that have the same customer requisition.

Using SQL it would be 2 minutes job using group by and having count(*)>1 however I wanted (without success) create a tile which would show me all the sales orders with the customer reference.

To do that , I have joint salestable again with salestable 

pastedimage1649582282715v2.png

ideally I would like to be writing a condition like this

(salestable.PurchOrderFormNum == salestable1.purchorderformnum)

the closest I got is this which does not work

pastedimage1649582313736v3.png

Would anyone know if this is possible using D365 advanced filtering?

I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    You should use this advanced query syntax only if absolutely necessary because - as you see - it's difficult to use and prone to errors.

    In your case, you said you want HAVING - then use it. It's supported. For example, look at Walkthrough: Creating an AOT Query that has Group By and Having Nodes.

    If you insist on following your approach anyway, please tell us more about your problem. "It does not work" may mean many different things.

  • Aparisi82 Profile Picture
    Aparisi82 2,188 on at

    Hi Martin, the scenario here is to have a  pure functional approach and try to exploit SQL statements as much as I can without asking for any development such as building an additional AOT query.

    Once I run that query , I  get empty rows which at least flags that the syntax is not wrong.

    Just realized that by joining the salestable with the salestable , D365 is probably joining on the key which would be the salesid and therefore this is never going to work.

    when I look at the relation source it says SalesTable : SalesTable . What does it mean? Is it joining on the primary key?

    pastedimage1649661027375v2.png

    Ideally I would like to run a query like this 

    select  

            *

        from salestable st

           join salestable st1 on

                   st.salesid <> st1.salesid

                   and st.purchorderformnum = st1.purchorderformnum

                   and st.dataareaid = st1.dataareaid

                   and st.custtable = st1.custtable

                   and st.invoiceaccount = st1.invoiceaccount

    This very inefficient query should return all SOs which are ( in our configuration ) duplicates as having the same customer requisition. Once D365 returns these records I can then build  TILE and add on a workspace.

    Also

    I do have a dev machine where I can actually debug D365. 

    Where should I put a breakpoint to see what query is executed when running the filter?

           

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    My opnion is that trying to use these expressions as much as possible is a bad idea. I recommend the opposite. You already see how difficult it is and that you're unable to debug them.

    What you need is close collaboration between business users and the development team and do things at the right place. I suspect that your key problem lies in the collaboration part and that you're trying to design workaround, instead of addressing the actual problem. Sure, it's often a difficult problem...

    Regarding "when I look at the relation source it says SalesTable : SalesTable . What does it mean? Is it joining on the primary key?": I think the first value is the table name and the other is the relation name. If you look at this relation in AOT, you'll see that it joins SalesTable.ReturnReplacementId with SalesId of another SalesTable record. It would have helped if this relation had a more descriptive name.

  • Aparisi82 Profile Picture
    Aparisi82 2,188 on at

    your opinion is always highly appreciated Martin :)

    I think based on the your technical answer, I cannot self-join on a fields which are not part of the primary key.

    Hence this cannot be achieved using the standard query framework.

    Cheers

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
Siv Sagar Profile Picture

Siv Sagar 283 Super User 2025 Season 2

#2
Laurens vd Tang Profile Picture

Laurens vd Tang 213 Super User 2025 Season 2

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 152 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans