Kendo Grid Running sum total

[Gaysorn]

Hi, 

I'm working on develop web application using MVC Core (razor). One of the view will have column with running total from previous row (see picture below) . I try to use Kendo Grid (GridEditMode.InCell) to do the job and write some codes to save to database but I cannot get amount from previous row to display. The first row the users enter the number to start and the row after that will need to get the amount from previous pulse the number from column b, c, d.

Do you have any recommendation what is the better tools or widgets to use to handle all these requirement? Thank you. 

[Stoyan]

Hi GaySorn,

To achieve the requirement as I understand it you can use the Grid Component. First a newly added row should be placed on the bottom of the Grid and then use the dataItem of the last row to populate the fields of the new row.

1. Define the Editable configuration of the Grid to set up the position of newly inserted rows:

   .Editable(settings => settings.CreateAt(GridInsertRowPosition.Bottom).Mode(GridEditMode.InCell))

2. Capture the Edit event of the Grid.

    .Events(e => e.Edit("onEdit"))

3. In the handler get the array of the Grid's dataItems with the use of the dataSource.data method.
4. The last item of the array will be empty because it represents the newly added row, the one before it contains the need data.
5. Use the kendo.data.Model's method isNew to determine if a new row was created.
6. Then you can use the Model's set method to set the dataItems of the new row.
7. Alternatively, use jQuery to set the value of the currently edited input.

   function onEdit(e) {
           var data = e.sender.dataSource.data();
           var dataItem = data[data.length - 2];
           if (e.model.isNew()) {
               $("input#Freight").val(dataItem.Freight);
               e.model.set("ShipCity", dataItem.ShipCity);
               e.model.set("ShipName", dataItem.ShipName);
               //$("input#ShipName").val(dataItem.ShipName)
               //$("input#ShipCity").val(dataItem.ShipCity)
               //the commented lines are useful with InLine editing
           }
       }

For your convenience I am attaching a sample project that showcases the above. Please let me know, if additional questions arise. Thank you.

Regards,
Stoyan
Progress Telerik

[Gaysorn]

Hi Stoyan,

Thank you for your reply to my question.

We do not allow to insert or delete to this grid. We will populate all rows we need to this table and allow users to enter the data only. The row will be fixed rows (such as 5 rows) depend on our requirement. As the sample below, the user will enter the number in row-1 column (g) only to start and row-2 in column (d) or (e) or (f) depend on their need (they may enter only one or two or three column) Each time users entered the number, column (g) current row will calculate the new balance by using the number from previous row column (g) + ((d) + (e) - (f)) formula

Here is my view codes

@(Html.Kendo().Grid<RateCases.ViewModels.EnergyEfficiencyProgramViewModel>()
                .Name("myGrid")
                .Width(1200)
                .Columns(column =>
                {
                    column.Bound(c => c.SequenceNumber).Hidden();
                    column.Bound(c => c.TestYearMinus).Encoded(false).Width(150)
                        .Title("(a) ");

                    column.Bound(c => c.CommunityProgram).Encoded(false).Width(30)
                        .Title("Commitment to Community Program<br />(b)")
                        .ClientTemplate("<input type='checkbox' id='CommunityProgram' #= CommunityProgram? checked='checked' : '' #disabled='disabled' class='chkbx' />")
                        .HtmlAttributes(new { style= "text-align:center!important" });

                    column.Bound(c => c.FocusDOA).Encoded(false).Width(30)
                        .Title("Focus on Energy<br />(c)")
                        .ClientTemplate("<input type='checkbox' id='FocusOnEnergy' #= FocusOnEnergy? checked='checked' : '' #disabled='disabled' class'chkbx' />")
                        .HtmlAttributes(new { style = "text-align:center!important" });

                    column.Bound(c => c.AdjustmentAmount).Encoded(false).Width(150)
                        .Title("Adjustment<br />(d)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Adjustment<br />(d)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.Account186Amount).Encoded(false).Width(150)
                        .Title("Account 186<br />Expenditures<br />(e)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Account 186<br />Expenditures<br />(e)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.Account253Amount).Encoded(false).Width(150)
                        .Title("Account 253<br />Collection(-)<br />(f)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Account 253<br />Collection(-)<br />(f)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.NetBalanceAmount).Encoded(false).Width(150)
                        .Title("December 31<br />Balance<br />(g)")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important" });
                })
                .Editable(ed => ed
                    .CreateAt(GridInsertRowPosition.Bottom)
                    .Mode(GridEditMode.InCell)
                )
                .Events(e => e
                    .Edit("onEdit")
                )
                .Navigatable(true)
                .DataSource(ds => ds
                    .Ajax()
                    .Batch(true)
                    .ServerOperation(false)
                    .Model(m =>
                    {
                        m.Id(mm => mm.RateCaseId);
                        m.Id(mm => mm.SequenceNumber);
                        m.Field(mm => mm.TestYearMinus).Editable(false);
                        m.Field(mm => mm.NetBalanceAmount).Editable(false);
                    })
                    .Events(ev => ev
                        .RequestEnd("onRequestEnd")
                        .Change("updateTotal")
                    )
                    .Read(read => read.Action("EnergyEfficiencyProgramLoad", "ScheduleFourMisc", new { id = Model.RateCaseId }))
                    .Update(update => update.Action("EnergyEfficiencyProgramSave", "ScheduleFourMisc").Data("addParameter"))
                )
            )

function onEdit(e) {

        $("#NetBalanceAmount").attr("disabled", "disabled");

        if (e.model.SequenceNumber == 0) {
            $("#CommunityProgram").attr("disabled", "disabled");
            $("#FocusOnEnergy").attr("disabled", "disabled");
            $("#AdjustmentAmount").attr("disabled", "disabled");
            $("#Account186Amount").attr("disabled", "disabled");
            $("#Account253Amount").attr("disabled", "disabled");
            $("#NetBalanceAmount").removeAttr('disabled');
        }


        if ("@Model.ScheduleIndex.ScheduleStatus" == "C") {
            e.container.find("input").each(function () {
                $(this).attr("disabled", "disabled")
            });
        }
    }

// Event Change on dataSource
    function updateTotal(e) {
        //PreviousBalance();
        // Need to get previous row balance for NetBalanceAmount
        var balance = 0;
        if (e.action == "itemchange") {       
            var item = e.items[0];

            var adjustment = e.items[0].AdjustmentAmount;
            var account186 = e.items[0].Account186Amount;
            var account253 = e.items[0].Account253Amount;

            if (!adjustment) adjustment = 0;
            if (!account186) account186 = 0;
            if (!account253) account253 = 0;

            var total = parseInt(balance) + (parseInt(adjustment) + parseInt(account186) - parseInt(account253));

            if (total != 0) {
                item.set("NetBalanceAmount", total);
            }
            else {
                item.set("NetBalanceAmount", "");
            }
        }
    }

Thank you for your help.

                

[Stoyan]

Hi Gaysorn,

Even if you do not  allow users to edit the Grid, I'd still recommend using the Edit event as it is also thrown when a new row is created.

In such a case you can skip the check whether a model is new as it will always be new when this event is handled. That being said the approach of getting the previous rows' items remains the same.

Then to define the logic in which column (g) is calculated, I'd recommend you to utilize the CellClose event:

.Events(e => e.Edit("onEdit").CellClose("onClose"))

function onClose(e) {
        var dataItem = e.sender.dataItem(e.container.parent().prev());
        if(e.type == "save" && dataItem!=null){
             e.model.set("NetBalanceAmount", (dataItem.NetBalanceAmount + e.model.Account186Amount  + e.model.AdjustmentAmount ) -e.model.Account253Amount);
        }
    }

The expression e.container.parent().prev() gets the previous row. When you pass a row to the dataItem method of the Grid you'll get its dataItem.

I hope the information above is helpful.

[Gaysorn]

Thank you for your help. The sample is working and helpful but our users changed their mind. They want the last column(g) balance to calculate and display with new value every time they make change or update to any column in d, e, and f. I can get it to re-calculate but the values do not update/change in the column g but if I click  on the column g the new value will display.

I have my codes below, thank very much.

@(Html.Kendo().Grid<RateCases.ViewModels.EnergyEfficiencyProgramViewModel>()
                .Name("myGrid")
                .Width(1200)
                .Columns(column =>
                {
                    column.Bound(c => c.SequenceNumber).Hidden();
                    column.Bound(c => c.TestYearMinus).Encoded(false).Width(150)
                        .Title("(a) ");

                    column.Bound(c => c.CommunityProgram).Encoded(false).Width(30)
                        .Title("Commitment to Community Program<br />(b)")
                        .ClientTemplate("<input type='checkbox' id='CommunityProgram' #= CommunityProgram? checked='checked' : '' #disabled='disabled' class='chkbx' />")
                        .HtmlAttributes(new { style= "text-align:center!important" });

                    column.Bound(c => c.FocusDOA).Encoded(false).Width(30)
                        .Title("Focus on Energy<br />(c)")
                        .ClientTemplate("<input type='checkbox' id='FocusOnEnergy' #= FocusOnEnergy? checked='checked' : '' #disabled='disabled' class'chkbx' />")
                        .HtmlAttributes(new { style = "text-align:center!important" });

                    column.Bound(c => c.AdjustmentAmount).Encoded(false).Width(100)
                        .Title("Adjustment<br />(d)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Adjustment<br />(d)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.Account186Amount).Encoded(false).Width(100)
                        .Title("Account 186<br />Expenditures<br />(e)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Account 186<br />Expenditures<br />(e)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.Account253Amount).Encoded(false).Width(100)
                        .Title("Account 253<br />Collection(-)<br />(f)")
                        .ClientHeaderTemplate("<span title='Manual Entry' style='color:maroon'>Account 253<br />Collection(-)<br />(f)</span>")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important", @class = "editable-cell" });

                    column.Bound(c => c.NetBalanceAmount).Encoded(false).Width(100)
                        .Title("December 31<br />Balance<br />(g)")
                        .Format("{0:n0}")
                        .HtmlAttributes(new { style = "text-align:right!important" });
                })
                .Editable(ed => ed
                    .CreateAt(GridInsertRowPosition.Bottom)
                    .Mode(GridEditMode.InCell)
                )
                .Events(e => e
                    .Edit("onEdit")  //.CellClose("onClose")                    
                )
                .Navigatable(true)
                .DataSource(ds => ds
                    .Ajax()
                    .Batch(true)
                    .ServerOperation(false)
                    .Model(m =>
                    {
                        m.Id(mm => mm.RateCaseId);
                        m.Id(mm => mm.SequenceNumber);
                        m.Field(mm => mm.TestYearMinus).Editable(false);                       
                    })
                    .Events(ev => ev
                        .RequestEnd("onRequestEnd")                    
                    )
                    .Read(read => read.Action("EnergyEfficiencyProgramLoad", "ScheduleFourMisc", new { id = Model.RateCaseId }))
                    .Update(update => update.Action("EnergyEfficiencyProgramSave", "ScheduleFourMisc").Data("addParameter"))
                )
            )

function onEdit(e) {
        $("#NetBalanceAmount").attr("disabled", "disabled");

        if (e.model.SequenceNumber == 0) {
            $("#CommunityProgram").attr("disabled", "disabled");
            $("#FocusOnEnergy").attr("disabled", "disabled");
            $("#AdjustmentAmount").attr("disabled", "disabled");
            $("#Account186Amount").attr("disabled", "disabled");
            $("#Account253Amount").attr("disabled", "disabled");
            $("#NetBalanceAmount").removeAttr('disabled');
        }

        updateTotal();

        if ("@Model.ScheduleIndex.ScheduleStatus" == "C") {
            e.container.find("input").each(function () {
                $(this).attr("disabled", "disabled")
            });
        }
    }

 

function updateTotal() {

        var grid = $("#myGrid").data("kendoGrid").dataSource.data();
        var balance = 0;

        if (grid.length > 0) {
            for (var i = 0; i < grid.length; i++) {                
                var item = grid[i];
               
                if (i == 0) {
                    balance = grid[i].NetBalanceAmount;
                    if (!balance) balance = 0;
                }
                var adjustment = grid[i].AdjustmentAmount;
                var account186 = grid[i].Account186Amount;
                var account253 = grid[i].Account253Amount;

                if (!adjustment) adjustment = 0;
                if (!account186) account186 = 0;
                if (!account253) account253 = 0;

                balance = parseInt(balance) + (parseInt(adjustment) + parseInt(account186) - parseInt(account253));
                //var data = grid.dataSource.data()[0];
              
                if (balance != 0) {                   
                    item.set("NetBalanceAmount", balance);
                }
                else {
                    item.set ("NetBalanceAmount", "");
                }                             
            }
        }        
        return;        
    }

 

 

[Stoyan]

Hello Gaysorn,

Thank you for sharing your modified code and the comprehensive explanation alongside it.

Upon examining the code snippet I noticed that the Grid's DataSource Model configuration has 2 declared Ids which might cause conflicts when dataItems are manipulated. Please make sure that the Model has only one Id defined.

     .DataSource(ds => ds
                    .Ajax()
                    .Batch(true)
                    .ServerOperation(false)
                    .Model(m =>
                    {
                        m.Id(mm => mm.RateCaseId);
                        //m.Id(mm => mm.SequenceNumber);
                        m.Field(mm => mm.TestYearMinus).Editable(false);                       
                    })

In addition in the onEdit handler you disable the #NetBalanceAmount input which likely prevents it from being updated. Instead of disabling the input I recommend to make it readonly.

       $("#NetBalanceAmount").attr("readonly", "readonly");

If the suggestions above still don't resolve the issue, please consider modifying the sample project I have attached to my original answer to replicate the reported behavior as this will allow me to research the issue in more depth and suggest a more appropriate solution.