In my last post I talked about a simple example of using Sharepoint's owssvr.dll API interface with jQuery. Today I want to provide a more complex example by showing how I've used the technique to create object collections that can be manipulated to generate on-the-fly statistics.

To start with, I had a Sharepoint survey that captured a set of data from the user. That data was later used to calculate statistics. Previously, the survey was exported to Excel and that was used to come up with the numbers. My goal was to create a client-side application that would pull the information from Sharepoint and generate the statistics dynamically based on up-to-the-minute data.

To do this, I used the technique I described in my previous post to build a $.get query.

var list = 'A1234567-B123-C123-D123-A12345678901';
var view = 'E9876543-F987-9876-B987-A98765432109';

Then, I set up an object called fields that would map each survey field to an attribute. This is used later to build the object collection. The best way to find these field titles is to inspect the API XML data directly.

fields = {
    author: 'ows_Author',
    date: 'ows_Date_x0020_and_x0020_Time',
    source: 'ows_Source',
    issue: 'ows_Issue_x0020_Type',
    details: 'ows_Details',
    site: 'ows_Site'
};

I also set up an array to hold my data objects.

data = [];

Now I was ready to build the $.get query.

url = 'http://example.com/sites/mysite' +
    '/_vti_bin/owssvr.dll?XMLDATA=1&List={' + list +
    '}&View={' + view + '}';

$.get(url, {}, loadData);

In the callback function, I walked the XML DOM like I described in part 1 and then for each object I call another function to save the data. I set it up in separate functions like this because of some dynamic date range selecting I implemented later.

function loadData(xml) {
    $('xml > *:last > *', xmlData).each(function (i) {
        saveData(this);
    });
}

In the function to save the data to an object in our collection, I iterated over the fields object I established above and saved the contents of each field to an attribute on a new object inside the data array I created above for the collection.

function saveData(currentObj) {
    var i = data.length;

    data[i] = {};

    for (var f in fields) {
        data[i][f] = $(currentObj).attr(fields[f]);
        if (data[i][f] == undefined) data[i][f] = '';
    }

If any of the fields in the survey are left blank, they are mapped as undefined to the object. I cleaned that up by changing that to an empty string. After that, I take care of any overrides or post-processing I need to do to the fields before they are ready, such as stripping some junk data out of the front of the author field and setting an id field.

data[i].author = data[i].author.slice(data[i].author.indexOf('#') + 1);
data[i].date = parseDate(data[i].date);
data[i].id = i;

For the date, I use a custom function to convert the string to a JavaScript date object. In my opinion, the way I implemented the date parser is messy and I think I could have cleaned it up further if I'd had more time.

function parseDate(unparsed) {
    var dateParse = new Object();

    dateParse.unparsed = unparsed; 
    dateParse.year = dateParse.unparsed.slice(0,4);
    dateParse.month = dateParse.unparsed.slice(5,7) - 1;
    dateParse.day = dateParse.unparsed.slice(8,10);
    dateParse.parsed = new Date(dateParse.year,
                                dateParse.month,
                                dateParse.day);

    return dateParse.parsed;
}

Now, all of the data is in an object collection that you can manipulate in order to generate your stats. I used a jQuery plugin called jLINQ, and I'm sure there are others out there that could be used for different purposes.