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

Filtering Get Items using Calculated Column

Hi,

 

I have a library with 1000+ documents. The goal is to send a notification to the document owner when the Document Review is 21 days away.

 

I have following related columns:

  • Last Reviewed (Date Column)
  • Review Period (Number)
  • Review On (Calculated) = [Last Reviewed] + [Review Period]
  • Days Remaining (Calculated) = [Review On] - [Last Reviewed]

When I try to use [Days Remaining] column in Filter Query, I get this error "The field of type 'Calculated' cannot be used in the query filter expression."

 

Is there an alternative way to achieve this? I tried using Condition, the flow works but it takes around 6 to 7 minutes to run the flow and the flow goes through all 1000+ documents one by one.

 

7 REPLIES 7
Mira_Ghaly
Dual Super User II
Dual Super User II

@MaanR 

Can you please check the below topic reply it can give you an idea to work around the calculated Limitations:

https://powerusers.microsoft.com/t5/Building-Flows/Odata-filter-query-help-less-than-or-equal-to-tod...

So what you need is similar to the above, you need to construct the Review On in your Flow based and then do the filter query based on the Review On which you have  calculated in your flow.

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
takolota
Power Participant
Power Participant

@MaanR 

 

You can also use the Filter array action. It’s like the condition set-up, but it handles all 1000 of those condition checks in a single quick action.

MaanR
Frequent Visitor

Hi Mira,

Thanks for the response. The problem is that all the documents don't have the same review period. That's why I am using a calculated Review On date. I think the calculated time post you shared will only work if the review period is for all documents.

MaanR
Frequent Visitor

Hi Takola,

Filter array works. But the challenge for me is that I don't know how to extract information from Filter Array Body. I need to extract Document Name, Owner Email and Document Link.

takolota
Power Participant
Power Participant

@MaanR 


After the Filter array, put a Parse JSON action.

Then run the flow so it fails on the Parse JSON action. Go to the Filter array action in the failed run, & copy the Outputs section of the Filter array.

Then go back to edit mode & go to the Parse JSON action. Select Generate schema from sample & in the pop-up paste the Outputs from the Filter array action run & select Ok.

 

That should then give you the Filter array results in your dynamic content menu.

MaanR
Frequent Visitor

Hi @takolota 

Thank you. Parse JSON worked great for 1 document. But when I tested it with 2 documents, I got an error. There will always be multiple documents falling in the same review period.

 

I built the flow by putting Parse JSON inside Apply to Each Container. See the screenshots below. Any tips to solve this would be great.

MaanR_0-1649857717107.pngMaanR_1-1649857810426.pngMaanR_2-1649857921172.png

 

takolota
Power Participant
Power Participant

@MaanR 

The Parse JSON should already include the data for each document without the Apply to each.

Just completely remove Apply to each 2. Apply to each 3 should already iterate through each document from the adjusted data in the Parse JSON.

 

Although, if you are trying to share file contents in the email, then you will need to include a Get contents action in the same loop as the email.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (1,314)