cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JonPl
Frequent Visitor

Cannot filter array by text in calculated column

I have a document library with a calculated 'Overdue' column which displays the text "Overdue" for items that meet the conditions.

I've setup a flow to send a weekly email with an HTML table with those files listed, however, I only want the table to list the files with the text "Overdue" in the 'Overdue' column. I cannot that to the "Filter Query" in the "Get Files" step because it's a calculated column (other non calculated text columns work okay during testing, but I need to filter by that calculated column or for the flow to perform the calculation itself based on a combo of other columns).

To filter by a calculated column, I tried adding a filter array step, however the flow runs successfully but the resulting email with table includes everything (is not filtered).

Can anyone point me in the direction of info on how to filter by text in a calculated column?

JonPl_0-1625260449507.png

I tried moving the filter array step lower, with no effect. I also tried removing and replacing with a condition step, but the result was I received separate emails with jumbled tables (seemed to be only the overdue, but I need them in a single email).

Thank you in advance for any info or assistance!

1 ACCEPTED SOLUTION

Accepted Solutions
ccc333ab
Solution Sage
Solution Sage

To use the select without the Parse Json, you can do the following: 

ccc333ab_0-1625275956509.png

Where the expressions on the right hand side would be: 

      item()?['xxx']    where xxx would be the name of your fields. 

 

As to filtering multiple fields, you definitely can do that with your fitler array by clicking on the Edit in Advanced Mode, and creating a string condition something like: 

@And(contains(item()?['Overdue'], 'Overdue'), equals(item()?['Dept'],'IT'))

 

   (This will check to see if the Overdue field contains "Overdue", AND the Dept is equal to IT.

 

View solution in original post

5 REPLIES 5
ccc333ab
Solution Sage
Solution Sage

I see nothing wrong with your filter array, so I created a calculated column where some of my rows would get set to "Overdue", used your exactly filter and it filtered as expected. 

 

The one thing that I am curious to is your statement of "moving filter array lower with no effect". Are you not using the results of your filter array with the rest of your actions? If not, then you wouldn't be filtering anything.

 

IF that doesn't make sense, can you show your Select/Create HTML/Send and Email actions. 

Hi @ccc333ab , thank you for your reply! Yes, I believe you're correct (I was having the same thought earlier), but I'm unsure how to flow should be updated to use the results of the filter array; if in the "Select" step I try to use the "Item" or "Body" from the filter array, I cannot map to the sharepoint fields without it converting back to using the value from the "Get files" step, and the create HTML table step won't let me use the output from the "Select" step.

My original flow was:

JonPl_0-1625269931832.png

My current flow is looking like:

JonPl_1-1625270251497.png

I recognize I must be 90% there - could you let me know where/how to use the output from the filter array?

 

I appreciate the help!

JonPl
Frequent Visitor

I ended up adding a "Parse Json" step after "Filter array" and before "Select", so that I could use the dynamic fields from the filter array step, and that seems to have resolved it!

For future reference - what if I wanted to filter by both a calculated column AND another column? Could I do that in the flow, or would the solution there be to create a new calculated column in the sharepoint library that refers to the columns in question, displays a result, and then I filter the array by that?

ccc333ab
Solution Sage
Solution Sage

To use the select without the Parse Json, you can do the following: 

ccc333ab_0-1625275956509.png

Where the expressions on the right hand side would be: 

      item()?['xxx']    where xxx would be the name of your fields. 

 

As to filtering multiple fields, you definitely can do that with your fitler array by clicking on the Edit in Advanced Mode, and creating a string condition something like: 

@And(contains(item()?['Overdue'], 'Overdue'), equals(item()?['Dept'],'IT'))

 

   (This will check to see if the Overdue field contains "Overdue", AND the Dept is equal to IT.

 

JonPl
Frequent Visitor

Thank you @ccc333ab , that was a great tip to be able to skip the Parse Json step!

Using "item()?['xxxx']?['Value']", and where there was an array, "item()?['xxxx'][0]['Value']", I was able to return the actual value, which is what I wanted and was having some difficulties with when using Parse Json.

For anyone in the future reading this, one small thing that also took me a few mins to figure out - the site column "_Status" needs to be "OData__Status" in the expression.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,290)