Cross Site Lookup Filter

[Cloud.Ed]

I'm trying to filter a Cross Site Lookup using multiple criteria. Please see code below:

function (term, page) {
var myVar = '1';
var myStatus = 'Active';
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status&$orderby=Created desc&$filter=Master eq " + myVar + " and Status eq " + myStatus + "&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status&$orderby={LookupField}&$filter=startswith({LookupField}, '" + term + "') and Master eq " + myVar + " and Status eq " + myStatus + "&$top=10";
}

The Master field is a text field and I can get the filter to work if I only use it. The Status field is a choice dropdown and I cant figure that part out. When I try to filter on the Choise Dropdown type of fields the Cross Site Lookup "Fails to Load".

[rostislav]

You'll have to add an expand attribute and reference that particular column in the referenced list that your lookup is pointing to (most probably it's Title):

Code: Select all

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status/Title&$expand=Status&$orderby=Created desc&$filter=Master eq " + myVar + " and Status/Title eq '" + myStatus + "'&$top=10";

Notice the single quotes around the status value, the expand attribute and the '/Title' part added after "Status".

[Cloud.Ed]

I see. The next issue I'm having is using an "or" on the query. We would like to compare the status to "Active" or "Pending" I just can't seem to get the "or" added to the filter.

[rostislav]

That should do it:

Code: Select all

return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status/Title&$expand=Status&$orderby=Created desc&$filter=Master eq " + myVar + " and (Status/Title eq 'Active' or Status/Title eq 'Pending')&$top=10";

[Cloud.Ed]

I'm still having problems using a choice filed to filter on. When I add the "Status/Title" and then the $expand it fails to load.


Thank you.

[rostislav]

Oh, sorry! I misread your question. I told you how to filter by a lookup field, not a choice field. You're simply missing quotes around values.

Code: Select all

function (term, page) {

	var myVar = '1';

	var myStatus = 'Active';

	if (!term || term.length == 0) {

		return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status&$orderby=Created desc&$filter=Master eq '" + myVar + "' and Status eq '" + myStatus + "'&$top=10";

	}

	return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Master,RelationName,Status&$orderby={LookupField}&$filter=startswith({LookupField}, '" + term + "') and Master eq '" + myVar + "' and Status eq '" + myStatus + "'&$top=10";

} 

If you still don't get correct results, do this:

1. Open up your browser's console (F12-> console)

2. Open the cross site lookup dropdown

3. Observe the error in the console

4. Copy the link from the error message, paste into the address box and navigate to the page. The page should give you an explanation of what's wrong with the query. If you cannot resolve the issue, come back to us and paste in the error message here along with the URL.