Dynamics AX 2012 Advanced filter with sql statement; outer join

(0) Share

Report

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

All responses (4)

Answers (0)

dolee 11,279 on at

Like (0)

Report

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 10,877 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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