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 ?

up vote 0 down vote accepted

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):

enter image description here

The second list (ListB):

enter image description here

  • how does this script work in SharePoint Online where Microsoft have made it very difficult to add scripts to pages? – motionpotion Sep 17 at 7:26
  • 1
    It can work in classic experience in SharePoint Online. – JoannaW_MSFT Sep 17 at 7:29

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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