How to have lookup content filtered by CAML?

[senglory]

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.

[Nikita Kurguzov]

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!

[senglory]

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!

[Nikita Kurguzov]

You'll need to use something like this in your code:

$filter=Lookup/Id eq null

[senglory]

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?

[Nikita Kurguzov]

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.

[senglory]

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);

[Nikita Kurguzov]

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.

[senglory]

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?

[Nikita Kurguzov]

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.