Filtering Get Items using Calculated Column

[MaanR]

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.

[Mira_Ghaly]

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

[takolota]

@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]

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]

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]

@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]

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.

[takolota]

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

[ljh]

Hello MaanR. I'm very thankful I came across your post filtering a calculated date column.  Could you please expand the Filter Array and Parse JSON you used?  Thank you!!!