Supply chain | Supply Chain Management, Commerce

Advance filter on dates - (LessThanUtcNow()) but not blank

(6) Share

Report

Posted on by jwell22

Hello,

I am trying to apply an advance filter on confirmed receipt date for open purchase orders.

I want to apply both (LessThanUtcNow()) and is not blank. Otherwise, the (LessThanUtcNow()) filter alone will display blank values, which I do not want.

I have tried a few without success:

>01011900
!""

Any ideas?

Thank you in advance!

Categories:

Dynamics 365 Supply Chain Management

I have the same question (0)

All responses (15)

Answers (0)

Adis 6,069 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Right from the gut I would try to apply the BETWEEN syntax => 01.01.1900..(LessThanUtcNow())

Advanced filtering and query syntax - Finance & Operations | Dynamics 365 | Microsoft Learn

I would also check:

-if the two values work separately, ie. filter by no blanks OR (LessThanUtcNow()). Instead of using !"" or >01011900, try using a date before the first purchase order ever created in the system.

-Instead of (LessThanUtcNow()), try using a date. Maybe the function works as a stand alone function but not in combination.

Kind regards, Adis

If this helped, please mark it as "Verified" for others facing the same issue

Was this reply helpful? Yes No
Laurens vd Tang 4,018 Super User 2025 Season 2 on at

Like (0)

Report

Good day,

Could you try it with (LessThanUtcNow()) , !""

Best regards,

Laurens van der Tang

Was this reply helpful? Yes No
Suggested answer

Mohamed Amine Mahmoudi 26,447 Super User 2025 Season 2 on at

Like (0)

Report

Hi @jwell22,

You need to use >T instead of (LessThanUtcNow()) because it also contains null values

Best regards,

Mohamed Amine MAHMOUDI

Was this reply helpful? Yes No
Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

Can you tell me what is the data that you want to filter? for example, do you want to return records that have confirm receipt date > 1/1/1900?

Thanks,

Waed Ayyad

Was this reply helpful? Yes No
jwell22 85 on at

Like (1)

Report
Thanks all for your responses so far, but unfortunately none of the suggestions work.

I'm still getting blank values come through.

The data I'm trying to filter is on lines where the confirmed receipt date is:

A date in the past (not including today)

Not blank

Was this reply helpful? Yes No
Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

To be clear do you want to get all records < today () and not blank?

Thanks,

Waed Ayyad

Was this reply helpful? Yes No
jwell22 85 on at

Like (0)

Report

That's right. So I want to see POs which are late.

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

Like (1)

Report

The problem with yours, lvdtang's and Mohamed's approaches is that they're combining the ranges with OR operator, not AND.

A good approach may be creating a custom query range util method, similar to lessThanUtcNow(), but excluding empty values.

Was this reply helpful? Yes No
Suggested answer

Mohamed Amine Mahmoudi 26,447 Super User 2025 Season 2 on at

Like (0)

Report

Hi @jwell22,

the first solution works very well for me.

if not, I suggest this solution: put 1901 instead of 1900.

Best regards,

Mohamed Amine MAHMOUDI

Was this reply helpful? Yes No
jwell22 85 on at

Like (0)

Report
This one is almost perfect, except I don't want to filter on today's date (T), instead I want yesterday.

The idea being that I capture POs that are "late"

I have tried (Day(-1)) but this does not work either.

Was this reply helpful? Yes No