Select action to filter on date fails on null values

[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.

[wskinnermctc]

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

[Paulie78 · Accepted solution]

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?

[mhamerton]

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?

[wskinnermctc]

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?

[Paulie78]

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

1. Get Items 
2. Filter
3. Select

or perhaps even just:

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

[mhamerton]

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.