cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PHemingway610
Advocate II
Advocate II

Filter Array Action Fails After Adding OData Sort to Get Items

I have built a workflow which gets items from a list, filters for items with an "Internal Date" field greater than 7 days ago, selects certain columns, builds an HTML table, formats the table, then sends an email.  All of it works perfectly until the customer asked a VERY simple request: can you sort the results in the opposite order?  

 

Currently, the table in the email shows the highest dates at the top (descending order) and that is when no OData sort is applied.  If I add "Internal_x0020_Date asc" to the "Order By" inside "Get Items," however, my workflow will fail at the next action which is Filter array.  I tried to sort descending (Internal_x0020_Date desc) and that does not cause errors.  The specific error for Filter Array is:

 

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@greaterOrEquals(item()?['Internal_x0020_Date'], addDays(utcNow('yyyy-MM-dd'), -7))' failed: 'The template language function 'greaterOrEquals' expects two parameter of matching types. The function was invoked with values of type 'Null' and 'String' that do not match.'.

 

I would also like to include a screenshot here, of my actions' configuration in the Flow builder, but I do not know what to enter in "source" so that I can simply add a screenshot to the message body.  HELP!

Thank you,

Phil

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @PHemingway610,

 

There is a blank in the column name, right? So you input "Internal_x0020_Date asc" into the "Order By", actually, you have no need to add the "_x0020_" when using the "Order By", just as below:

Annotation 2020-04-03 141330.png

 

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

View solution in original post

I have figured out the problem.  Hopefully this might save others in the future.  If you, like me, are working with a somewhat large list and do not know if all items being compared have a value, quickly check your list by setting a view to filter for when that column is blank.  I created a SharePoint view for when InternalDate was blank, and there were 5 items in a list of 2400 which did not have a date filled in.  This was causing the "Filter Array" action to fail on my greaterOrEquals comparison.  BUT, why did it only fail when I applied the "Order By" sort in the "Get Items" step?  This is because of the following: 

https://powerusers.microsoft.com/t5/I-Found-A-Bug/Get-Items-Limit/td-p/70417

 

Despite the "top count" limit setting in "Get Items" telling us that leaving blank gets ALL items, it does not.  The default IS IN FACT 100 items.  If you do not fill in a limit, it grabs only 100 items.  When I was not sorting, the default sort was descending and the empty items at the bottom of the list weren't grabbed by "Get Items" because of a default 100 item limit, they definitely then weren't checked in the "Filter Array" comparison, and the workflow ran without errors.  When I was sorting in ascending order, the items with empty column values were at the top, and were included in the "Get Items" action's default 100, and their null values then caused the "Filter Array" action to fail at the greaterOrEquals comparison.  Setting the "Top Count" to a high number like 5000, as well as ensuring all items had values in the column being compared, is the solution.

View solution in original post

7 REPLIES 7
v-litu-msft
Community Support
Community Support

Hi @PHemingway610,

 

There is a blank in the column name, right? So you input "Internal_x0020_Date asc" into the "Order By", actually, you have no need to add the "_x0020_" when using the "Order By", just as below:

Annotation 2020-04-03 141330.png

 

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

Hello Lin Tu,

 

Thank you very much for your reply.  Yes I have a space in the column name.  It reads "Internal Date" in SharePoint as display name and "Internal_x0020_Date" is the system name.  I tried your suggestion and removed the "_x0020_" but that did not work.  The workflow fails, this time on the Get Items step (previously it would fail on the next step when I added the sort).  And it fails with error message:

Column 'InternalDate' does not exist. It may have been deleted by another user.
clientRequestId: 0c3b3028-3f79-46f6-8f26-ac00890ded05
serviceRequestId: 0c3b3028-3f79-46f6-8f26-ac00890ded05

 

 

Thank you,

Phil

Hi @PHemingway610,

 

Did you ever change the column name in SharePoint?

If the column named changed, the display name couldn't be used, you should use its system name to Order By, check its system name, you could click List Settings>>Column Name>>Check name in the address bar:

1.

Annotation 2020-04-08 175841.png

2.

Annotation 2020-04-08 175913.png

 3.

Annotation 2020-04-08 175939.png

 

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

Hi Lin Tu,

 

Thank you again for your response.  No, the list is brand new (I'm going to be migrating an old SP2010 list), and the system name for the column, as it shows in the address bar, is "Internal_x0020_Date".  I created the column as a site column, added it to my content type, and I have not changed its name at all.

 

 

Hi @PHemingway610,

 

It's weird, how about create a new column named without space?

 

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

Hello,

 

I did not accept your original answer as a solution.  Please revert back marking this as solved.  This is not accurate and will not help anyone else with the same problem.

 

I very much appreciate your suggestion to create a new column without a space in it.  I have just done this, and updated my flow to use the new column in the OData "Order By" box for the "Get Items" action.  However, the same exact issue persists.  I have the following entered in "Order By":

InternalDate asc

 

This now points to a column named InternalDate which was created without any spaces, as a date field, and populated with values (where before my column was "Internal%20Date").    But I get the same error message as described in my original post.  When I erase the sort, the workflow runs successfully, but with the sort applied, the workflow fails on the next step.

I have figured out the problem.  Hopefully this might save others in the future.  If you, like me, are working with a somewhat large list and do not know if all items being compared have a value, quickly check your list by setting a view to filter for when that column is blank.  I created a SharePoint view for when InternalDate was blank, and there were 5 items in a list of 2400 which did not have a date filled in.  This was causing the "Filter Array" action to fail on my greaterOrEquals comparison.  BUT, why did it only fail when I applied the "Order By" sort in the "Get Items" step?  This is because of the following: 

https://powerusers.microsoft.com/t5/I-Found-A-Bug/Get-Items-Limit/td-p/70417

 

Despite the "top count" limit setting in "Get Items" telling us that leaving blank gets ALL items, it does not.  The default IS IN FACT 100 items.  If you do not fill in a limit, it grabs only 100 items.  When I was not sorting, the default sort was descending and the empty items at the bottom of the list weren't grabbed by "Get Items" because of a default 100 item limit, they definitely then weren't checked in the "Filter Array" comparison, and the workflow ran without errors.  When I was sorting in ascending order, the items with empty column values were at the top, and were included in the "Get Items" action's default 100, and their null values then caused the "Filter Array" action to fail at the greaterOrEquals comparison.  Setting the "Top Count" to a high number like 5000, as well as ensuring all items had values in the column being compared, is the solution.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

New Process Advisor Capabilities carousel.png

Read the blog for the latest news

Read the latest about new experiences and capabilities in the Power Automate product blog.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

AI Builder AMA June 7th carousel (up on May 25th, take down June 8th) (1).png

'Ask Microsoft Anything' about AI Builder!

The AI Builder team invite you to ask questions and provide helpful answers at our next AMA.

Users online (1,532)