How to have lookup content filtered by CAML?

Discussions about Cross-site Lookup
senglory
Posts: 11
Joined: Tue Sep 19, 2017

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";
}

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

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";
}
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

30 Sep 2017

Hi,

Nope, this code still not working. In your dropdown I get "Load failed", in Chrome console I see this stuff: Image
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?

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

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
InternalNameColumn.png (17.04 KiB) Viewed 1894 times
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

02 Oct 2017

Hi,

Here's the description of my "Categories" list:

Content Type ID | ContentTypeId | ContentTypeId
Title | Text | Title
Approver Comments | Note | _ModerationComments
File Type | Text | File_x0020_Type
ParentCategory | Lookup | ParentCategory
ID | Counter | ID
Content Type | Computed | ContentType
Modified | DateTime | Modified
Created | DateTime | Created
Created By | User | Author
Modified By | User | Editor
Has Copy Destinations | Boolean | _HasCopyDestinations
Copy Source | Text | _CopySource
owshiddenversion | Integer | owshiddenversion
Workflow Version | Integer | WorkflowVersion
UI Version | Integer | _UIVersion
Version | Text | _UIVersionString
Attachments | Attachments | Attachments
Approval Status | ModStat | _ModerationStatus
Edit | Computed | Edit
Title | Computed | LinkTitleNoMenu
Title | Computed | LinkTitle
Title | Computed | LinkTitle2
Select | Computed | SelectTitle
Instance ID | Integer | InstanceID
Order | Number | Order
GUID | Guid | GUID
Workflow Instance ID | Guid | WorkflowInstanceID
URL Path | Lookup | FileRef
Path | Lookup | FileDirRef
Modified | Lookup | Last_x0020_Modified
Created | Lookup | Created_x0020_Date
Item Type | Lookup | FSObjType
Sort Type | Lookup | SortBehavior
Effective Permissions Mask | Computed | PermMask
Name | File | FileLeafRef
Unique Id | Lookup | UniqueId
Client Id | Lookup | SyncClientId
ProgId | Lookup | ProgId
ScopeId | Lookup | ScopeId
HTML File Type | Computed | HTML_x0020_File_x0020_Type
Edit Menu Table Start | Computed | _EditMenuTableStart
Edit Menu Table Start | Computed | _EditMenuTableStart2
Edit Menu Table End | Computed | _EditMenuTableEnd
Name | Computed | LinkFilenameNoMenu
Name | Computed | LinkFilename
Name | Computed | LinkFilename2
Type | Computed | DocIcon
Server Relative URL | Computed | ServerUrl
Encoded Absolute URL | Computed | EncodedAbsUrl
File Name | Computed | BaseName
Property Bag | Lookup | MetaInfo
Level | Integer | _Level
Is Current Version | Boolean | _IsCurrentVersion
Item Child Count | Lookup | ItemChildCount
Folder Child Count | Lookup | FolderChildCount
App Created By | Lookup | AppAuthor
App Modified By | Lookup | AppEditor

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 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
1_SourceList.png (15.81 KiB) Viewed 1889 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
2_NotNullSettings.png (40.95 KiB) Viewed 1889 times
Here are my results for NOT Null:
3_NotNullResults.png
3_NotNullResults.png (3.43 KiB) Viewed 1889 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
4_OnlyNullSettings.png (37.3 KiB) Viewed 1889 times
Here are my results for IS Null:
5_OnlyNullResults.png
5_OnlyNullResults.png (3.51 KiB) Viewed 1889 times
Hope these examples are good and you'll get it to work for you as well. Let me know!
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

04 Oct 2017

Hi,

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

against this list:
Image


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?

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

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

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest