Supply chain | Supply Chain Management, Commerce

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

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

Sort by

Adis 6,686 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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,758 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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,803 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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,135 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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 93 on at

Like (1)

Report
Copy link

Link copied!
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,135 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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 93 on at

Like (0)

Report
Copy link

Link copied!

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

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

Like (1)

Report
Copy link

Link copied!

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,803 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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 93 on at

Like (0)

Report
Copy link

Link copied!
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