Filter the records in Query Range based on Date

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (5)

Answers (1)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Iulian Cordobin 8,201 on at

Like (0)

Report

Try like this:

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

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

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report
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()));

Was this reply helpful? Yes No
Tony Mok 577 on at

Like (0)

Report

Hi Martin Dráb,

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

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No