Caml-Query Dropdowns (very large List Lookup dropdown)

10 18 10.5K

According to Leif Frederiksen‌ last post Filtering large List Lookup dropdowns ...‌

I had the problem with a real big dropdown (nearly 20.000 entries). So the code of Leif was not suitable for me because it took too long to filter (blank out) all the entries that are not needed.

I decided to do a workaround and figured out another way to solve the problem. Why not do a CAMLquery on the whole list and show only the results in the dropdown? So I have the full control of the query (i.e. "Contains").

In this example I show you how I did the search/filter for our suppliers. My users can choose a category and the search works with "contains".

There are too many entries we have to deal with...

This looks more comfortable! . So, how to do it?

1) Save the attached JavaScript file in a place from where you can reference it. As Leif I usually store my scripts under Site Assets/Scripting. For this example I have stored it as Site Assets/Scripting/supplierfilter.js.

2) Create a dropdown for the categories, a Single Line Text Box and a Button. Then create a second Dropdown (not a lookup field!) underneath it where we can show the results of the search.

3) Go to the settings of the "Category" Dropdown and enter your categories. Please take care of it - it has to be the same wording like the columns of the lookup list!

Then open "Advanced" and switch "Store Client ID in JavaScript variable" to yes. The ID is varCategory

4) Open the settings of the Single Line Textfield and set Store Client ID in JS to yes and name it "varSearchvalue".

5) Now the Control Settings of the second Dropdown - our results Dropdown! Store ID - yes, and name it "varDropdown".

At Formatting enter a name in the first CSS-class. It is important that it ends with " customDropDown". (Don't miss the whitespace!)

6) At least the Button to start the search: Switch to JavaScript and name it "Search". Open the category advanced and enter at Userclick (Change the URL to your lookuplist i.e. 'http://wf.t-intranet/002/BE'):

retrieveListItems('yourUrl to lookuplist', varCategory, varSearchvalue);

7) Open the Form Settings and reference the file supplierfilter.js

Click save and that's it. But there will be a prolem. The search works fine but you will get an "invalid view stage" error when you run it and try to submit or cancel. To avoid that do the following steps:

😎 Create another Single Line Textfield and connect it to your list (this is the only one which is connected and save the chosen entry!). Activate JS-Client ID and name it "varSupplier".

Hide it with css-code so it will not show up in the form. (Please do not hide it with the Settings in "Appearance" - this won't work!)

9) Open the Settings of the cancel button and Switch it to JavaScript. Name it and enter an url that suits you into Userclick: 

window.Location.href = 'your URL in case of cancel'; 

This is also necessary to avoid the error.

10) Open the Settings of the submit button and enter the following snippet in Userclick:


This will start the Javascript to avoid the error. It stores the value of the Dropdown (not the Text!) in the hidden textfield and deactivate the Dropdown. In this case there will be no error!

11) At least you have to Change a few things in the JavaScript:

- Line 9 - getByTitle('yourListName');  = Please note that this is the internal name!

- Line 55+56 - ddval = Value you want to save. / ddtext = value you want to show your users. = This is very useful because I want to Show my users the suppliername and store the suppliernumber in the order.

-  Line 86 - If you have more than one Dropdown - please add more. Or Change the variables if you choose different names for them into your form.

- Line 97 - Here is the separator to disable the Dropdowns.

So - this it is in the end. I hope this helps...

Here is the JS-Code for fast access:

function retrieveListItems(siteUrl, category, searchvalue) {

  // Get content from the Nintex Form
  var category = NWF$('#' + category).val();    
  var searchvalue = NWF$('#' + searchvalue).val();

  // Query Lookup List
  var clientContext = new SP.ClientContext(siteUrl);
  var oList = clientContext.get_web().get_lists().getByTitle('Supplier');
  // CAML Abfrage
  var camlQuery = new SP.CamlQuery();
    '<View>' +
       '<Query>' +
         '<Where>' +
            '<Contains>' +
              '<FieldRef Name=\'' + category + '\'/><Value Type=\'Text\'>' + searchvalue + '</Value>' +
            '</Contains>' +
          '</Where>' +
          '<OrderBy>' +
            '<FieldRef Name=\'' + category + '\' Ascending=\'true\' />' +
          '</OrderBy>' +
       '</Query>' +
  this.collListItem = oList.getItems(camlQuery);
  // asynchron Query
    Function.createDelegate(this, this.onQuerySucceeded),
    Function.createDelegate(this, this.onQueryFailed)

// Query successful - Get Data!
function onQuerySucceeded(sender, args) {
  var category = NWF$('#' + varCategory).val();
  var listItemInfo = '';
  var listItemEnumerator = collListItem.getEnumerator();
  // Force Nintex Dropdown
  var dropdown = NWF$('#' + varDropdown);
  var ddval = '';
  var ddtext = '';
  // Empty Nintex leeren
  dropdown.append(NWF$('<option></option>').val('').html('Please choose a supplier.')); 

  // Filter Data and store in Dropdown    
  while (listItemEnumerator.moveNext()) {
     var oListItem = listItemEnumerator.get_current();
     // Set Data (val = Data we want to save / html = Lookup Text the user will see)
     ddval = oListItem.get_item('CompanyNo');
     ddtext = oListItem.get_item('Company') + '   -   ' + oListItem.get_item('PostCode') + ' ' + oListItem.get_item('Ort');

// Error ?
function onQueryFailed(sender, args) {    
  alert('Search not successfull! ' + args.get_message() +
    'n' + args.get_stackTrace());

// Store data in an extra field to avoid the error "invalid view state".
// The dropdowns will be deactivated and the data will take over from the extra textfields.
var Buttons = {
     // This function is configured within Nintex Forms to trigger on click of Save/Submit
     onSave: function () {

var DropDowns = {
     // Populates hidden single-line textbox with drop-down value (workaround for invalid view-state)
     populateTextBoxes: function () {
         DropDowns.populateTextBox(varSupplier, varDropdown);
     // Populates associated textbox with selected value from Choice Control (pt1 : workaround for invalid view-state)
     // @param {string} labelID : JavaScript Client ID variable for the hidden textbox. 
     // @param {string} dropdownID : JavaScript Client ID variable for the choice control associated with the hidden textbox. 
     populateTextBox: function (labelID, dropdownID) {
         var obj = NWF$('#' + labelID);
         obj.val(NWF$('#' + dropdownID + ' option:selected').val());  // val = Felddaten (s.o.) / text = Anzeigetext übernehmen
     // Disables all choice controls (pt2 : workaround for invalid view-state)
     disableAll: function () {
         NWF$('.customDropDown').prop('disabled', true);