Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Filter the records in ...
Microsoft Dynamics AX (Archived)
Answered

Filter the records in Query Range based on Date

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

Hi all,


 I want to filter the record in Customer Transaction(CustTrans), the Closed date should empty OR greater than Today.
 

i am tried with


criteriaOpen = this.query().dataSourceNo(1).addRange(fieldNum(CustTrans,Closed));


criteriaOpen.value(strFmt('(%1.%2 == "") || (%1.%2 < %3)',
                        this.query().dataSourceNo(1).name(), fieldStr(CustTrans, Closed), today()));

its not working.

*This post is locked for comments

  • Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Filter the records in Query Range based on Date

    Could you please explain what you mean by this statement?

    Do you realize that "OR" is added automatically if you add multiple ranges for the same field, as I did?

  • Tony Mok Profile Picture
    Tony Mok 577 on at
    RE: Filter the records in Query Range based on Date

    Hi Martin Dráb,

    It does not support "OR" case in your code.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,514 Most Valuable Professional on at
    RE: Filter the records in Query Range based on Date

    I strongly recommend not using the extended query syntax if it's not necessary. It's ugly and error-prone, as you've found by yourself.

    Isn't this better?

    QueryBuildDataSource ds = this.query().dataSourceNo(1);

ds.addRange(fieldNum(CustTrans, Closed)).value(queryValue(dateNull());
ds.addRange(fieldNum(CustTrans, Closed)).value(queryRange(today()+1, dateMax()));
  • Suggested answer
    Iulian Cordobin Profile Picture
    Iulian Cordobin 8,201 on at
    RE: Filter the records in Query Range based on Date in Ax 2012

    Try like this:

    criteriaOpen.value(strFmt('(%1.%2 == %4) || (%1.%2 < %3)',

                           this.query().dataSourceNo(1).name(), fieldStr(CustTrans, Closed), date2StrXpp(today()), date2StrXpp(datenull())));

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Filter the records in Query Range based on Date in Ax 2012

    As per MSDN, Null value of date is treated as 1900-01-01  

    For CustTrans, if you will have a look at SQL SERVER DB, you will find that, the value of field Closed is not null, its 1900-01-01 00:00:00.000 on SQL SERVER

    So, passing dateNull() from X++ seems to fix the issue

    Give a try

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,514 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans