Filter Query - Wont let me use and

(0) Share

Report

Posted on by Community Power Pla...

Hey All,

So filter query is no longer my best friend..For some reason its driving me made. I want to use it but keeps erroring.

Heres what I have.

CompletedDate ge addDays(utcNow(),-10,'yyyy-MM-dd') and 3MonthCheck eq true

Getting the following error

{"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"CompletedDate ge '2022-03-04' and 3MonthCheck eq true\" is not valid."}}}

I tried adding a condition rather than adding it in the filter query..It works but rather then sending me one email with a table of all the data that meets the conditions I get 1 email for each match and the table as well.

Where am I going wrong.

Thanks L

All responses (7)

Answers (1)

Community Power Pla... on at

Like (0)

Report
Re: Filter Query - Wont let me use and

I understand what you're saying now. Thank you very much that worked...It was looking for this

OData__x0033_MonthCheck_x003f_
Changed that and boom!

Amazing thanks for you patience.
Verified answer

KvB1 1,596 on at

Like (0)

Report

Re: Filter Query - Wont let me use and

The name of the column as you see it in sharepoint isn't always how its seen by PA. You need the name as PA sees it. To figure that out you run the fetch items from sharepoint block without the filter thats giving you problems, so you can see what it fetches, and what the columns, and other things you might want to use as a filter criteria, are called.
Community Power Pla... on at

Like (0)

Report
Re: Filter Query - Wont let me use and

body":{"status":400,"message":"Column '_x0033_MonthCheck_x003f_' does not exist. It may have been deleted by another user
"$filter": "_x0033_MonthCheck_x003f_ eq true"
So just messing about, doing my own testing. If I create a new column that doesn't have some dodgy reference it works
Example - I made a threeMonthCheck column and replace the filter and it all is well.
I'll have to make some changes to my add to see the new column (which is fine but still be good to know why it doesn't work)
KvB1 1,596 on at

Like (0)

Report

Re: Filter Query - Wont let me use and

If you just use the odata filter without the month check: CompletedDate ge '@{addDays(utcNow(),-10,'yyyy-MM-dd')}'

You should get returns since this part of the filter seems to work fine. In the output, it will show the name of the column in the way its recognized by PA
Community Power Pla... on at

Like (0)

Report
Re: Filter Query - Wont let me use and

Thanks for your quick reply. I've checked the output but i must admit not totally sure what i'm looking for everything looks to be right.
"$filter": "CompletedDate ge '2022-03-04' and _x0033_MonthCheck_x003f_ eq true"

I see this from the output download.
"status":400,"message":"Column '_x0033_MonthCheck_x003f_' does not exist. It may have been deleted by another user
KvB1 1,596 on at

Like (0)

Report

Re: Filter Query - Wont let me use and

In the output of the fetch items action it should say the exact phrasing of the column, as automate sees it

Also, if you dont need to create an action for the condition if it fails, you can add this in a filter or maybe even http request if you feel like doing that (would make it more flexible)
Community Power Pla... on at

Like (0)

Report

Re: Filter Query - Wont let me use and

Ok just checking a couple of things. I double checked and saw my column name was incorrect so i've change that and the error is different.

CompletedDate ge '@{addDays(utcNow(),-10,'yyyy-MM-dd')}' and _x0033_MonthCheck_x003f_ eq true

Column '_x0033_MonthCheck_x003f_' does not exist. It may have been deleted by another user.