Thursday, 6 January 2011

Fixing SharePoint 2010 Lookup Drop Down 20 Items Limit

As you might have noticed already, SharePoint (both 2007 and 2010) will render your lookup drop down column differently depends on the number of item in the drop down list. If you have less than 20 items, SharePoint will just render the drop down using the normal drop down list control.

However SharePoint will change the rendering style when there is more than 20 items. Instead of the simple drop down list control, it will be changed to a textbox + ajax + filtering list, like the following:

So when you start typing in the textbox, it filters the options, allows the user to select an item from a large list. There are pros, and of course there are cons:
- If you decide to select from the list, you need to do a double click in order to pick an option
- The free-text box gives an impression that you can free-text, which ends up saving empty string
- Inconsistent user experience if you have other drop down list in the form that has less than 20 items.
- There are some rendering issue for the Ajax Drop Down list in SharePoint 2010

To get around this, I have implemented a JQuery solution. Basically the script does the following if the drop down has more than 20 items:
- Hide the text box
- Hide the drop down arrow
- Provision the options in a simple drop down list, register an OnChange event to set the lookup hidden field that SharePoint is using.
- Add the drop down to the page.

Insert the following script to the New/Edit form (by using a content editor web part or custom ribbon javascript). I am assuming that you have a reference to the JQuery library in your master page.

Code Snippet
  1. <script>
  2. $(document).ready(function () {

  3. // Name of the column (Display Name)
  4. var columnName = "Lookup";

  5. // Override the Drop Down List
  6. OverrideDropDownList(columnName);

  7. // Main Function
  8. function OverrideDropDownList(columnName) {

  9. // Construct a drop down list object
  10. var lookupDDL = new DropDownList(columnName);

  11. // Do this only in complex mode...
  12. if (lookupDDL.Type == "C") {

  13. // Hide the text box and drop down arrow
  14. lookupDDL.Obj.css('display', 'none');
  15."img").css('display', 'none');

  16. // Construct the simple drop down field with change trigger
  17. var tempDDLName = "tempDDLName_" + columnName;
  18. if (lookupDDL.Obj.parent().find("select[ID='" + tempDDLName + "']").length == 0) {
  19. lookupDDL.Obj.parent().append("<select name='" + tempDDLName + "' id='" + tempDDLName + "' title='" + tempDDLName + "'></select>");

  20. lookupDDL.Obj.parent().find("select[ID='" + tempDDLName + "']").bind("change", function () {
  21. updateOriginalField(columnName, tempDDLName);
  22. });
  23. }

  24. // Get all the options
  25. var splittedChoices = lookupDDL.Obj.attr('choices').split("|");

  26. // get selected value
  27. var hiddenVal = $('input[name=' + lookupDDL.Obj.attr("optHid") + ']').val()
  28. if (hiddenVal == "0") {
  29. hiddenVal = lookupDDL.Obj.attr("value")
  30. }

  31. // Replacing the drop down object with the simple drop down list
  32. lookupDDL = new DropDownList(tempDDLName);

  33. // Populate the drop down list
  34. for (var i = 0; i < splittedChoices.length; i++) {
  35. var optionVal = splittedChoices[i];
  36. i++;
  37. var optionId = splittedChoices[i];

  38. var selected = (optionId == hiddenVal) ? " selected='selected'" : "";
  39. lookupDDL.Obj.append("<option" + selected + " value='" + optionId + "'>" + optionVal + "</option>");
  40. }
  41. }
  42. }

  43. // method to update the original and hidden field.
  44. function updateOriginalField(child, temp) {
  45. var childSelect = new DropDownList(child);
  46. var tempSelect = new DropDownList(temp);

  47. // Set the text box
  48. childSelect.Obj.attr("value", tempSelect.Obj.find("option:selected").val());

  49. // Get Hidden ID
  50. var hiddenId = childSelect.Obj.attr("optHid");

  51. // Update the hidden variable
  52. $('input[name=' + hiddenId + ']').val(tempSelect.Obj.find("option:selected").val());
  53. }

  54. // just to construct a drop down box object. Idea token from SPServces
  55. function DropDownList(colName) {
  56. // Simple - when they are less than 20 items
  57. if ((this.Obj = $("select[Title='" + colName + "']")).html() != null) {
  58. this.Type = "S";
  59. // Compound - when they are more than 20 items
  60. } else if ((this.Obj = $("input[Title='" + colName + "']")).html() != null) {
  61. this.Type = "C";
  62. // Multi-select: This will find the multi-select column control on English and most other languages sites where the Title looks like 'Column Name possible values'
  63. } else if ((this.Obj = $("select[ID$='SelectCandidate'][Title^='" + colName + " ']")).html() != null) {
  64. this.Type = "M";
  65. // Multi-select: This will find the multi-select column control on a Russian site (and perhaps others) where the Title looks like 'Выбранных значений: Column Name'
  66. } else if ((this.Obj = $("select[ID$='SelectCandidate'][Title$=': " + colName + "']")).html() != null) {
  67. this.Type = "M";
  68. } else
  69. this.Type = null;
  70. } // End of function dropdownCtl
  71. });
  72. </script>
The script will then turn the complex drop down list (if it has more than 20 items) to the simple drop down list:

Originally I extended the SPServices JQuery Library to fix the drop down issue. So you may find that my scripts contains little portion of the library. I have integrated the above solution with the Cascading Drop Down list function provided by the SPServices library. Let me know if you want to obtain the extended script.
Since there are request for the Extended SPServices, I have make it available for download. You can download it from here. (Please rename the extension from .txt to .js)
Few notes before you use this:

1. This extended version is based of SPServices version 0.5.7

2. I have extended SPCascadeDropDowns and called the new function SPCascadeDropDownsEx

3. The original SPServices expects you to have the parent-child list in the following format:
Parent1 - Child1a
Parent1 - Child1b
Parent1 - Child1c
Parent2 - Child2a
Parent3 - Child3a
Parent3 - Child3b

However I have changed this so that it works with a child list and a parent list. For example, I have a child list with list item Child1a, Child1b, Child1c, Child2a, Child3a, Child3b. And then I create a parent list with a multi-valued lookup child colume like this:

Parent1 - Child1a, Child1b, Child1c
Parent2 - Child2a
Parent3 - Child3a, Child3b

(Sorry for the rough explanation, I will include screenshots when I have the system around)

And this is how I invoked the function:
// Enable Cascading Drop down for Department
relationshipList: "Department",
relationshipListParentColumn: "Title",
relationshipListChildColumn: "AreaOfImpact",
parentColumn: "Department",
childColumn: "Area Of Impact",
relationshipListType: "LookupMultiValue",
promptText: "--- Please select ---",
debug: false
So in the above script:
- relationshipList is the parent list
- relationshipListParentColumn is the name of the parent column in the parent list
- relationshipListChildColumn is the name of the childLookup column in the parent list
- parentColumn is the display name of the parent drop down list
- childColumn is the display name of the child drop down list
- relationshipListType is the type of the relationshipListChildColumn in the parent list


sherri said...

Would love to obtain the extended script, because I can get your dropdown override working, and the Cascading dropdown as well - just not both at the same time. I think it may be timing issues - can you give me some pointers? said...


I've had this on my list to add to SPServices for a long time, but I haven't gotten to it. I'd be interested in seeing your code to see how you've done it!


Wilson Leung said...

As requested, I have posted the detail of the extended script. Let me know if you have any problem.

Todd said...


Ive tried copying and pasting your code to fix the dropdown problem but it does not work for me....Dont know what Im doing wrong.

I created a Content Editor Web Part on the New Item page and pasted it as source code. Do I have to edit the script or do I just copy and paste it as is into the web part?

P.s - I am not a programmer.....


Wilson Leung said...

Hi Todd,

Have you download JQuery 1.4.x and add a reference to your masterpage? This script relies on the JQuery API.

André Rentes said...

Hello Wilson Leung,

Great workaround, I just had to modify teh script including \" because it was causing error.

line 25
var hiddenVal = $('input[name=\"' + lookupDDL.Obj.attr("optHid") + '\"]').val();

and line 69
$('input[name=\"' + hiddenId + '\"]').val(tempSelect.Obj.find("option:selected").val());


rishi said...

Please anyone with a better explanation of casacding drop using spservices 0.5.7...???plz...

rishi said...

I m not able to implement cascading dropdown using Jquery Library SPservice 0.5.7...please anyone with a better explanation???

damo said...

Thank you very much

damo said...

Thank you very much :)

Patrick said...

FYI, the Extended SPServices file you have linked is truncated - missing quite a few lines of code. As a result, I was not able to use it -- through I'm trying to use it by copying your new function into a clean version of 0.5.7. Could you please re-host your file?

Wilson Leung said...

Hi Patrick,

I have tried the link and it works for me. Could you please try again? The file is hosted in Google Doc.

Wilson Leung said...

Rishi, if you just want to use the original SPServices Cascading Drop down, here's a good link to it:

If you want to use the extended one, could you please tell me what problem do you have?

quintorel said...

If you want to use this dropdown to implement cascade filter you can use this framework that implement both scenarios: