Unanswered

Can't use calculated category in query filter expression

(0) Share

Report

Posted on by DJfinley

I am stuck here. I created an initialized variable with the following function: addDays(utcNow(),3,'MM/dd/yyyy'). I then created an action of Get items- Sharepoint and in the filter query I have a calculated column equal to my initialize variable. I received an error message that stated I couldn't use a calculated category in query filter expression. I was forwarded to trying a Filter Array but I'm lost. When I test my flow, it's still failing. Essentially what I would like to have happen is to be able to have scheduled alerts for 3 days, 7 days and 14 days before the due date mailed to specific individuals. I've set the recurrence to run daily to check for the due dates with the "# of days before" parameters. I've attached screenshots below. Any help would be appreciated.

3DaysOut - Initialize Variable Coding:

Get Items- Return 3 Days Estimated Completion Date Tx Plan Coding:

This is as far as I've gotten with the filter array but it's failing. Here's the coding:

I have the same question (0)

All responses (7)

Answers (0)

David_MA 12,515 Super User 2025 Season 2 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

Query filters require the date to be in ISO 8601 format. Change your date format to yyyy-MM-dd instead of MM/dd/yyyy and it should work. Your date value is actually stored in the ISO format in SharePoint.

This is an example in one of my workflows:
This is the first expressions: addDays(utcNow(),-8,'yyyy-MM-dd')

Was this reply helpful? Yes No
DJfinley 20 on at

Like (0)

Report
Re: Can't use calculated category in query filter expression

Thank you for responding so quickly. This is my first time using PowerAutomate. Do you mean to change the date format in my initialize variable as well or just in my filter query. I am still receiving the error message" The field of type 'Calculated' cannot be used in the query filter expression. The output I've received reflects:

{"statusCode":400,"headers":{"Vary":"Origin","X-NetworkStatistics":"59,4204800,0,1569,676470,4204800,2359026","X-SharePointHealthScore":"1","X-MS-SPConnector":"1","X-SP-SERVERSTATE":"ReadOnly=0","DATASERVICEVERSION":"3.0","SPClientServiceRequestDuration":"52","X-DataBoundary":"None","X-1DSCollectorUrl":"https://mobile.events.data.microsoft.com/OneCollector/1.0/","X-AriaCollectorURL":"https://browser.pipe.aria.microsoft.com/Collector/3.0/","SPRequestGuid":"db477f60-c541-4f41-bd38-203e9058ab85","request-id":"db477f60-c541-4f41-bd38-203e9058ab85","MS-CV":"YH9H20HFQU+9OCA+kFirhQ.0","Strict-Transport-Security":"max-age=31536000","X-FRAME-OPTIONS":"SAMEORIGIN","Content-Security-Policy":"frame-ancestors 'self' teams.microsoft.com *.teams.microsoft.com *.skype.com *.teams.microsoft.us local.teams.office.com *.powerapps.com *.yammer.com *.officeapps.live.com *.office.com *.stream.azure-test.net *.microsoftstream.com *.dynamics.com *.microsoft.com securebroker.sharepointonline.com;","MicrosoftSharePointTeamServices":"16.0.0.23102","X-Content-Type-Options":"nosniff","X-MS-InvokeApp":"1; RequireReadOnly","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","x-ms-apihub-obo":"false","Cache-Control":"max-age=0, private","Date":"Mon, 21 Nov 2022 19:15:38 GMT","P3P":"CP=\"ALL IND DSP COR ADM CONo CUR CUSo IVAo IVDo PSA PSD TAI TELo OUR SAMo CNT COM INT NAV ONL PHY PRE PUR UNI\"","X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Length":"266","Content-Type":"application/json","Expires":"Sun, 06 Nov 2022 19:15:38 GMT","Last-Modified":"Mon, 21 Nov 2022 19:15:38 GMT"},"body":{"status":400,"message":"The field 'Tx_x0020_Plan_x0020_Due_x0020_Da' of type 'Calculated' cannot be used in the query filter expression.\r\nclientRequestId: db477f60-c541-4f41-bd38-203e9058ab85\r\nserviceRequestId: db477f60-c541-4f41-bd38-203e9058ab85"}}

Was this reply helpful? Yes No
David_MA 12,515 Super User 2025 Season 2 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

If you are going to use the output from your variable in the filter, then yes, you would need to change it there as well. But if you need that format for other things in your flow, you can just format it directly in the filter query as I have shown above. You said in your post you are initializing the variable as addDays(utcNow(),3,'MM/dd/yyyy'). So if you are not using the variable anywhere else, then update it to addDays(utcNow(),3,'yyyy-MM-dd') and you should be good. Just be sure to enclose the variable within single quotes in the query filter as I have shown in my prior screen shot.

Was this reply helpful? Yes No
DJfinley 20 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

I am still receiving the same error. Is there a way to get around the calculated column error? I'm needing my tx plan due date to correspond with my "last tx plan date" to prompt an email 180 days from the "last tx plan date". I've created the formulate =[LastTxPlanDate]+180. I updated the variable as you instructed, but still seem to be stuck with the same flow run failed error.

Was this reply helpful? Yes No
David_MA 12,515 Super User 2025 Season 2 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

In your initial post, you said you were trying to use a date variable that you initialized in your flow to filter your SharePoint list. Is the calculated column in your list the one you are trying to use for your filter? If so, you cannot use a calculated column for the filter. You need to use an actual date field.

Is LastTxPlanDate the actual date field in your list that you need to filter on and which you have created a calculated column? If so, and based on what you have shown, you can use that in your filter query. Since your calculated column adds 180 days, and your variable adds three more days, you could add 183 days to get what you want.

Was this reply helpful? Yes No
DJfinley 20 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

My TxPlanDueDate is my calculated column it pings off of my LastTxPlanDate (which is just a regular date column). In my Filter Query, I've changed the TxPlanDueDate (calculated column) to LastTxPlanDate it now looks like this:

Will I also need to change my initialized variable from addDays(utcNow(),3,'yyyy-MM-dd') to addDays(utcNow(),183,'yyyy-MM-dd') as I've done in the Get Items - Filter Query?

Was this reply helpful? Yes No
David_MA 12,515 Super User 2025 Season 2 on at

Like (0)

Report

Re: Can't use calculated category in query filter expression

I don't know what you are using your initialized variable for, so I cannot answer whether it needs to be updated. I assume the filter query is now working as you wanted since you didn't say you got an error. If the expression you are using in the filter query is the same value you need for whatever you are doing with the variable, then you can update that as well. If you are not using the variable for anything now, you could just delete it.

Was this reply helpful? Yes No