How to have lookup content filtered by CAML?

senglory · 23 Sep 2017

Nope , still getting "No matches found"

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby=Created desc&$expand=ParentCategory/Id&$filter=ParentCategory/Id eq null &$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby={LookupField}&$expand=ParentCategory/Id&$filter=startswith({LookupField}, '" + term + "') and ParentCategory/Id eq null &$top=10";
}

25 Sep 2017

Are you sure you have items in the Source List with empty ParentCategory field?

I've tested this code prior to sending it and have only replaced the field name to the one you've provided. In order to test that we are calling correct field in the correct list, try to replace eq with ne (not equal). It should be something like this and it should return all values where ParentCategory is not empty.

Code: Select all

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby=Created desc&$expand=ParentCategory/Id&$filter=ParentCategory/Id ne null &$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby={LookupField}&$expand=ParentCategory/Id&$filter=startswith({LookupField}, '" + term + "') and ParentCategory/Id ne null &$top=10";
}

senglory · 30 Sep 2017

Hi,

Nope, this code still not working. In your dropdown I get "Load failed", in Chrome console I see this stuff:

http://prntscr.com/groa6s

Tried to go to this url http://sp2013-dev/BNP/_api/web/lists('3 ... 6798545866:

Got
<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/0 ... s/metadata">
<m:code>-2146232060, Microsoft.SharePoint.SPException</m:code>
<m:message xml:lang="en-US">Exception from HRESULT: 0x80131904</m:message>
</m:error>

What's wrong with my code?

02 Oct 2017

Hello!
I am not exactly sure what is wrong with the code as it should return at least some results. Can you show me the Source List you are using for this Lookup?

Also, make sure that the field we are checking has internal name ParentCategory. You can check it by going to List Settings, clicking on the column and checking the URL. Internal name can be different from displayed name and we need to know the internal name to get the results here.

: InternalNameColumn.png (17.04 KiB) Viewed 5011 times

senglory · 02 Oct 2017

03 Oct 2017

Okay, I am not sure what exactly is wrong then. There were errors I've noticed in the code on the previous page, but the one on this page should work just fine. I will give an example here and leave you the working code I've used, it should finally work I hope.

First, here is my Source List with ParentCategory Lookup field. It has some test values, but some rows are left empty:

: 1_SourceList.png (15.81 KiB) Viewed 5006 times

First, I am filtering to display all values where ParentCategory is NOT Null. I am using this code, same as before:

Code: Select all

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby=Created desc&$expand=ParentCategory/Id&$filter=ParentCategory/Id ne null &$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby={LookupField}&$expand=ParentCategory/Id&$filter=startswith({LookupField}, '" + term + "') and ParentCategory/Id ne null &$top=10";
}

Here are my settings for NOT Null:

: 2_NotNullSettings.png (40.95 KiB) Viewed 5006 times

Here are my results for NOT Null:

: 3_NotNullResults.png (3.43 KiB) Viewed 5006 times

Next, I am filtering to display all values where ParentCategory IS Null. I am using this code, slightly changed from before:

Code: Select all

function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby=Created desc&$expand=ParentCategory/Id&$filter=ParentCategory/Id eq null &$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby={LookupField}&$expand=ParentCategory/Id&$filter=startswith({LookupField}, '" + term + "') and ParentCategory/Id eq null &$top=10";
}

Here are my settings for IS Null:

: 4_OnlyNullSettings.png (37.3 KiB) Viewed 5006 times

Here are my results for IS Null:

: 5_OnlyNullResults.png (3.51 KiB) Viewed 5006 times

Hope these examples are good and you'll get it to work for you as well. Let me know!

senglory · 04 Oct 2017

Hi,

Tried this query:
http://sp2013-dev/BNP/_api/web/lists/ge ... 0eq%20null

against this list:

Got this result:
<?xml version="1.0" encoding="utf-8"?><feed xml:base="http://sp2013-dev/BNP/_api/" xmlns="http://www.w3.org/2005/Atom" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/0 ... s/metadata" xmlns:georss="http://www.georss.org/georss" xmlns:gml="http://www.opengis.net/gml"><id>9aa9342 ... /id><title /><updated>2017-10-04T19:57:10Z</updated><author><name /></author></feed>

What's wrong with the query?

05 Oct 2017

Hello, senglory!

Tried this query:
http://sp2013-dev/BNP/_api/web/lists/ge ... 0eq%20null

What do you mean by "this query"? Unfortunately, I can't use this URL to give any advice as I don't how exactly it's being used.

Can you simply send me a screenshot of your settings for the Cross-site Lookup, including Advanced settings code? That would be most helpful.

Connect with your social network account

How to have lookup content filtered by CAML?

Who is online