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 / Microsoft Dynamics AX (Archived) / QueryHavingFilter Expr...
Microsoft Dynamics AX (Archived)

QueryHavingFilter Expression with or condition for different fields

(0) ShareShare
ReportReport
Posted on by Enxhi Bregu 123

Hi everyone,

I have to write in Ax a query like this:

SELECT  a.ORDERNUM, a.CHARGESPROGRESSLINETYPE, SUM(Ordered) as Ordered , 
 SUM(Statutory) as statutory, SUM(Accerted) as Accerted
 FROM ATestTable_SAM a
 GROUP BY a.CDR,a.MainAccount, a.ItemId, 
 a.Year, a.startDate, a.endDate, a.TransDate, a.PostDate, a.OrderNum, 
 a.BudgetTotal, a.BudgetSourceTrackingStatus, a.ProjId, a.ProjName 
 having ((sum(ORDERED) !=0) or (sum(Accerted) !=0) or (sum(statutory) !=0))

I do not want to see in my form the lines that have sum(ordered) and sum(Accerted) and sum(Statuory) zero, 
so at least one of this sums has to be not zero.
I am trying use the QueryHavingFilter, but I am not finding a way how to use the or condition as In SQL.
queryHavingFilter = fds.query().addHavingFilter(qbds, fieldStr(ATestTable_SAM, Ordered),AggregateFunction::Sum);
 queryHavingFilter.value(SysQuery::valueNot(0));
 
Can I use an expression like in queryRange :

qbr.value((strFmt('(((%1 != %4)|| (%2 != %4)|| (%3 != %4)))',
fieldStr(ATestTable, Ordered),
fieldStr(ATestTable, Accerted),
fieldStr(ATestTable, Statutory),
queryValue(0))));

Thank you,

Enxhi


*This post is locked for comments

  • Martin Dráb Profile Picture
    Martin Dráb 231,432 Most Valuable Professional on at
    RE: QueryHavingFilter Expression with or condition for different fields

    If you can't get it working with Query::insertRecordset(), simply use a while loop and a RecordInsertList or direct insert(). If you use this approach, you can interpret the conditions for amounts in the application and you don't need any HAVING clause.

    I'm not sure if Query::insertRecordset() supports it or not. It may be worth explaining your problem with it; maybe we could resolve it.

  • Enxhi Bregu Profile Picture
    Enxhi Bregu 123 on at
    RE: QueryHavingFilter Expression with or condition for different fields

    I can not write something like this in the query,should I find a workaround?

    I am trying to use a tmp table, and insert the data using Query::InsertRecordset(), but I can not make it work, with group by and Aggreate fields.  Do you have any other suggestion?

  • Enxhi Bregu Profile Picture
    Enxhi Bregu 123 on at
    RE: QueryHavingFilter Expression with or condition for different fields

    The user enters a date range, and I need the sum of the fields specified only for the lines that fall into that range

  • Martin Dráb Profile Picture
    Martin Dráb 231,432 Most Valuable Professional on at
    RE: QueryHavingFilter Expression with or condition for different fields

    Do you meaning something like filtering by a particular customer? Then you would have a group for all customers and filter it based on what you get from the dialog.

  • Enxhi Bregu Profile Picture
    Enxhi Bregu 123 on at
    RE: QueryHavingFilter Expression with or condition for different fields

    The problem is that  I have to group the lines when the user opens the form. I have to modify the queryrun of the form applying the ranges that the user enters in a dialogbox. I do not know before, what records should I group.

  • Martin Dráb Profile Picture
    Martin Dráb 231,432 Most Valuable Professional on at
    RE: QueryHavingFilter Expression with or condition for different fields

    I would create a view based on the query (sum with group by) and filter the view.

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... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,432 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans