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:
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:
(I will explain how to configure External Lists in a future post if I have the time.)
To store my script and the jQuery and SPServices scripts I created a Document Library called “Script”.
Here is the GetListItems.js script which will get the list items from the External List:
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.
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.