Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Dynamics AX 2012 Advan...
Microsoft Dynamics AX (Archived)

Dynamics AX 2012 Advanced filter with sql statement; outer join

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I would like to use the advanced filtering function to find records that has no transactions in another table. Example; filter transactions in SalesAgreementHeader that is not yet confirmed. This means that there is no transaction in the SalesAgreementHeaderHistory. I found below sql example to find transactions in the CustTable even if there is no match in SalesTable.

 SELECT * FROM CUSTTABLE

       OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

       AND SALESTABLE.CURRENCYCODE = 'EUR'

First i wonder what the syntax is to enter this in the advanced filter function; if it is possible at all. Secondly what must I add to ONLY find the transactions that have no match in the SalesAgreementHeaderHistory table (or SalesTable with the above example given).

*This post is locked for comments

  • dolee Profile Picture
    dolee 11,279 on at
    RE: Dynamics AX 2012 Advanced filter with sql statement; outer join

    I tried "EXISTS" and "IN", both didn't work for me. (But you might want to give it a try anyway in case the problem was on my side. )

    Cheers

  • guk1964 Profile Picture
    guk1964 10,877 on at
    RE: Dynamics AX 2012 Advanced filter with sql statement; outer join

    Enter the query as an SQL statement between parenthesis.

    For example, (datasource.Fieldname!= "A")  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamics AX 2012 Advanced filter with sql statement; outer join

    Thanks but how would the example look in the advanced filter function? I would like to, as a normal user, enter and save the filter to find transactions with no transactions in the "sister" table.

    BR

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamics AX 2012 Advanced filter with sql statement; outer join

    you must use the not exists join as in the example shown.

    while select AccountNum, Name from custTable

       order by AccountNum

       notExists join * from ctr

       where (ctr.AccountNum ==

           custTable.AccountNum)

    Refer here for more sql syntax

    msdn.microsoft.com/.../aa656402.aspx

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans