Using SPServices with jQueryUI’s Autocomplete Function on InfoPath Forms in SharePoint

Yes, that title says using jQueryUI on InfoPath forms in SharePoint. InfoPath forms are fantastic, except when they just aren’t quite able to do what you want. While InfoPath can help you take your SharePoint list forms to a whole new level, there may be times when you want to add some behavior to those forms which InfoPath simply doesn’t offer.

Guess what? jQuery and jQueryUI can come to the rescue in some of those cases, just as it can with the standard list forms.

I recently worked with a client to do a relatively simple thing, but it was a huge hit. They had a SharePoint list had a Single line of text column, to which the user could add whatever they wanted. However, there was a desire to make suggestions as the user typed, based on the contents of a different list, which had thousands of items.

A dropdown doesn’t make sense in that situation, because there are simply too many items. They also wanted to show matches to what the user had typed, regardless where those characters occurred in the list item.

This is a perfect situation in which to use the idea of autocomplete. We all are familiar with this idea, if not the actual term. You probably see it in action every single day as you use a search engine. As you type your search term(s), the search engine shows suggestions, probably based on some fairly sophisticated algorithms.

Here’s an example from Bing:

image

and from Google:

image

(I can’t help but point out that Google was more on the mark in guessing that I was searching for SPServices, but hey, who’s counting?)

When InfoPath forms are rendered in the browser, they are built of ordinary markup just like any other page, albeit fairly complicated markup, driven by a lot of script.  That doesn’t mean that you can’t add some script of your own as well to add additional capabilities. There are a few peculiarities to this situation, though, which you need to handle.

Here’s a slightly dumbed down version of the script we ended up with.

window.onload = function() {
  window.setTimeout(readyCall, 1000);
}

function readyCall(){

  var externalParties = [];

  $().SPServices({
    operation: "GetListItems",
    listName: "External Parties",
    CAMLViewFields: "",
    async: false,
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        externalParties.push($(this).attr("ows_Title"));
      });
    }
  });

  //<input tabIndex="0" title="" class="q_zwfUqJo2fRthHnM4_0 as_zwfUqJo2fRthHnM4_0 b9_zwfUqJo2fRthHnM4_0" id="ctl00_m_g_a226da68_1383_40e3_8410_1ada27d49dcf_FormControl0_V1_I1_T2" aria-invalid="true" style="position: relative;" onfocus="return (TextBox.OnFocus(this, event));" onblur="return (TextBox.OnBlur(this, event));" onpropertychange="return (TextBox.OnPropertyChange(this, event));" type="text" OriginalId="V1_I1_T2" FormId="ctl00_m_g_a226da68_1383_40e3_8410_1ada27d49dcf_FormControl0" ViewDataNode="3" direction="ltr" wrapped="true" ScriptClass="TextBox" VCARD_NAME="91161f891e59461042587839b2504693728ce05a" ?=""/>
  $("input[id$='FormControl0_V1_I1_T2'], input[id$='FormControl0_V1_I1_T3']").autocomplete({
    source: externalParties,
    minLength: 3
  });
}

When InfoPath forms load in the browser, they don’t load with the rest of the page, but instead they are loaded slightly afterward in what amounts to an asynchronous load. Because of that, using $(document).ready(), our trusted jQuery friend, doesn’t work. Instead, as you can see in lines 1-3, we simply wait for 1000 milliseconds (1 second) before we run our script. We found that this was an adequate amount of wait time for our particular form; you might need to adjust this.

In lines 9-19, we use my SPServices library to call the Lists Web Service, using the GetListItems operation. This operation simply reads items from the list based upon the criteria you specify. Once we have the data, we push each of the Title column values into an array called externalParties.

Finally, we call the jQueryUI function autocomplete, using two selectors. In line 21 above, which is commented out, you can see an example of the markup for one of the input elements rendered in the InfoPath form. One of the hardest parts of all of this was to figure out what selector to use. We settled on looking for an input element where the id contained ‘FormControl0_V1_I1_T2’.  (We actually added the autocomplete behavior to two columns in the form, thus the second selector for ‘FormControl0_V1_I1_T3’.)

We added this script into the newifs.aspx and editifs.aspx pages using a trusty Content Editor Web Part (CEWP). Since this was SharePoint 2010, and because it makes for far better code management, we stored the script in a separate file and referenced it using the Content Link.

Bingo-bango, we had a nice, little additional piece of functionality which made the users very happy. This is an example where thinking about all of the tools at your disposal and how you might glue them together into the right solution to get the job done can be the right approach rather than a lot of custom coding.

<UPDATE dateTime=”2011-08-25T23:51″>

My partner in crime for this exercise was Marcel Meth (@marcelmeth), and he’s done a post on it, too, which you can read here. I was able to steal his image of the results, which I’ve added above. Note that the image is not showing the real data but our test data, which was simply a list of the 3000 or so major cities and towns in the US.

</UPDATE>

<UPDATE dateTime=”2011-08-26T09:25″>

I got a question in the comments below about how we added the script to the InfoPath page, and I wanted to add those details here.

  • First, we opened each form in the browser, which launched it in a dialog box.
  • We got the actual URL of the page in the dialog by right-clicking and looking at its properties. The NewForm was newifs.aspx and the EditForm was editifs.aspx (as I mention above).
  • We opened the form page directly in a new browser window and used the toolpaneview=2 Query String parameter trick to put the page into edit mode. This allows you to edit a list form page and add Web Parts to it.
  • We added the CEWP, and put the reference to our script file in the Content Link.
  • Apply and done.

</UPDATE>

Similar Posts

128 Comments

  1. How can I find the selector/Form Control for the column that I want to set auto complete on…

    1. Andy:

      You’ll need to get comfortable with jQuery selectors. The Developer Tools can help you figure out what the right selector should be. Generally, for Single line of text columns, it should be $(“input[title=’column name’]”).

      M.

  2. Hi Marc, first, thanks for this and all the contributions you make to the SP community. I can’t count the number of times one of your blog posts has led me to the solution I need!

    I was able to get this working. For some reason, the XML parser on line 15 gave me a bit of trouble. I replaced it with some code from your SPServices documentation example, and it worked fine.

    Orig: $(xData.responseXML).find(“[nodeName=’z:row’]”).each(function() {
    New:: $(xData.responseXML).SPFilterNode(“z:row”).each(function() {

    In your original, it appears that you’re pulling from an administrator-maintained reference list, so the column you are pulling can be guaranteed to have a value for all items. My situation is slightly different in that I’m self-referencing the same list – getting values that users have previously typed in. This seems to have two challenges.

    1. The referenced field is not required, and so for some list items is undefined. In this situation, sometimes the autopopulate will work, sometimes not (haven’t isolated why yet). If I populate all the records with values on a dev site, it works consistently. Any way you know of to filter nulls?

    2. Since it’s self referencing, as you use the feature, you end up with a lot of duplicates in the autopopulate. Know of any ways to easily take a javascript array and perform a “select distinct” type operation on it?

    1. Joe:

      You’re welcome!

      I wrote this post before I had to add the SPFilterNode function. The jQuery guys made a change in 1.7 that caused the old z:row filtering method to break.

      As for your list, you can add a CAMLQuery to your GetListItems call
      to filter for values which are not null.

      There are a number of tricks to get the distinct values from what is returned. CAML won’t do it for you, unfortunately. The approach I use is to push all of the values into an array and then apply a filter to the array to eliminate duplicates. I don’t have an example handy, but you should be able to find something fairly easy by Bingling.

      M.

      1. Thanks for the thoughts! I posted the solution I found below (was looking at a stale browser window and didn’t realize you’d replied). This is a great solution, and I can already think of a lot of sites that would benefit from it.

        1. PS – I actually like your approach to filtering NULL values better than the one I posted below, as it will cause the server to return less data. That would be more efficient.

  3. Earlier today I had commented with a couple of questions about duplicate values and records that do not have values. I think I’ve found working solutions for both and as such, thought I’d share the code back. The IF block on line 12 evaluates whether a particular record has a null value in the column of interest, and if so, skips adding it to the array. The function call on line 19 combined with the prototype function on 26-31 removes any duplicate values from the array.

    function readycall() {
    var venueNames = [];
    var distinctVenueNames = [];
    $().SPServices({
    operation: “GetListItems”,
    listName: “Meetings”,
    viewName: “{E14B49A5-DD39-401C-A66E-142B049ED817}”,
    CAMLViewFields: “”,
    async: false,
    completefunc: function (xData, Status) {
    $(xData.responseXML).SPFilterNode(“z:row”).each(function() {
    if (typeof $(this).attr(“ows_Venue_x0020_Selected”) != “undefined”) {
    venueNames.push($(this).attr(“ows_Venue_x0020_Selected”));
    }
    });
    }
    });

    distinctVenueNames = venueNames.unique();

    $(‘#ctl00_m_g_c5114d91_0661_478d_b4d2_a0b69e24ecba_FormControl0_V1_I1_T64’).autocomplete({
    source: distinctVenueNames,
    minLength: 3
    });
    }
    Array.prototype.unique = function() {
    var o = {}, i, l = this.length, r = [];
    for(i=0; i<l;i+=1) o[this[i]] = this[i];
    for(i in o) r.push(o[i]);
    return r;
    };

  4. Dear Marc,

    I am very excited to try this, but am stuck at the very beginning…. perhaps due to my newness at using infopath forms at all. I am logged into sharepoint and looking at my list…. I click on a list item and the edit form I have made appears in a dialog bog. I right click the top of the dialog bog and select properties, but the link I get is

    http://www.foo.edu/mysite/Lists/mylist/myview.aspx?InitialTabId=Ribbon%2EList&VisibilityContext=WSSTabPersistence

    Note that the aspx refers to the view of the list I am currently in. So clearly I am confused. How to I open the edit form so that I can actually get its address?

    Thanks so much for helping this newbie,
    K

  5. HI Marc , i tried this in a List form template and it worked great , only one thing is when there is a “sending data to server” message from any control in page , the auto-complete didn’t worked , and i should refresh the page , how can we avoid this ?

  6. HI Marc , thanks a lot for this post , your code works great , can we change filter to “BeginsWith” ? it works with contains now , any sample will be great , thanks again

  7. I am new to InfoPath. As such I am explaining every steps that I have done. But it is still not working. need help.

    1. At first I have created a list named “Province” and added values only to the title field.
    2. Then I have created a web part page and added two web parts; CEWP and InfoPath web parts.
    3. added the following script to the CEWP through edit HTML command.

    window.onload = function()
    {
    	window.setTimeout(readyCall, 1000);
    }
    function readyCall()
    {
    	var externalParties = [];
    	$().SPServices({
    					operation: "GetListItems",
    					ListName:   "Province",
    					CAMLViewFields: "",
    					async: false,
    					completefunc: function (xData, Status)
    						{
    							$(xData.responseXML).SPFilterNode("z:row").each(function()
    							  {
    								externalParties.push($(this).attr("ows_Title"));
    								});
    						}
    					});			
    	$("input[id$='FormControl0_V1_I1_T1']").autocomplete
    			({
    				source:externalParties
    			});
    }
    

    4. still getting a message while typing in the title field of the form. ” no search results”.

    Please need help

    1. solil:

      Odds are that the selector for ‘FormControl0_V1_I1_T1’ isn’t correct in your situation. You need to use a DOM inspector like the IE Devleoper Tools to find the correct id in your specific form.

      M.

  8. Hi Marc,

    I have a form, in which I have to create a textbox like predictive textbox so for this the search result should be come from the secondary data source(SQL server table).
    So please help, how can i achieve this.

    1. Sonu:

      You should be able to use jQuery to read from the SQL table directly if you don’t have it set up as an external list in SharePoint.

      I’d suggest using jQueryUI’s autocomplete, as it is more full featured than the one in SPServices.

      M.

      1. Hi Marc,

        Thanks for reply, but my question is that can i create such textbox in InfoPath form(not in sharepoint) which should be predictable when we start to type the characters ?
        As same as you have created this in sharepoint in your example.

        I have some fields in my InfoPath form which have some n numbers of processes name,
        If I use dropdown here then it is not easy for use this because we have to find the process name by scrolling thousand of process names every time so I want to use the predictive textbox here in place of dorp down.

        Thanks in advance

        1. sonu:

          If you’re using InfoPath, then you should set up the datasource for SQL in InfoPath and work there. That’s not my forte, but I believe it can be done. It sounds like you may already have the datasource working, but you need add what amount to a code behind.

          M.

  9. Hi Mark..
    Thanks for your post…!
    But..i have some problem, can you help me to solve it?
    1. I’m write code in CEWP, and it only work after i go to Page -> Edit Page ->Edit HTML Source (CEWP), after that (do nothing), Exit Edit Page –> the autocomplete work…every time, and every browser…can you tell me how to solve it?
    2. I cant use parameter toolpaneview=2 trick in form browser, but it’s ok in another sharepoint site,…?
    Hope you can help me..thank you, thank you…!

        1. Seem to be….the same ahhhhhhh….!
          I was tried on SP Designer, CEWP, and Content Link…But it wasn’t help me.
          Ah…i found a little different in html code of infopath control before and after i click ok in CEWP…

          Before (not working)

          and After that (autocomplete) :

          I was tried use javascript to add attr by this way:
          $(“input[id$=’FormControl0_V1_I1_T8′]”).attr(‘autocomplete’, ‘off’);
          and $(“#ctl00_m_g_0c369260_4d41_423d_8609_c0ea2e473cf8_FormControl0_V1_I1_T8”).attr(‘autocomplete’, ‘off’);
          But all of them are not working…

          Hope it can suggest something….!

          Thanks for read, although my english is not good…lol

          1. Phong:

            It’s really hard to make a suggestion because there will be unique things about your page and form. This method is pretty hacky to begin with, but in theory you should be able to make it work.

            M.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.