Tuesday, May 17, 2011

Sharepoint Query with SPAPI

1. To use SPAPI query download it from HERE!!!.
2. On SharePoint server under "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS" create folder "SPAPI".
3. Place extracted js files from download to this folder.
4. Put your query within these code lines: 


<script type="text/javascript" src="/_layouts/SPAPI/SPAPI_Core.js"></script>
<script type="text/javascript" src="/_layouts/SPAPI/SPAPI_Lists.js"></script>
<script type="text/javascript">
 

<your spapi query>

</script>

//SPAPI query example:

 
var lists = new SPAPI_Lists('http://url')// SharePoint site URL
var items = lists.getListItems(
'ListName',// SharePoint list name
'',        // SharePoint list view name (for default leave blank)
'<Query><Where><Eq><FieldRef Name="FieldName"/><Value Type="Text">'+Value+'</Value></Eq></Where></Query>', // query
'<ViewFields><FieldRef Name="FieldName"/></ViewFields>', // which field/s to view
1,  // RowLimit
''  // QueryOptions
);

if (items.status == 200)
{
  var rows = items.responseXML.getElementsByTagName('z:row');
 
  for(x=0; x < rows.length; x++) {
  var listValue = rows[x].getAttribute('ows_FieldName'); // query field + ows prefix
    }
}
else
{
  alert('There was an error: ' + items.statusText); // else alert error message
}


Things you should know:
If you use field names with blank spaces you should use field names formatted properly otherwise query will not work. From my experience I use SharePoint Designer to get field names with unsupported characters or blank spaces like this:

1. Connect to your SharePoint site via SharePoint Designer
2. Navigate to "Data Source Library" in upper right section
3. Expand SharePoint lists and find your list from which you want to query fields
4. Right click on the list name and select "Show Data"
5. There you will see all field names with data from current list.
6. Look for your field name (it will be with some different characters like _x0020_).
7. Use this field name in your query. 

7 comments:

  1. Hi - I'd like to attempt some of the stuff you'e suggested, but it looks like the site to download SPAPI is password protected. Would you be able to share the files here or send them to me directly?

    ReplyDelete
  2. Hi, Tristan, i will send you these files to meil, please give me your email

    ReplyDelete
  3. Hi! I can't find this .js files. Could you send me them, please. My e-mail siisii@rambler.ru

    ReplyDelete
  4. What is the need of writing "Viewfields" when you are already using "Query".

    ReplyDelete
    Replies
    1. In query you specify query parameters like (fieldA = 1) and then with "Viewfields" you specify which fields you want to see based on this criteria

      Delete
  5. SPAPI_Core.js can be found here http://sharepoint.untangletheweb.com.au/zsLibrary/spconnect/SPAPI_Core.js

    and the SPAPI_Lists.js can be found here
    http://sharepoint.untangletheweb.com.au/zsLibrary/spconnect/SPAPI_Lists.js

    ReplyDelete