4

Say I have ten subsites, each with it's own list that use the same content type. Each list has several number columns.

I want to create a list in the parent site that provides the sum of the number columns from those lists.

Now, I know you can use a CEWP and fetch some values from other lists, but this doesn't display like a list, and doesn't do the sum.

Basically, I want a list that is a summary of all the lists (in subsites) with that content type.

How can I do this?

So, I've gotten this far using REST:

var basurl = "https://my.site.com/multisite";
GetSites();

function GetSites(){
    $.ajax({
        //url: baseurl + "_api/search/query?querytext='contenttype:DROID'",
        url: baseurl + "_api/web/webs",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (site_data) {
            var sitedata = site_data.d.results;
            getSiteData(sitedata);
        },
        error: function (data) {
            console.log("GetSites Error: "+ data);
        }
    });
}

//do this for each site
function getSiteData(whatdata){
    $(whatdata).each(function(index,thisdata){  //splits information out of the primary array
    console.log("site Url:" + thisdata.Url);  
        getListsbyContentType(thisdata.Url);
    })
}

//gets all lists from a site
function getListsbyContentType(whatsiteurl){
     $.ajax({
        url: whatsiteurl + "/_api/search/query?querytext='contenttype:Item'",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (listct_data) {
            console.log("All CT data:" + listct_data.d.results);
            $(listct_data.d.results).each(function(index,listctdata){  //splits information out of the primary array
                console.log(listctdata);
            });
        },
        error: function (data) {
            console.log("getListsbyContentType Error: "+ data);
        }
    });
}

This will iterate through the sites okay, but it never finds my content type. Even if I change the content type from droid to ITEM it always comes up empty.

4

An alternative to the Search based solution could be a workflow on each list, that do the sum and updates the master list.

3

Here is a solution using the pnp JSCore library which leverates REST calls.

This code goes in a .js file and is loaded in an aspx page (or content editor - don't forget to add fetch.js and promises.js for IE - see the docs)

So I have several subsites, each with a list called Droids. Each list has columns: Title and RND (for random number). This code will fetch the values from the RND columns and total them for the list itself AND for all the lists. You just have to supply the url for baseurl.

The code will sum the totals for each list and for all lists in each of the subsites. Of course, it isn't looking via Content Type, but I'm sure that's possible too. This example only fetches the values based on list name.

var baseurl = "https://my.portal.sharepoint.com";
var multisite = baseurl + "/multi";
var droidlistName = "Droids";
var RNDtotal = 0;
getMultiSites(multisite);

//fetch all the subsites by url
function getMultiSites(whaturl){
    var w = new $pnp.Web(whaturl);
    w.webs.get().then(function(this_site) {
        $(this_site).each(function(index,thissite){
               // console.log("Site: " + thissite.Title + " : " + thissite.Url);
                getDroidLists(thissite.Url);
        })
    });
}

//fetch Droid list in a site
function getDroidLists(whatsite){
    console.log(whatsite);
    var w = new $pnp.Web(whatsite);    

    w.lists.getByTitle('Droids').get().then(function(this_list) {
        console.log("List Title:" + this_list.Title + " : Id:" + this_list.Id);
    });

    var listRNDtotal = 0;
    w.lists.getByTitle('Droids').items.get().then(function(list_fields) {

        $(list_fields).each(function(index,thisfield){
            if(thisfield.RND){
                RNDtotal = RNDtotal + thisfield.RND;
                listRNDtotal = listRNDtotal + thisfield.RND;
            }
            console.log(thisfield.Title + " : " + thisfield.RND);

        });
    }).then(function(){
        console.log("listRNDtotal=" + listRNDtotal);
        console.log("RNDtotal=" + RNDtotal);
    });    
}

The advantage to using this method over workflows is that if you need to edit it you can edit it from one place; you don't have to edit the workflows for each list - especially if you have 100 subsites, each with it's own list whence you need to fetch data. It's one single place for code that you attach to the page where you will display the data.

Just a word of caution - when you try to pass global variables, understand that the pnp methods are all running async - so you should use the .then() function to proceed for the next thing after your method is over. See this question for an example.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.