How to achive Sum Related in sharepoint list

Question

I have an issue. I have two lists here is schema:

First list:

 Name | Count | Sum
 asd  |   2   | 11
 wda  |   1   | 7

Second list:

LookUp/Name | Value
    asd     |   5
    asd     |   6
    wda     |   7

I solved the first part and get the count column on first list by using LookUp field and then pick Count Related option. I would like to use option like Sum Related but it dosen't exist. I can make it work programmatically and even from designer. Does anybody face similiar problem and deal with it ?

Answer 1

You need script to achieve that:

1. Get all related items from Second list;
2. Calculate the sum value for the Values of related items;
3. Return the sum value.

Here is a demo about how to use REST API to get related items and calculate the sum value, then use CSR to change the “Sum” value in the list view.

The first list (ListA):

• Created a field named “Sum”. Its internal field name is “Sum”.

The second list (ListB):

• has a lookup field named “ListA”; (internal field name is “ListA”)

• has a number field named “Value” (internal field name is “Value”).

(Adding the script below into the first list view page.)

<script src="https://code.jquery.com/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
(function () {
    var overrideNameField = {}; 
    overrideNameField.Templates = {};
    // This action will edit the "Sum" field. Change the "Sum" to another column’s internal name.
    overrideNameField.Templates.Fields = {
        "Sum": { "View": overrideNameFieldTemplate }        
    };    

    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideNameField);

})();
function overrideNameFieldTemplate(ctx) { 
//get the current item id.
var itemID = ctx.CurrentItem.ID;

// get the related items
var sum = getRelatedItems(itemID);

return sum;   
}

function getRelatedItems(itemID){
    var sum = 0;
    //var siteUrl = _spPageContextInfo.webAbsoluteUrl;
    var siteUrl = "https://<technet>.sharepoint.com/sites/<site>";
    var requestUri =  siteUrl + "/_api/Web/Lists/getbytitle('ListB')/Items?$select=Value,ListA/ID&$expand=ListA&$filter=ListA/ID eq " + itemID;
    $.ajax
    ({
    url: requestUri,
    type: "GET",
    cache: true,
    async: false,
    headers:{
    "ACCEPT": "application/json;odata=verbose"
         },
    success: function (data) {
    if($(data.d.results).length !== 0) 
        for (var i = 0; i < data.d.results.length; i++) {
            var item = data.d.results[i];
            sum += item["Value"];
        }
    },
    error: function () {
    }
    });
    return sum;
}
</script>

Result:

The first list (ListA):

The second list (ListB):