SharePoint REST query select items where column values are not equal

Question

What is the REST query to obtain all items from a list where the value of two different columns for a given item are different.

Take the query: http://<site_collection>/_api/web/lists/getbytitle(<list_name>)/items?$select=ePR_x0020_NameId,ePurchaseRequestName&$filter=(ePR_x0020_NameId ne ePurchaseRequest)

ePR_x0020_NameId is a lookup column with the title "ePR Name". How do I properly use the $expand parameter to get the text of this column rather than the numeric value that is returned?

The query

http://<site_collection>/_api/web/lists/getbytitle(<list_name>)/items?$select=ePR_x0020_NameId,ePurchaseRequestName

returns ePR_x0020_NameId as numeric value, not the string that is in the lookup column.

So when I try to add a filter parameter to this query to return only items where "ePR Name" and "ePurchaseRequestName" are not equal, I get a "The webpage cannot be found".

What is the proper REST query to return items where the column values "ePR Name" and "ePurchaseRequestName" are not equal?

Answer 1

I'm not sure you'll be able to do that. Although it does seem like in the oData spec you should be able to filter by comparing two fields, the examples given in that answer look like they are referencing oData v4, and SharePoint implements oData v3.

In any case, I tried a bunch of tests and whenever I tried to add a filter to compare two fields against each other, I got

{
    "error": {
        "code": "-2147024809, System.ArgumentException",
        "message": {
            "lang": "en-US",
            "value": "Value does not fall within the expected range."
        }
    }
}

even when comparing two text fields against each other, much less trying to comapre a text field to an expanded lookup field.

If you were able to do it, my guess is the query would look something like

http://<site_collection>/_api/web/lists/getbytitle(<list_name>)/items?$select=ePR_x0020_Name/Title,ePurchaseRequestName&$expand=ePR_x0020_Name&$filter=(ePR_x0020_Name/Title ne ePurchaseRequest)

that is, assuming the lookup is to the Title field in the lookup list. Without the filter, this will let you see the text value of the lookup instead of the Id:

http://<site_collection>/_api/web/lists/getbytitle(<list_name>)/items?$select=ePR_x0020_Name/Title,ePurchaseRequestName&$expand=ePR_x0020_Name