Autocomplete for external list

[lzuhuo]

Hi Alex, I’ll wait for your return from the holidays, hehe. But I want to know if with your autocomplete.js script I can query data from an external list in DFFS, or I need to create a custom js to connect to my external sql server to query the data. Thanks

[Alexander Bautz]

Hi,
I have not used external lists myself so I need you to test this snippet for me. Ensure you use the latest version of spjs-utility.js (v1.205), and add this override code below where you refer the spjs-utility.js file:

<script type="text/javascript">
spjs.utility.queryItems = function(argObj){
	var content, result, requestHeader, fieldValObj, value;
	if(argObj.listBaseUrl===undefined){
		argObj.listBaseUrl=L_Menu_BaseUrl;
	}
	if(argObj.listName===undefined || (argObj.query===undefined && argObj.viewName===undefined)){
		alert("[spjs.utility.queryItems]\n\nMissing parameters!\n\nYou must provide a minimum of \"listName\", \"query\" or \"viewName\" and \"viewFields\".");
		return;
	}
	if(spjs.$.inArray('ID',argObj.viewFields)===-1){
		argObj.viewFields.push('ID');
	}
	if(spjs.$.inArray('BdcIdentity',argObj.viewFields)===-1){
		argObj.viewFields.push('BdcIdentity');
	}
	content = spjs.utility.wrapQuery({'listName':argObj.listName, 'query':argObj.query, 'folder':argObj.folder, 'viewName':argObj.viewName, 'viewFields':argObj.viewFields, 'rowLimit':argObj.rowLimit, 'pagingInfo':argObj.pagingInfo,'scope':argObj.scope});
	result = {'count':-1, 'nextPagingInfo':'', items:[]};	
	if(argObj.setRequestHeader===false){
		requestHeader  = '';
	}else{
		requestHeader  = 'http://schemas.microsoft.com/sharepoint/soap/GetListItems';
	}
	spjs.utility.wrapSoap(argObj.listBaseUrl + '/_vti_bin/lists.asmx',requestHeader, content, function(data){
		result.count = parseInt(spjs.$(data).filterNode("rs:data").attr('ItemCount'),10);
		result.nextPagingInfo = spjs.$(data).filterNode("rs:data").attr('ListItemCollectionPositionNext');
		fieldValObj = {};
		spjs.$(data).filterNode('z:row').each(function(idx, itemData){
			fieldValObj[idx] = {};
			spjs.$.each(argObj.viewFields,function(i,field){
				if(field === "ID" && spjs.$(itemData).attr("ows_ID") === undefined){
					if(spjs.$(itemData).attr("ows_BdcIdentity") !== undefined){
						value = spjs.$(itemData).attr("ows_BdcIdentity");
					}else{
						value = "";
					}
				}else{
					value = spjs.$(itemData).attr('ows_' + field);
				}				
				if(value === undefined){
					value = null;
				}
				fieldValObj[idx][field] = value;
			});
			result.items.push(fieldValObj[idx]);
		});
	});
	return result;
}
</script>

When this is done, try the autcomplete solution. Please let me know how this works out – and use the developer toolbar (F12 > Console) to look for error messages.

Alexander

[lzuhuo]

Could you explain me with more details? I have to insert this code in CEWP below of DFFS_frontend_CEWP.js.aspx? Seems like ext list columns doesn’t have ID

[Alexander Bautz]

Hi,
Yes, it is the missing ID column that causes it to malfunction. This is what I have tried to take care of in the modified function above.

Try inserting this right below where you load the spjs-utility.js script in the “DFFS_frontend_CEWP.js.aspx” CEWP:

<script type="text/javascript" src="/Your_Path_Here/spjs-utility.js"></script>
// Add the code snippet here

If this does not work, you can most likely make it work if you add a column “ID” (with an unique id number) to the external list.

Alexander

[lzuhuo]

Hi Alex
I insert an ID column and “/” in “listBaseUrl” and still not working. It tries the search but does not complete, do not return data. This is the console error

NewForm.aspx:794 Uncaught SyntaxError: Unexpected token }
jquery-1.11.1.min.js:4 Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience. For more help, check http://xhr.spec.whatwg.org/.
http://srvintranet/Javascript/jquery-1.11.1.min.js Failed to load resource
spjs-utility.js:340 Uncaught TypeError: Cannot read property 'match' of undefined
http://srvintranet/Javascript/jquery-1.11.1.min.js Failed to load resource
spjs-utility.js:340 Uncaught TypeError: Cannot read property 'match' of undefined

[Alexander Bautz]

The first line indicates a syntax error. If you right click the page and view source – what do you find in line 794?

Alexander

[lzuhuo]

Just a “}”, I correct already

[lzuhuo]

This is the console error:

 OPTIONS http://_vti_bin/lists.asmx jquery-1.11.1.min.js:4 sendjquery-1.11.1.min.js:4 m.extend.ajaxspjs-utility.js:325 spjs.utility.wrapSoapNewForm.aspx:767 spjs.utility.queryItemsspjs-utility.js:978 spjs_QueryItemsVM1154:1 (anonymous function)
spjs-utility.js:340 Uncaught TypeError: Cannot read property 'match' of undefinedspjs-utility.js:340 spjs.$.ajax.errorjquery-1.11.1.min.js:2 jjquery-1.11.1.min.js:2 k.fireWithjquery-1.11.1.min.js:4 xjquery-1.11.1.min.js:4 m.extend.ajaxspjs-utility.js:325 spjs.utility.wrapSoapNewForm.aspx:767 spjs.utility.queryItemsspjs-utility.js:978 spjs_QueryItemsVM1154:1 (anonymous function)

[Alexander Bautz]

I’m sorry, but as I have not experience dealing with external lists I cannot be of much help here. I hope you find the solution, and when you do, please post back the solution here.

Alexander

[lzuhuo]

Hi Alex.
I saw your update on https://spjsblog.com/dffs/dffs-change-log/#SPJS-utilityjs_v1312
Could you give more instruction for how to use it on autocomplete function? Thanks

Attachments:

1. 

   teste.png
   

[Alexander Bautz]

I’m sorry, but currently it’s not built in support for specifying the “primary key” in the query from the spjs-autocomplete solution. I have noted it and will add this in a future release.

Alexander

[lzuhuo]

Thank you very much.

[Author]

Posts