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:
- 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
- You need to use the “include method” with the executeQueryAsync method
- 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, });
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);
}
Shouldn’t your ajax be a GET? I used the CSOM, not AJAX/SOAP to connect to BCS content.
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?
If I am understanding you?, I would probably hide the field that you want to change and recreate it using jQuery. WOuld that work?
Hmm yeah we have thought about that. Do you have any information on how I might do this?
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?
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.
Thanks for the nice comments. I am glad I could help. It really makes my day when the crap I write is useful !!!!
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.
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?
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.
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.
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
Thanks for a nice post. Helped me a lot in querying external list with JavaScript!