Finding the Distribution of Column Types in a SharePoint List with SPServices
I just went through a little exercise that was simple, but fun, and I thought I’d share. As part of building my new SPXmlToJson function for SPServices, I wanted to spin through some of my lists to see what the various types of columns were and count the occurrences. Sure, I could have probably just looked in the SDK somewhere for the exhaustive list, but I though it would be a useful exercise to code something. The idea was to call GetList on a particular list, go through all of the columns in the results, and list out the number of times each column type shows up.
Since I am building a function to emit JSON, knowing what type of objects I need to create is important. Each column has a type, of course, which you select when you create the column. In the UI, the column types are things like “Single line of text”, “Choice”, or “Lookup”, as you’re probably used to seeing:
However, those aren’t the values that SharePoint stores in the list settings. If you work with the SharePoint Object Model with managed code, you’re used to seeing SPFieldType (yup, that link is where I could have gone to see the full list without resorting to my code).
In the Web Services (at least GetList), we see an attribute simply named “Type” for each “Field”. Yes, fields are columns. Isn’t it grand how inconsistent some of the naming in SharePoint is?
Here’s the script I ended up with. Since I’m focused on JSON, I wanted to play around with the various options for storing the data. I ended up with an array of typeName/typeCount values, which I could then sort in various ways. My first cut was to sort the types alphabetically and show them as simple list bullets.
$(divId).html(waitMessage).SPServices({
operation: "GetList",
listName: "Sales Opportunities",
completefunc: function (xData, Status) {
var types = [];
$(xData.responseXML).find("Fields > Field").each(function() {
// If we already have this type in the array, increment the count...
var match = false;
for(i = 0; i < types.length; i++) {
if(types[i].typeName === $(this).attr("Type")) {
types[i].typeCount++;
// No need to loop further
match = true;
}
}
// ...otherwise, add it to the array with a count of 1
if(!match) {
var thisType = {typeName: $(this).attr("Type"), typeCount: 1};
types.push(thisType);
}
});
types.sort(function(a, b){
var typeA = a.typeName.toLowerCase(), typeB = b.typeName.toLowerCase();
// We want to sort the type strings, ascending
if (typeA < typeB) return -1;
if (typeA > typeB) return 1;
// If they are equal, return 0 (no sorting)
return 0;
});
var out = "<ul>";
for(i=0; i < types.length; i++) {
out += "<li>" + types[i].typeName + " = " + types[i].typeCount + "</li>";
}
out += "</ul>";
$(divId).html("").append("<b>This is the output from the GetList operation:</b>" + out);
}
});
What I ended up with is something like this:
To see the column type sorted by number of occurrences, I simply changed the sort function to this:
types.sort(function(a, b){
// To sort descending, subtract a from b; ascending would be the reverse
return b.typeCount - a.typeCount;
});
This gave me output like this:
Since I’ve found the SDK page for SPFieldType, this is throwaway code, but it’s not throwaway learning. It’s a little snippet of script that taught me a few things about formatting data for working storage on the client in script and also showed me what the internal types were for the columns which I use most often in my testing for SPServices. Note that while I’m using SPServices, and therefore jQuery, most of the heavy lifting in my script is plain old JavaScript.
> Isn’t it grand how inconsistent some of the naming in SharePoint is?
This is why I “love” SharePoint… As a non-native english speaker this is even worse, as you have to work with non-english SharePoint setups and it’s surprising how Microsoft translates some expressions.
I m using SharePoint 2010. I have two different lists using the same content type. Customers list has CustomerID and Orders list has OrderID. I have two SPServices defined. First SPServices would get CustomerIDs using operation: GetListItems and store the values in an array. The second SPServices would get OrderIDs using operation: GetListItems and store the values in another array.
My question is how can i combine the values of those two separate arrays using jquery and display the result ? Please let me know if I can do that.
Another question is what if these two lists reside in different web sites. Can we still use SPService to accomplish our goal of displaying a combined result from those two lists residing in different sites?
Below is my code.
$(document).ready(function () {
var listName = “Customers”;
var fields = ”;
var query = ”;
$().SPServices({
webURL: ‘https://abcd.com/Projects’,
operation: ‘GetListItems’,
listName: listName,
async: false,
CAMLQuery: query,
CAMLViewFields: fields,
CAMLRowLimit: 150,
completefunc:
function (xData, Status) {
arr_Customer = [];
$(xData.responseXML).find(“z\:row, row”).each(
function () {
arr_Customer = arr_Customer.concat($(this).attr(“ows_CustomerID1”));
});
arr_Customer.sort();
//end of each function/loop
} //end of completed function
}); //end of spservices call
}); //end of document ready function
$(document).ready(function () {
var listName = “Orders”;
var fields = ”;
var query = ”;
$().SPServices({
webURL: ‘https://abcd.com/Projects’,
operation: ‘GetListItems’,
listName: listName,
async: false,
CAMLQuery: query,
CAMLViewFields: fields,
CAMLRowLimit: 20,
completefunc:
function (xData, Status) {
arr_Order = [];
$(xData.responseXML).find(“z\:row, row”).each(
function () {
arr_Order = arr_Order.concat($(this).attr(“ows_OrderID”));
}); //end of each function/loop
arr_Order.sort();
} //end of completed function
}); //end of spservices call
}); //end of document ready function
Thanks in adv.
sspb4:
It really depends on what you want to display and how the two lists relate to each other. At the moment, you’re just putting the CustomerIDs and OrderIDs in two separate arrays. There must be some column in the two lists that you can use as a common key?
As for storing the lists in different sites, you can pass the webURL option to specify where they are located, as you are above.
Note that the .find(“z\:row, row”) syntax may not work the same across different browsers. Take a look at the SPFilterNode function.
M.
Thanks Marc. Sorry for incomplete information on the relationship between those lists. Let’s say the lists have same fields. For e.g., Customer List has Title field which holds different values and Orders list also has Title field which holds different values related to Order.
My concern is how would you combine the results of two different arrays scoped at different SPServices and then display a combined result from those arrays?
I also found this post which discusses the same scenario. http://spservices.codeplex.com/discussions/265771
I didn’t quite get the solution for my problem. May be because I m a new bie in this arena. Any help is much appreciated. Thanks a lot. :)
I m using http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js
and SPServices-0.7.0.min.js
Initially I used
$(xData.responseXML).find(“[nodeName=’z:row’]”).each(function() which didn’t fetch me data so on googling I found, z:\row, row which works fine with IE, ff and Chrome.Thanks for pointing out SPFilterNode function. I will consider that.
There must be some column(s) which relate the Orders to Customers, though, right? I’m assuming that you want to match those column values as a key to show orders per customer. You can do this by either:
It really depends on what you want to show, how many items you expect, etc. You don’t have to move the XML structures into arrays, as you can parse them directly; again, it really depends on what you need to display.
M.
Thanks for your prompt response. I will get clear requirements tomorrow. As of now, I only know that I need to combine the results from two different lists having same fields.
A list has – Title, Description fields.
B list has – Title, Description fields.
Need – combine A and B to show both the titles and descriptions as:
A_Title A_Description B_Title B_Description
A1 AD B1 BD
There’s no lookup column. If there’s a common column/s, the above suggested methods would be ideal. Thanks again for your precious time.