SharePoint 2010, Client OM, jQuery Autocomplete and BCS/External lists

In this previous post, I used jQuery/SOAP/SPServices to access a SharePoint BCS list (an external list). I wanted to do the same thing using the Client Object Model (Some call it Clien OM? Or maybe CSOM?). Below is the code to access contact data in a BCS connection to Microsoft CRM 2011, and use it for autocomplete values. Things to note:

  1. You need to have a method tag in your CAML statement. This tells the list which “Operation” (as it is listed in SharePoint Designer) to use
  2. You need to use the “include method” with the executeQueryAsync method
  3. You need to include the ViewFields tags in the CAML statement, and I think they should match the include statement above
$('#ContactsSearchTextbox').autocomplete({
    source: function( request, response ) {
	    var CAMLQuery= "<View><Method Name='ContactReadList' /><Query><Where><Contains><FieldRef Name='FullName' /><Value Type='Text'>"+request.term+"</Value></Contains></Where></Query><ViewFields><FieldRef Name='FullName' /><FieldRef Name='ContactId' /><FieldRef Name='ParentCustomerIdName' /></ViewFields></View>";
		var context = new SP.ClientContext.get_current();
		var web = context.get_web();
		var list = web.get_lists().getByTitle('Contacts');
		var query = new SP.CamlQuery;
		query.set_viewXml(CAMLQuery);
		allItems = list.getItems(query);
		context.load(allItems, 'Include(FullName,ContactId,ParentCustomerIdName)');
		context.executeQueryAsync(function (){
			var arrayOfResults = new Array();
			var listItemEnumerator = allItems.getEnumerator(); 
			while(listItemEnumerator.moveNext()) {
				var oListItem = listItemEnumerator.get_current();
				arrayOfResults.push(oListItem);
			}
			response($.map(arrayOfResults ,function( item ) {
				return {
				label: item.get_item('FullName') + ' (' + item.get_item('ParentCustomerIdName') + ')',
				value: item.get_item('ContactId'),
				id: item.get_item('FullName')
				}
			}));
		},function(sender, args){
		   alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
		});        
	},
	minLength: 5,
});

, ,

14 Responses to SharePoint 2010, Client OM, jQuery Autocomplete and BCS/External lists

  1. John December 19, 2012 at 10:41 am #

    Is this only for CRM? I have been able to create an autocomplete for a lookup coming from an external content type although when I recreate it on a BCS field I can’t get the autocomplete to work the code I am using is…

    // force cross-site scripting
    jQuery.support.cors = true;
    $(document).ready(function() {
    $(“input[title=’INPUTTITLENAME’]”).autocomplete({
    source: function(req,add)
    {
    SoapService(req.term,add,’Text’,’SITEURL’,’SOURCELIST GUID’,’SOURCEFIELDNAME’,false,’/_layouts/SP_Form_Suggestion/AjaxSoapProxy.aspx’);
    }
    });});
    function SoapService(value,handler,fieldType,webURL,listGUID,sourceFieldName,ajaxProxy,ajaxProxyURL) {

    var serviceURL = “/_vti_bin/lists.asmx”;
    var xmlCode = [];
    xmlCode.push(”);
    xmlCode.push(”);
    if(ajaxProxy)
    xmlCode.push(“”+webURL+serviceURL+””);
    xmlCode.push(”);
    xmlCode.push(”);
    xmlCode.push(”+listGUID+”);
    xmlCode.push(” “+value+””);
    xmlCode.push(“”);
    xmlCode.push(’10’);
    xmlCode.push(”);
    xmlCode.push(”);
    xmlCode.push(”);
    xmlCode = xmlCode.join(”);

    var targetURL;
    if(ajaxProxy)
    targetURL = ajaxProxyURL;
    else
    targetURL = webURL+serviceURL;

    $.ajax({
    async: true,
    type: “POST”,
    url: targetURL,
    contentType: “text/xml; charset=\”utf-8\””,
    data: xmlCode,
    dataType: “xml”,
    complete: function(data,textStatus){
    GetListItemsComplete(data,sourceFieldName,handler);
    }

    });

    }
    function GetListItemsComplete(xData,dataFieldName,handler) {

    var results = [];
    // firefox
    if(xData.responseXML)
    {
    $(xData.responseXML).find(“z\\:row”).each(function () {
    results.push($(this).attr(“ows_” + dataFieldName));
    });
    }
    // IE
    else
    {
    $(xData.responseText).find(“z\\:row”).each(function () {
    results.push($(this).attr(“ows_” + dataFieldName));
    });
    }

    handler(results);
    }

  2. jbmurphy December 19, 2012 at 10:51 am #

    Shouldn’t your ajax be a GET? I used the CSOM, not AJAX/SOAP to connect to BCS content.

  3. John December 19, 2012 at 11:07 am #

    It’s bringing in the information this way to a normal feild on the edit properties form. Is there a away to bring in the information with the external item picker field for BCS?

  4. jbmurphy December 19, 2012 at 4:21 pm #

    If I am understanding you?, I would probably hide the field that you want to change and recreate it using jQuery. WOuld that work?

  5. John December 26, 2012 at 5:54 pm #

    Hmm yeah we have thought about that. Do you have any information on how I might do this?

  6. jbmurphy December 27, 2012 at 2:52 pm #

    I just wrote a new post that shows how I hid a field, created a replica, attached an autocomplete, and put the selected value back in the original hidden field. Hope that might help?

  7. Brage Rønning Tukkensæter January 4, 2013 at 9:37 am #

    Thanks for a great post, this really helped me a lot. I didn’t really find many examples of querying external lists using JavaScript / jQuery / SPServices – your series of posts helped me a lot 🙂

    Now I am able to filter my document library really easily based on External Lookup columns.

  8. jbmurphy January 4, 2013 at 4:42 pm #

    Thanks for the nice comments. I am glad I could help. It really makes my day when the crap I write is useful !!!!

  9. Alex January 18, 2013 at 4:26 pm #

    I have been struggling getting an ASP:GridView to be autocompleted using a SPDataSource from BCS. I have my grid populated with 2 columns of data “clientname” and “clientid”. Do you have any tricks to pull from your magic bag of knowledge to help me? My grid fill with data just fine, but when I type text into my TextBox the grid does not filter. – I am bummed out.

  10. jbmurphy January 18, 2013 at 4:30 pm #

    Is clientid a guid in sql? BCS does not support guid data types (I don’t know where I found that). I create a new view in SQL and use the CAST command to change the type to a varchar.

    Does that help?

  11. Alex January 22, 2013 at 9:55 am #

    jbmurphy, thanks for the quick reply. The columns in SQL are both varchar. I am using SPDataSource to pull the BCS data using the SelectCommand as my query and the WebURL Parameter as {sitecollectionroot}. My grid is populating with the correct data, but I have not been able to filter the data using a textbox (ie. filter-as-you-type) with jQuery. I hope this extra information is helpful.

  12. jbmurphy February 15, 2013 at 2:28 pm #

    Sorry I wasn’t able to help. I have not had a chance to go back and look into this. If I come across something, I will post it here.

  13. Pran June 6, 2013 at 12:43 am #

    Hi ,

    I am using almost similiar script fot autocomplete with External list. code snippet below.Here config.fieldname is my column which i need to have.
    var context = SP.ClientContext.get_current();
    var web = context.get_web();
    var list = web.get_lists().getByTitle(config.listTitle);
    var query = ” + keyword + ”;
    // var query = ” + keyword + ”;
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(query);
    this.listCollection = list.getItems(camlQuery);
    context.load(this.listCollection, ‘Include(‘ + config.fieldName + ‘,ID)’);
    context.executeQueryAsync(Function.createDelegate(this, this.onSuccess), Function.createDelegate(this, this.onFailure));

    Seems like ext list columns doesnt have ID and becoz of that in include am getting error.Pls help me on this

  14. Jonas February 15, 2016 at 5:31 am #

    Thanks for a nice post. Helped me a lot in querying external list with JavaScript!