Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

So… A while back I had to set a SharePoint Lookup drop down list value with JavaScript and ran into the quirk where SharePoint handles Lookup drop downs differently if there are 20 or more items in the list. I’m not exactly sure why SharePoint does this. The only common sense reason I can think of is to pass less data over the wire, but if this were the case why not just be consistent and do the same thing ALL the time????  I guess that would make too much sense.

Anyway, this week I had to do it again, and well, I couldn’t remember exactly how I did it the last time. I did some Googling with Bing and didn’t get the answer I was looking for. I’m SURE this has to be blogged somewhere, but whoever you are you didn’t title it well enough and I didn’t find you! So, I decided to blog it myself… oh, and thanks to Mr. Marc Anderson for being my sanity checker and making sure I was heading in the right direction.

This works for BOTH SharePoint 2010 and the SharePoint formerly known as MOSS by the way…

The Problem

Like I said earlier, someone had the BRILLIANT idea that SharePoint should handle Lookup drop down lists differently if there are 20 or more items in a list… In fact, I can picture how that might have gone… picture if you will…..

A couple of developers, up late at night, just got done with a marathon session of WoW (That’s World of Warcraft for you normal folks)… fingers covered in the yellow deliciousness that is cheetos.. maybe they had a few too many wine coolers…. They reminisce about how awesome Zima was and how they miss their MC Hammer Pants from high school…  Oh crap.. they forgot they had some work they had to do… so they start coding… intoxicated and emotionally high from their latest raid in Darnassus… One guy just bursts out laughing… “Dude!!  Look at this code I wrote!!!  Dare me to commit it???”  “Do it dude!!! I double dare you!!!”

And behold… SharePoint’s handling of Lookup fields with 20 or more entries… I’m kidding of course, I’m sure there is a completely valid reason for why they did it.. I’m probably just not smart enough to understand… so, if you are that developer, I apologize for poking fun out of ignorance… now get back to your game…

The Solution

So… anyway… when you create a Lookup field in SharePoint and there are less than twenty items in the drop down list the html that is generated on the new and edit forms looks something like:

<select name="ctl00$m$g_50e06708_50bb_4560_ab4c_6c3c5a95a113$ctl00$ctl04$ctl01$ctl00$ctl00$ctl04$ctl00$Lookup" 
id="ctl00_m_g_50e06708_50bb_4560_ab4c_6c3c5a95a113_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_Lookup" 
title="Numbers">
                    <option value="0">(None)</option>
                    <option value="8">eight</option>
                    <option value="5">five</option>
                    <option value="4">four</option>
                    <option value="9">nine</option>
                    <option value="1">one</option>
                    <option value="7">seven</option>
                    <option value="6">six</option>
                    <option value="10">ten</option>
                    <option value="3">three</option>
                    <option value="2">two</option>
                </select>

Okay, aside from the HIDEOUS generated name and id, this is exactly what you would expect.  Also note that it is using the Display Name of the field with an attribute called “title” this is uber important when it comes to setting these values with jQuery. We can use the “title” attribute instead of having to know what the elements id is… So, setting the value of a drop down list with 20 or less items is quite easy and can be done with one line of jQuery. If we wanted the above drop down box to display the option with a value of “2”, the jQuery would look like:

$("select[title='Numbers']").val("2");    

Simple? eh? We tell jQuery we are looking for a “select” element with a “title” attribute equal to “Numbers” and we set the value to “2”.  It is important to note, we are setting the OPTION value, not the text displayed in the drop down list. 

Well… what happens if there are more tan 20 items?  Well… ladies and gentlemen… I’m glad you asked… Here is how the same field looks when there are MORE than 20 items:

<input name="ctl00$m$g_50e06708_50bb_4560_ab4c_6c3c5a95a113$ctl00$ctl04$ctl01$ctl00$ctl00$ctl04$ctl00$ctl01" 
type="text" 
id="ctl00_m_g_50e06708_50bb_4560_ab4c_6c3c5a95a113_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_ctl01" 
class="ms-lookuptypeintextbox" onfocusout="HandleLoseFocus()" opt="_Select" 
title="Numbers" 
optHid="SPNumbers_Hiddenctl00$m$g_50e06708_50bb_4560_ab4c_6c3c5a95a113$ctl00$ctl04$ctl01$ctl00$ctl00$ctl04$ctl00" onkeypress="HandleChar()" onkeydown="HandleKey()" match="" 
choices="(None)|0|eight|8|eightteen|18|eleven|11|fifteeen|15|five|5|four|4|fourteen|14|nine|9|nineteen|19|one|1|seven|7|seventeen|17|six|6|sixteen|16|ten|10|thirteen|13|three|3|twelve|12|twenty|20|two|2" 
onchange="HandleChange()" />

Holy crap… reallly? Did you SEE that? We no longer have a select element, we now have an INPUT element… that’s right… the drop down list you actually see is generated using the event handlers in the element. Now, I’ve never dissected these events to see exactly what they are doing (and frankly I don’t care).  What I care about is getting my drop down value set…. Ah… but slow down there rookie, what you also don’t understand yet (or you do understand, and just reading my blog because you are bored) is that this input field is NOT what is being stored when you press that “Ok” button to save your changes.  This input is strictly for display purposes. See that attribute in the element called “optHid”. That’s another important attribute. This attribute is the ID of a HIDDEN INPUT element that is ACTUALLY the element that we need to set in order for our selection to be saved:

<input type="hidden" 
name="SPNumbers_Hiddenctl00$m$g_50e06708_50bb_4560_ab4c_6c3c5a95a113$ctl00$ctl04$ctl01$ctl00$ctl00$ctl04$ctl00" 
id="SPNumbers_Hiddenctl00$m$g_50e06708_50bb_4560_ab4c_6c3c5a95a113$ctl00$ctl04$ctl01$ctl00$ctl00$ctl04$ctl00" 
value="0" />

My head hurts… Where was I? Oh.. Yes… se we actually need to set the value of the HIDDEN input element not the value of some select element or the value of the VISIBLE input, that would just be silly!!!  So… we can use the “title” attribute from the visible element along with the “optHid” attribute to find the hidden input and set it. So, if we wanted to set the value of THIS Lookup field to “2” that jQuery would look like:

hiddenInput = $("input[title='Numbers']").attr("optHid");
$("input[id='" +hiddenInput +"']").attr("value","2")

In this instance we are looking for the “input” element (not select like we did before) that has the “title” attribute set to “Numbers. We are then looking for the “optHid” attribute from that element and storing it in a variable called “hiddenInput”. We then look for the “input” with the “id” equal to the value of the “optHid” attribute. and we are setting the value of that input to “2”. 

I should point out here that $("input[id='" +hiddenInput +"']").attr("value","2")  SHOULD be the same as $("#" +hiddenInput).val(”2”); unless there is something I don’t know about hidden inputs, BUT for some reason that didn’t work for me…  I’m 99.5% sure I didn’t fat finger it, but oh well.. if it works for you, more power to you. Smile

So… we SET the value for the hidden input that is responsible for storing the value in the list, but we can’t forget about that pesky input element that is used to display the selected lookup entry. If we just set the value of the hidden field IT WILL SAVE THE CORRECT VALUE when you press “OK” even though no option will be displayed in the visible lookup field. When the page reloads the correct value will be displayed in the lookup field.  If you want the text to display for your Lookup field when you set it, we are not quite done… we also need to set the correct value in the displayed input. I used this little piece of script to accomplish that:

choices = $("input[title='Numbers']").attr("choices");
                
        choiceArray = choices.split("|");
        for (index = 1; index < choiceArray.length; index = index + 2)
        {
            if (choiceArray[index] == lookupVal){
                $("input[title='Numbers']").val(choiceArray[index - 1]);    
            }
        }

Now.. I’m not enamored with this script and know there is a more efficient way to write it, but it works and it’s easy to understand (KISS principle).  What I’m doing is getting that string of choices which looks like "(None)|0|eight|8|eightteen|18|eleven|11|fifteeen|15…”.  As you can see it’s a string that is the select text, pipe character, select value, pipe character… etc…  So, I take this string of choices and “split” it at the “|” to turn it into an array. Now we can index each option value and text.  That’s what I’m doing in my for loop, I’m searching through the array for my “value” that I’m trying to set. When I find my value, I have to back up one in my array to get the text that I want to display in the visible text box and display it.  Not too hairy hopefully?

Put it all together

Okay.. we really need to put these two solutions together in one function that can determine which method to use.. right? Understand? Comprendo? That function would look something like:

function SetLookup( fieldTitle, lookupVal)
{
    //Set default value for lookups with less that 20 items
    if ( $("select[title='" +fieldTitle+ "']").html() !== null)
    {
       $("select[title='"+ fieldTitle +"']").val(lookupVal);    
    }
    else
    {
        choices = $("input[title='" +fieldTitle +"']").attr("choices");
        hiddenInput = $("input[title='" +fieldTitle +"']").attr("optHid");
        $("input[id='" +hiddenInput +"']").attr("value",lookupVal)
        
        choiceArray = choices.split("|");
        for (index = 1; index < choiceArray.length; index = index + 2)
        {
            if (choiceArray[index] == lookupVal){
                $("input[title='" +fieldTitle +"']").val(choiceArray[index - 1]);    
            }
        }
    }
}

This function takes two parameters… the Title (Display name) of the Lookup field we want to set, and the value we want to set it to. I’ve used this both in 2007 and 2010 and it worked like a charm.

Okay… but WHERE the heck would I use this?

Actually… there is a GREAT reason to use this in conjunction with my Parent/Child list relationship post.  In that post I have you modifying the NewForm.aspx page in SharePoint Designer which un-Ghosts (customizes) the page and I’m not particularly crazy about doing that.  What you can do instead is put a Content Editor Web Part on the NewForm.aspx page and use this script to set the value of the drop down in conjunction with the Query String Variable. So, the final script for that would look like like the script below (Notice I changed the Lookup field name to match the field in my previous blog).

Oh wait? You don’t know how to edit the NewForm.aspx page or EditForm.aspx page in SharePoint because there is no “Edit Page” option?  All you have to do is append “&ToolPaneView=2” to the URL of the page to get it in Edit Mode (replace the “&” with “?” if there are no other Query String Variables).

<script src="/scripts/jquery.min.js" type="text/javascript" ></script>

<SCRIPT type=text/javascript>     

jQuery(document).ready(function($)
{
    
    //get "IssueID" from Query String
     var vals = new Object();
      var qs = location.search.substring(1, location.search.length);
      var args = qs.split("&");
      for (var i=0; i < args.length; i++) {
       var nameVal = args[i].split("=");
       var temp = unescape(nameVal[1]).split('+');
       nameVal[1] = temp.join(' ');
       vals[nameVal[0]] = nameVal[1];
      }
      var issueID = vals["IssueID"];

      //Set the Lookup field with dislay name of "IssueID" to the item with value from Query String
      setLookup("IssueID",issueID);
});

function setLookup( fieldTitle, lookupVal)
{
    //Set default value for lookups with less that 20 items
    if ( $("select[title='" +fieldTitle+ "']").html() !== null)
    {
       $("select[title='"+ fieldTitle +"']").val(lookupVal);    
    }
    else
    {
        //get the hiddent input using the "optHid" attribute of displayed Input
        hiddenInput = $("input[title='" +fieldTitle +"']").attr("optHid");
        //set value in the hidden input
        $("input[id='" +hiddenInput +"']").attr("value",lookupVal)

        //get the string of choices from the input element so we can set displayed value
        choices = $("input[title='" +fieldTitle +"']").attr("choices");
        
        //turn choices string into an array so we can iterate through it
        choiceArray = choices.split("|");
        
        //improve performance by iterating over every other entry (just look at values)
        for (index = 1; index < choiceArray.length; index = index + 2)
        {
            if (choiceArray[index] == lookupVal){
                //set the displayed input which is the PREVIOUS entry in array
                $("input[title='" +fieldTitle +"']").val(choiceArray[index - 1]);    
            }
        }
    }
}

</script> 

Okay… so, maybe more than you ever wanted to know about Lookup fields in SharePoint.. but on the bright side I’ll  have the freaking script next time I need it… enjoy… Smile

posted @ Wednesday, April 6, 2011 7:02 PM
Print

Comments on this entry:

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by rachuciachu at 4/10/2011 4:47 PM
Gravatar
Great job. Once you did step 1 it is time for step 2 ;) Have you ever try to find the way to generate onChange event from +20 lookup field? I would like to build cascading lookup fields with jquery and getJSON(). I found some examples with seting onpropertychange in optHid field but it seems that it fires many events for every single change. Thanks in advance for any suggestion.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Mark at 4/11/2011 8:55 AM
Gravatar
Actually, I always prefer doing things as easy as possible.. so, instead of figuring out how to trigger a change event when greater than 20, why not replace the Displayed Input with your own HTML Select, and on the change of YOUR select set the value of the hidden input?

That would totally work and should be pretty straightforward.

Good luck!
Mark

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Marcus at 4/13/2011 1:52 PM
Gravatar
I been looking for this for quite some time. I can't Thank You enough. One issue I'm experiencing is that when I submit the record. I receive a, "Input String was not in a correct format" Can you provide any assistance?

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Mark at 4/13/2011 1:58 PM
Gravatar
Make sure you are setting the hidden value to the ID of the lookup, not the text. No matter what field the lookup is created on, it uses the item's ID in the hidden field.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Marcus at 4/13/2011 2:25 PM
Gravatar
BINGO!!!. Thanks for the quick response and resolution.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by CarlosT at 4/14/2011 2:33 PM
Gravatar
One thing that would make this a little more efficient is adding a break statement in the conditional block where you match the lookup. Once you've found your match there's no need to continue looking through the list. It's a minor thing, but if you've got a long list, it could make a difference.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Captain Caverne at 4/15/2011 5:24 AM
Gravatar
Well, thanks a lot, you saved my day !

On a side note, this completely stupid "lets-switch-a-select-for-an-input" thing only happens in IE !!

With FF or Chrome (and I suppose with other browsers as well), the controls are always rendered as select, no matter how many items they hold...

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by mair at 4/25/2011 6:24 AM
Gravatar
Dude!
You saved me :)
Thanks

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Christophe at 6/19/2011 8:56 AM
Gravatar
@Captain_Caverne the benefit of the input element is that it offers a built-in auto-suggest/filter. It makes sense when you have hundreds of options.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Anna at 7/28/2011 8:26 AM
Gravatar
Your're king of LookupFields in SP! Thank you!

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Matthew Lamb at 9/21/2011 12:21 PM
Gravatar
Anyone know a way to switch this behaviour OFF?

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Đặng Ngọc Tàu at 9/30/2011 4:58 AM
Gravatar
Thank you very much. I took me 2 days to find you.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by Peter Allen at 10/3/2011 2:24 PM
Gravatar
Thank you!!!! Brilliant! Just what I needed for a client project.

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by She at 11/27/2011 10:08 PM
Gravatar
I am a follower of your post. Thanks for it and I would like to share something about lookup.
I also experience this in the Lookup that I am creating. There's another way to solve it without using the code above.
To solve it, in SharePoint designer, open the aspx page where the Lookup resides, mine it is in Lists folder/<List Name>/Newform.aspx. I suggest you keep a back-up of your page before you edit it in case you want to roll back your changes. Then in aspx, look for the lookup column row where it shows
<td width="400px" valign="top" class="ms-formbody">
<SharePoint:FormField> -> look for this one and add "InDesign" attribute set to true
</td>

it should look like this:
<td width="400px" valign="top" class="ms-formbody">
<SharePoint:FormField runat="server" InDesign="true">
</td>

# re: Setting SharePoint Lookup Lists w/ jQuery (+/- 20 items)

Left by David at 12/15/2011 8:19 AM
Gravatar
Yeah!!! You made my day!!! Many thanks!!!

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«October»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678