How to have lookup content filtered by CAML?

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

19 Sep 2017

Hi,

I'm using Cross-site Lookup 1.4.4 and I need to have master lookup field populated only with records matching particular criteria. How can I have it done? What should I write in function (term, page)? To be more specific, I'd like to have there records where ParentCategory(another lookup field) is empty.

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

20 Sep 2017

Hello!
We have a couple of articles about how you can configure Cross-site Lookup filtering, including Cascading Lookups and regular filtering. Instead of CAML, Cross-site Lookup uses OData queries, you can read more about them here.

If you'll have any questions about specifics of implementation, let us know!
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

20 Sep 2017

Hi,

I looked at the link https://dev.office.com/sharepoint/docs/ ... t-requests you mentioned but it doesn't reveal how to filter records by empty lookup field value. Could you please elaborate the idea? Thanks!

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

20 Sep 2017

You'll need to use something like this in your code:
$filter=Lookup/Id eq null
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

20 Sep 2017

Nope, the doesn't seem to work:
http://sp2013-dev/BNP/_api/web/lists('3 ... 0eq%20null
although my list contains several records with ParentrCategory = null.

At the same time this url:
http://sp2013-dev/BNP/_api/web/lists('3 ... t=Id,Title

returns a lot of records.

What's wrong with my query?

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

21 Sep 2017

Can you send me a screenshot of your code in Advanced section of Cross-site Lookup? Also, a console screenshot from the form when you open Cross-site Lookup might be helpful, if there are any errors.
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

21 Sep 2017

Hi,
Here's the code in "Request items" section:

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


The Advanced section:
http://prntscr.com/go2h2u

The Chrome console:
http://prntscr.com/go2i06

And the "select2_locale_en_us.js" content (taken BTW from https://github.com/select2/select2)

/**
* Select2 <Language> translation.
*
* Author: Your Name <your@email>
*/
(function ($) {
"use strict";

$.fn.select2.locales['en'] = {
formatMatches: function (matches) { if (matches === 1) { return "One result is available, press enter to select it."; } return matches + " results are available, use up and down arrow keys to navigate."; },
formatNoMatches: function () { return "No matches found"; },
formatInputTooShort: function (input, min) { var n = min - input.length; return "Please enter " + n + " or more character" + (n == 1 ? "" : "s"); },
formatInputTooLong: function (input, max) { var n = input.length - max; return "Please delete " + n + " character" + (n == 1 ? "" : "s"); },
formatSelectionTooBig: function (limit) { return "You can only select " + limit + " item" + (limit == 1 ? "" : "s"); },
formatLoadMore: function (pageNumber) { return "Loading more results…"; },
formatSearching: function () { return "Searching…"; }
};

$.extend($.fn.select2.defaults, $.fn.select2.locales['en']);
})(jQuery);

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

22 Sep 2017

Once again, the problems I see are caused by the additional files you've included. As I've answered in another topic, you don't need them. Just delete their content altogether, but keep the files if you don't want to get 404 errors in the console.
Cheers

senglory
Posts: 11
Joined: Tue Sep 19, 2017

22 Sep 2017

Ok, I deleted these files but I'm still getting "no results" with this function (term, page) definition. How should I change it to have root categories (i.e. no parents assigned) returned?

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

22 Sep 2017

The code looks more or less fine to me, although you do also have to change the second part as well. Try this code:

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=Divisions/Id eq null &$top=10";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},ParentCategory/Id&$orderby={LookupField}&$expand=Divisions/Id&$filter=startswith({LookupField}, '" + term + "') and ParentCategory/Id eq null &$top=10";
}
This should work. If it doesn't - show me your console and make sure you are using the correct column name.

P.S. There are mistakes in this code. I didn't replace Divisions with ParentCategory in all the places. If you do, it should work.
Cheers

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest