Unanswered

Select action to filter on date fails on null values

(0) Share

Report

Posted on by mhamerton

I am trying to create a select action that filters out items from Get Items previous to today's date, but the flow fails on Null values.

Here is the function I have in the Map: less(item()?['SignOutDateTime'], formatdatetime(utcNow('yyy-MM-dd')))

Error: InvalidTemplate. The execution of template action 'Select_2' failed: The evaluation of 'query' action 'where' expression '{ "@item()?['SignOutDateTime']": "@less(item()?['SignOutDateTime'], formatdatetime(utcNow('yyy-MM-dd')))" }' failed: 'Unable to evaluate template language expression '@item()?['SignOutDateTime']' as JSON property name: the expression value of type 'Null' could not be used as property name.'.

I need to filter for not null and older than today.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

You need to use the empty() expression with an if() expression to check the value first. If it is empty() the result will be true, so you need to set the true part of the if() expression to '' blank or something else, then in the false part of the if() you can do the formatdatetime() expression

So it will be something like if(empty({DateValue}),'',formatDateTime({DateValue}))

Was this reply helpful? Yes No
Verified answer

Paulie78 8,422 Moderator on at

Like (1)

Report

Why not just put a filter before the select to remove any rows where the SignOutDateTime is equal to null? Then run your select which will be free of null entries?

Was this reply helpful? Yes No
mhamerton 7 on at

Like (0)

Report

Thank you, I did come across this type of solution, but couldn't get it to work either, could be syntax. Here is the formula I most recently used: if(empty(item()?['SignOutDateTime']),utcNow(),formatDateTime(item()?['SignOutDateTime']))

Error: The evaluation of 'query' action 'where' expression '{ "@item()?['SignOutDateTime']": "@if(empty(item()?['SignOutDateTime']),utcNow(),formatDateTime(item()?['SignOutDateTime']))" }' failed: 'Unable to evaluate template language expression '@item()?['SignOutDateTime']' as JSON property name: the expression value of type 'Null' could not be used as property name.'.

I am putting this expression in the correct place am I not?

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

I don't think the problem is the expression on the right side of the Map where you Enter Value. The problem is the left side of the Map where you Enter Key.

You have that green Dynamic content on the left side of the Map as the Key which is basically like a row name, and that is not allowed to be blank.

So you are dealing with blank values on the right side with the expression, but doing nothing to the left side where the Key should be if the value is blank.

I agree with @Paulie78 with needing to filter out the dates some other way.
I don't really understand what the flow is doing with the Select 2 being inside of an Apply to Each?

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
It looks to me like you don't need the apply to each at all. It should be:

Get Items

Filter

Select

or perhaps even just:

Get Items (with odata filter to exclude ones without dates).

Select

Was this reply helpful? Yes No
mhamerton 7 on at

Like (0)

Report

Paulie78, your solution worked well. I do need the Null values as the email sends a report based on these outstanding fields.

Thanks again, I was pulling my hair out.

Was this reply helpful? Yes No