Problem with ODATA filter compare a dropdown field to a text field

[SystemX17]

I have 2 columns that I am trying to compare inside a SharePoint GetItems

The fields are as follows:

auditResponseType - 4 options from a prepopulated dropdown list

auditLastResponse - a text field that eventually stores a copy of the auditResponseType Value

I have tried the following ODATA filters when comparing the two:

auditResponseType ne auditLastResponse - not valid

[auditResponseType Value] ne auditLastResponse - not valid

I believe it's just a problem with the auditResponseType being chosen from a dropdown and the filter is just not sure what I am trying to achieve. I would really appreciate some help with this if anyone can. TIA

[SystemX17]

auditResponseType ne auditLastResponse results in "Fetching items failed. Possible invalid string in filter query."

I know that the column names are correct because if I change auditReponseTyp (without the 'e') the error changes to column does not exist. Same with auditLastRepons (again without 'e')

[Jcook]

Hello @SystemX17

Can you try:
auditResponseType ne ‘auditLastResponse’

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[SystemX17]

Copy and paste I get this:

The expression "auditResponseType ne ‘auditLastResponse’" is not valid.

If I use single quotes it is looking for the exact text "auditLastResponse" by the looks of it as it returns everything. Include the entries where the auditResponseType is the same as the auditLastResponse.

[Jcook]

Hi @SystemX17 

What exactly are you trying to do with your Filter?

If auditLastResponse was a dynamic value than use that.

Just add single quotes to it

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[SystemX17]

I am using a dropdown list called auditResponseType that has the possible values of { Pending, Endorsed, Signed Off, Feedback Received }. I am monitoring changes to this value once it goes from Pending -> Signed Off or literally any other combination.

When I detect a change, I update auditLastResponse to the current value of auditResponseType so that I can confirm I have processed the current state.

So auditResponseType comes from a drop down, the value is used to update auditLastResponse (Single text line) and then I want to run an ODATA filter to get a list of all of the items that don't have matching auditResponseType and auditLastResponse.

Hope that helps.

[SystemX17]

auditLastResponse is a column name, not a value

[SystemX17]

I keep trying lots of different combinations but putting auditLastResponse in single quotes seems to just be treated as a text value of 'auditLastResponse'. I am not sure how the filtering works out the difference in this method between using 'auditLastResponse' as a column to pull data out of and 'auditLastResponse' as a literal word that the comparison is being performed against. All attempts of doing it how you have said seem to thing the string value is "auditLastResponse" not the string value contained within the data column titled auditLastResponse.

[v-bacao-msft]

Hi @SystemX17 ,

Unfortunately, the field with choice type is not supported in Filter Query.

Please consider use Filter array or Condition to filter items.

Please check this blog and get more details about Filter Query:

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

In addition, when we use a choice field to filter items, we need to select Choice Value.

Best Regards,

[SystemX17]

@v-bacao-msft I seem to be able to use it ok when asking if it contains a value..

These filters work:

auditResponseType eq 'Pending'

auditLastResponse eq 'Pending'

Just not

auditResponseType eq auditLastResponse

I figured they were just a comparison and if it is able to compare a string field to a string then it would be able to compare a string field to a string field. Does the comparison prevent specifically comparing columns against other columns or is it just if one of the columns is a Choice?

[SystemX17]

@v-bacao-msft like this..

ID	auditResponseType	auditLastResponse
1	Pending	Pending
6	Pending	Signed Off

OData Filter attempted	Result
auditResponseType eq 'Pending'	[1,6]
auditLastResponse eq 'Pending'	[1]
auditLastResponse eq 'Signed Off'	[6]
auditResponseType eq auditLastResponse	Error
auditLastResponse eq auditResponseType	Error

[v-bacao-msft]

Hi @SystemX17 ,

The parameters that are generally configured after the operator need to have actual values.

If only the field name is configured, not only can it not get the value of this field, but it will only compare its field name as a parameter. If the field value can be obtained through Dynamic content in the previous step, then we can configure the Dynamic content with the same name as the specified field to reference the field value.

Best Regards,

[v-bacao-msft]

Like:

Best Regards,

[SystemX17]

@v-bacao-msft I am not sure how to accomplish this. This is my flow:

SharePoint Get Items (filter to retrieve only where the data in Column1 is not the same as the data in Column2)

I am guessing there is a reason for not allowing a "ne" between 2 columns but it seems to mean that ODATA filters are extremely limiting. Only allowing one column against a fixed string. Unless I go for full text and do this:

(auditResponseType eq 'Pending' and auditLastResponse ne 'Pending') or (auditResponseType eq 'Feedback Received' and auditLastResponse ne 'Feedback Received') or (auditResponseType eq 'Endorsed' and auditLastResponse ne 'Endorsed') or (auditResponseType eq 'Signed Off' and auditLastResponse ne 'Signed Off') or (auditResponseType eq 'Cancelled' and auditLastResponse ne 'Cancelled') or (auditLastResponse eq '')

[SystemX17]

@v-bacao-msft that goes against the article you linked. It says getting all of the items in a Get Items is inefficient. As I am getting all of the results, the solution you have there is no different than get all the items, then filter them out. I am guessing this is just a limitation of ODATA being outdated or something. Which means ODATA filtering is not the correct type of filter to be using for what I am trying to achieve if that is the recommended practice.

Going to be loading up a LOT of list items just to find out where 2 columns don't match. Possibly bringing up over a thousand results and then filtering them instead of just bringing up the one that it is happening in.

Thanks for your help. ODATA filtering is just the wrong tool based on all this. Thanks anyways for your help. I really appreciate it.

[v-bacao-msft · Accepted solution]

Hi @SystemX17 ,

Yes, comparing the values of two fields of the same item in Filter Query is not supported. Filter array is an option to consider.

Best Regards,