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,178

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?

  • Aparisi82 Profile Picture
    Aparisi82 2,178 on at
    RE: Advanced Query using SQL Statement

    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

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 231,947 Most Valuable Professional on at
    RE: Advanced Query using SQL Statement

    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,178 on at
    RE: Advanced Query using SQL Statement

    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?

           

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,947 Most Valuable Professional on at
    RE: Advanced Query using SQL Statement

    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.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,274 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,947 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans