Flow using SQL Get Rows with OData Filter not working with complex filter and null dates

[USPG-Tech]

I am creating a flow that uses the "SQL Get Rows (V2)" in Microsoft Flow.  In the SQL table I have a DateTime field called "dtLastModified".  I am trying to return only rows that have the "dtLastModified" field with dates greater than a certain date.  I'm using the Odata Filter Query field with the expression: 

dtLastModified gt '2020-01-01'

The problem is that some of the "dtLastModified" fields don't have values in them and are NULL.  When you run the flow you will get a bad request error because of the NULL value fields.  So then I tried using the following expression:

dtLastModified ne NULL and dtLastModified gt '2020-01-01'

Again I get a bad request error.  Note I can run the query using just:

dtLastModified ne NULL

and it does return all rows that contain a date but that does not help.  Any suggestions?

[v-alzhan-msft]

Hi @USPG-Tech ,

You could take a try to add a Filter Array action under the Get rows action to achieve the requirement.

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[USPG-Tech]

Hi Alice,  That is what I did to get something to work but that added a whole new set of issue as I was not able to access the fields in the result set of the filter query directly.  I had to manually create expressions for each field I wanted to use.  Is that normal?  In other words, I have a "for each" loop right after the filter query which I can choose the "body" reference to get to the data but when I try and select a field to add to a SharePoint list, the filter query fields are not options in the Dynamic Content list.  Only the fields from the original SQL Get Rows request.  If I try to use any of those, I get a second for each loop for each field I choose.  I also created another test flow just to try several scenarios of the Odata filter but could not get any to work with the filter query or the get rows functions.  Everything I researched says something should work but nothing I tried did.  Here are a couple of the queries I tried:  

dtLastModified gt '2020-01-01' and dtLastModified ne null 

@And(not(equals(item()?['dtLastModified'], 'NULL')),@greater(item()?['dtLastModified'], '''2020-01-01'''))

[v-alzhan-msft]

Hi @USPG-Tech ,

Please take a try with expression below:

@and(equals(empty(item()?['dtLastModified']), true),greater(item()?['dtLastModified'], '2020-01-01'))

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[USPG-Tech]

Thanks Alice.  I tried the statement in the SQL Get Rows and it error-ed with the message below.  Note when I pasted in the expression into the Odata field, it automatically turned it into an expression without the beginning "@" symbol.  I tried adding the "@" back at the front of the expression and hit Update but that said my expression was invalid.  I then tried the expression in the filter array and got the same error.  See below.  Any suggestions?

SQL GET ROWS ERROR:

InvalidTemplate. Unable to process template language expressions in action 'Get_rows_(V2)' inputs at line '1' and column '2780': 'The template language function 'greater' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

FILTER ARRAY ERROR:

InvalidTemplate. The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@and(equals(empty(item()?['dtLastModified']), true),greater(item()?['dtLastModified'], '2020-01-01'))' failed: 'The template language function 'greater' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

[v-alzhan-msft · Accepted solution]

Hi @USPG-Tech ,

Sorry for my expression is wrong since we need to check if the dtLastModified is not empty.

Would you please take a try with expression below:

@and(equals(not(empty(item()?['dtLastModified'])), true),greater(item()?['dtLastModified'], '2020-01-01'))

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[USPG-Tech]

Hi Alice,  I tried the updated expression in the SQL Get Rows and it ran for a few minutes and then got a bad request error saying it was not valid json.  The expression did work in the Filter Array but again the goal was to reduce the amount to of records retrieved from the SQL Get Rows function.  The other option I tried was to use the column that is a SQL Row Version which is a data type of Binary(8) or BigInt but SharePoint doesn't know what that is.  I also tried using a stored procedure and the Flow Execute stored procedure (V2) and returning the results that way but could not figure out how to return and use all the results.  Any other suggestions?  In any case, I do appreciate your help.  Thank you.