How to get sum of values in multiple lists

Question

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.

Kasper Bo Larsen · Accepted Answer · 2017-07-22 16:33:55Z

4

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

answered Jul 22, 2017 at 16:33

Kasper Bo Larsen

2,5563 gold badges15 silver badges21 bronze badges

Add a comment |

bgmCoder · Accepted Answer · 2017-07-29 22:03:35Z

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.

Stack Exchange Network

How to get sum of values in multiple lists

2 Answers 2

Your Answer

Linked

Hot Network Questions

How to get sum of values in multiple lists

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions