SharePoint – Query List items from External Lists (with jQuery and SPServices)


Hello everyone. Today I tried to find out if it is possible to query External Lists by using jQuery and SPServices so that I can use the gathered data as I wish. And I found out that it is possible. And I´m going to show you how I did it.

The screenshot below shows you that I used the data gathered from an External List to display it in a Content Editor Webpart:

sharepoint_external_list_content_editor_finished

In my example I used an External List called “Location” which I created by importing a SQL Database Table by using the Business Connectivity Services (BCS) and SharePoint Designer with the following columns:

  • Name
  • Address
  • ZIP
  • Country
  • Country_Code

(I will explain how to configure External Lists in a future post if I have the time.)

sharepoint_external_list_example_location

To store my script and the jQuery and SPServices scripts I created a Document Library called “Script”.

sharepoint_external_list_script_library

Here is the GetListItems.js script which will get the list items from the External List:

<script type="text/javascript" src="/sites/test/Script/jquery.min.js"></script>
<script type="text/javascript" src="/sites/test/Script/jquery.SPServices.min.js"></script>

<script language="javascript" type="text/javascript">

$(document).ready(function() {
$().SPServices({
operation: "GetListItems",
async: false,
listName: "Location",
CAMLQuery: "<Query><Where><Eq><FieldRef Name='Country_Code' /><Value Type='Text'>AT</Value></Eq></Where></Query>",
CAMLViewFields: "<ViewFields> <FieldRef Name='Name' /> <FieldRef Name='Address' /> </ViewFields>",
completefunc: function (xData, Status) {
$(xData.responseXML).SPFilterNode("z:row").each(function() {

var liHtml = "<li>" + $(this).attr("ows_Name") + " " + $(this).attr("ows_Address") + "</li>";
$("#tasksUL").append(liHtml);

});
}
});
});

</script>
<ul id="tasksUL"/>

The script will do the following:

It will get all items from the external list, filter them by the column Country_Code and will insert them into a Bulleted List if the Country_Code is “AT”. The returned fields are “Name” and “Address”.

Important note: I only managed to insert the column data into the Bulleted List after I inserted the string “ows_” before the column name (For example: Instead of “Address” I needed to write “ows_Address”). Otherwise it did not work for me. I don´t know why, so if someone knows please let me know.

As always you can download my script from here.

In the next screenshot I added the link of my GetListItems.js script to a Content Editor Webpart.

sharepoint_external_list_content_editor_url

That´s all that needs to be done to make this script work. If you configured everything correct your result will look like the first screenshot in this post. Please keep in mind that it takes some time for External List items to load, so it might take some time before your page has finished loading.

I hope my post was helpful for you.

Sources:

http://www.itidea.nl/index.php/jquery-spservices-and-autocomplete/

http://documentation.agilepoint.com/SupportPortal/DOCS/ProductDocumentation/CurrentRelease/DocumentationLibrary/maps/agileshapeCamlQueryExample.html

https://williamwmy.wordpress.com/tag/getlistitems/

http://social.technet.microsoft.com/forums/sharepoint/en-US/f5371b48-d1aa-4907-ae08-dd7721bd42a2/external-content-type-login-failed-for-user-domainmyuser

Advertisements
Tagged ,

6 thoughts on “SharePoint – Query List items from External Lists (with jQuery and SPServices)

  1. Bas says:

    Great, just curious if you have tried this on O365

  2. Anonymous says:

    This was perfect and exactly what I needed! Worked like a charm….Thanks!

  3. Gord says:

    I have similar code that works terrific on lists but won’t work on external lists at all (BCS). Has this really been tested with an external (BCS) list? If so, did anything else need to be done for it to work?

  4. Victor Pinto says:

    I also tried “GetListItems” in my external list, but it doesn’t query at complete table. I supose it only find in the firt items specified by “FilterLimit”

  5. web1 says:

    dude – you are a life saver!! I was going to start using the client object model..very complex but spservices is great!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: