Totaling (sum) a column in fd.control List

Forms SharePoint Online
1

USE CASE: We have a List deployed in a form using fd.control. This list has a column (ValueFSD) calculated as a multiplication of two other columns in the same list. When we render the List in the form, we would like to sum all the rows and place the total at the top (if not, then the bottom) of the grid for that column.

ATTEMPT SO FAR: We reviewed the Kendo Grid Control documentation and decided that using the footerTemplate and/or the headerTemplate method might work. We tried many variants of the code but no success.

Here is where we landed as our best attempt:

fd.control('ListRICETask').ready(function(dt) {
dt.field("ValueFSD").footerTemplate(({ ValueFSD }) => Total: ${encode(ValueFSD.sum)});
});

Any suggestion?

2

Dear @vhancock,
Have you tried the Total property? Here - Data Table — Plumsail SharePoint Forms Documentation

3

I am using the Control "List or Library", not the control "Data Table". Is there a Total method on List control?

4

Dear @vhancock,
List or Library supports SharePoint List View settings:

image
image1054×624 94.1 KB

You can configure it for the selected List View inside of SharePoint UI:

image
image1689×422 16.8 KB

5

Thank you..

I understand that you can Sum a numeric column in SharePoint.

But you can not sum a calculated column in SharePoint and the field that I want to Sum on in the grid control is a calculated field.

6

Dear @vhancock,
Working with calculated columns is difficult for a reason - they do not store an actual value, just the formula, and the value has to be retrieved from the original fields that are part of calculation.

Moreover, List or Library control doesn't actually store any values unlike Data Table, all the values in List or Library control are retrieved as necessary - meaning you cannot even get all the values out of the control, if you have multiple pages of items.

To achieve what you want to achieve, you will need to use pnp-js to retrieve values from the source list, get values of fields that are used in calculated columns, do the same calculation with JavaScript, and then sum the results.

That's the only way I can think of summing calculated columns, not the easiest task by any means. Possible, sure, but much trickier than just summing a Number/Currency column.

7

Thank you Nikita, really appreciated. That is pointing me in the right direction.I will continue to work on this and if I am successful, will post the solution in this post.

By the way, have I told you recently how amazing your product is? It has completely transformed the user experience in SharePoint. Not only beautiful forms, but instantaneous actions with JS Code, unlike PowerAutomate that takes minutes to execute.

1 Like
8

Dear @vhancock,
It's a pleasure to hear it! Now, if you really need to work the way you've described - we can help get there, but probably one step at a time. I would break it down as following:

  1. Get the items with PNP-JS, configure filters to match your List or Library control view
  2. Get field values used in calculated fields, and do the same calculation with JavaScript
  3. Combine all values to get the sum of calculated fields

Now, I would recommend starting with step 1 and going to step 2, 3 as you progress, and we can help debug the code if you run into any issues or not sure what to do!

By the way, if you're enjoying the product, please, consider leaving a review or even better - contact support@plumsail.com to share your story, like others have done before, we really appreciate such feedback and in return could further help with code refactoring or custom solutions you might need!

9

Dear @vhancock,
I've run some basic tests with PNP-JS and it does seem like the calculated value is actually stored in items, so it doesn't need to be re-calculated, that's my bad:

image
image733×191 10 KB

It is returned as string though, but converting it shouldn't be that difficult.

10

Here is the code that we laned on. A lot of additional stuff in here, but directionally correct. Thanks again for you guidance. It really helped.

function setPercentage(){
    pnp.sp.web.lists.getByTitle("RICE Task").items
            //.select('PercentComplete', 'ID','ValueDEV', 'ValueFSD')
            .filter("RICEIDId eq " + fd.itemId )
            .get()
            .then(function(items){
                var sumPercentDEV = 0;
                var sumPercentFSD = 0;
                if(items.length == 0){
                    fd.field('TasksCreated').value = false;
                    
                    pnp.sp.web.lists.getByTitle("RICE").items.getById(fd.itemId).update({
                        TasksCreated: false
                    });
                    
                    
                }else{
                    for(var i = 0; i < items.length; i++){
                        sumPercentDEV += parseFloat(items[i].ValueDEV);
                        sumPercentFSD += parseFloat(items[i].ValueFSD);
                    }
                    sumPercentDEV = sumPercentDEV ;// items.length; //round
                    sumPercentFSD = sumPercentFSD ;// items.length; //round
                }
                
                pnp.sp.web.lists.getByTitle("RICE").items.getById(fd.itemId).update({
                        FSDPercentageComplete: sumPercentFSD,
                        DEVPercentageComplete: sumPercentDEV
                    }).then(function(result){ 
                        console.log('set fields');
                        fd.field('FSDPercentageComplete').disabled = false;
                        fd.field('DEVPercentageComplete').disabled = false;
                        fd.field('FSDPercentageComplete').value = (sumPercentFSD * 100).toFixed(2);   
                        fd.field('DEVPercentageComplete').value = (sumPercentDEV * 100).toFixed(2);   
                        fd.field('FSDPercentageComplete').disabled = true;
                        fd.field('DEVPercentageComplete').disabled = true;  
                    });
                
            });
}