Cross Site Lookup Filter

Discussions about Forms Designer for SharePoint 2013 / 2016 and Office 365.
Locked
Cloud.Ed
Posts: 40
Joined: Fri Dec 04, 2015

16 Jan 2016

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".

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

18 Jan 2016

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
Posts: 40
Joined: Fri Dec 04, 2015

26 Jan 2016

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.

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

27 Jan 2016

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
Posts: 40
Joined: Fri Dec 04, 2015

19 Feb 2016

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.

User avatar
rostislav
Moderator
Posts: 364
Joined: Mon Oct 19, 2015

22 Feb 2016

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.

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests