cancel
Showing results for 
Search instead for 
Did you mean: 

Caml-Query Dropdowns (very large List Lookup dropdown)

oliverschneider
Nintex Newbie
10 18 6,258

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:

8) 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:

NWF$(document).ready(function(){Buttons.onSave()});

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();
  camlQuery.set_viewXml(
    '<View>' +
       '<Query>' +
         '<Where>' +
            '<Contains>' +
              '<FieldRef Name=\'' + category + '\'/><Value Type=\'Text\'>' + searchvalue + '</Value>' +
            '</Contains>' +
          '</Where>' +
          '<OrderBy>' +
            '<FieldRef Name=\'' + category + '\' Ascending=\'true\' />' +
          '</OrderBy>' +
       '</Query>' +
     '</View>'
  );
  this.collListItem = oList.getItems(camlQuery);
  // asynchron Query
  clientContext.load(collListItem);
  clientContext.executeQueryAsync(
    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.empty();
  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');
    
     dropdown.append(NWF$('<option></option>').val(ddval).html(ddtext));
  }
}


// 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 () {
         DropDowns.disableAll();
         DropDowns.populateTextBoxes();
     }
};


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

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
18 Comments
leif_frederikse
Nintex Newbie

Hi Oliver,

This is quite an impressing and creative solution to this problem - thanks for sharing

What's up with the "my JS is not quiet good" comment on https://community.nintex.com/community/dev-talk/blog/2016/12/22/filtering-large-list-lookup-dropdown...? You seem to be a fast learner

Regards

Leif

oliverschneider
Nintex Newbie

Hi Leif,

thank you - Yes maybe I am... ;-)

I start learning JavaScript two/three weeks ago. I have knowledge in  html/php/css and vb/vba. This helps a lot beause it is similar to JS. In this case I did a lot of Research in Internet and combine a few tutorials to build my own code. The rest was try and error...

Thanks to all the Autors of the tutorials !

Regards,

Oliver

emha
Nintex Newbie

great article and interesting approach.

I have one question, though.

you only described how to set it up allow selection from big filtered list. but how do you handle VIEW form, resp. how do you populate existing value to the dropdown?

oliverschneider
Nintex Newbie

Hi Marian,

good question.

In such a case I use the Single Text Field (Step 8 - varSupplier) to Show it at View mode. This is the field with the saved informations in my list.

Simply delete the CSS-Code for hiding it and work with a Rule: Formatting /  =Is New Mode / Hide. The other way with the Dropdown. If I need more informations I work with lookup fields in the view mode.

rhia
Nintex Newbie

Oh wow, this is wonderful. Definitely going to try this out.

Thank you for writing it up so well!

c-kemme
Nintex Newbie

Is it possible to do a second camlquery after the first? I want the result of the first query to be used in a second query as the searchvalue.

oliverschneider
Nintex Newbie

Hi Christian,

yes this is possible.

I use this code for other changes on my forms. You have to copy the functions "retreiveListItems" and "onQuerySucceeded". Alter the names and configre them to the new list and query.

The trick is to watch at the first Dropdown on Change and then start the new query:

NWF$('#'+ varDropdown).change(
function() {
var myselection = NWF$('#' + varDropdown + ' option:selected');
var selectionText = myselection.val();

retrieveListItems2('http://wf.intranet/002/BE', selectionText);
}
);
c-kemme
Nintex Newbie

Thanks Oliver!

You approved what I've already tried and thought it won't work.

I reviewed the script and now it works!

If someone is interested  I can post the script, but basically it's the same Oliver did but with a second caml query.

Thanks again Oliver!

kovinis
Nintex Newbie

Hi,

it would be kind, if you could post the script with a second caml query.

brightlight
Nintex Newbie

Does this work on nintex mobile apps?

oliverschneider
Nintex Newbie

Hi Bright Light,

that is a good question. We don't use mobile Apps. I don't know if it uses JavaScript and jQuery. Could be a problem at  that point.

cherylshah
Nintex Newbie

Thank you for posting this - I could never come up with this on my own!

Is there a way to adapt this to the case where users are selecting multiple values from a lookup field? For example a technician is assigned a task and must add all the pieces of equipment they are using to perform the task, from a lookup to a list of all the equipment.

akrasheninnikov
Nintex Newbie

You can query once again, but if you only need to narrow the filter down, why not reuse the data you already have in memory?

akrasheninnikov
Nintex Newbie

If the count of values doesn't exceed 32, you can use the <In><FieldRef LookupId='TRUE' Name=.../><Values><Value ...>...</Value><Value ...>...</Value></Values></In> clause in your camlQuery. 

Array.prototype.join() could be handy

cherylshah
Nintex Newbie

Thank you for your reply Alexey Krasheninnikov‌. There are many more than 32 items in the equipment list. Or do you mean the number of values that a user would possibly select?

akrasheninnikov
Nintex Newbie

I meant the latter. The count of item IDs you would use in the query.

cherylshah
Nintex Newbie

I'm not familiar with CAML - would the <In><FieldRef LookupId='TRUE' Name=.../><Values><Value ...>...</Value><Value ...>...</Value></Values></In> clause go at line 93?

akrasheninnikov
Nintex Newbie

No, lines 16-18, inside 

camlQuery.set_viewXml()

Instead of 

            '<Contains>' +
              '<FieldRef Name=\'' + category + '\'/><Value Type=\'Text\'>' + searchvalue + '</Value>' +
            '</Contains>'