[Data Grid Web] Add group total row with recalculated values
Forge component by OutSystems R&D
Application Type
Traditional Web

Hi,

I need to add a totals row at the end of the table with some previously calculated values. For example I have to add weighted averages (AVG1 = column1*column2/sum(column2)).


I used the code from here https://www.outsystems.com/forums/discussion/68109/datagrid-add-group-total-sum-row-in-datagrid/ and made some adjustments:

var addSumRow = function(gridId,age){

var gObj = GridOS.ComponentUtils.getGridObjectById(gridId);

        var grid = gObj.grid;

        grid.columnFooters.rows.push(new wijmo.grid.GroupRow());

        grid.bottomLeftCells.setCellData(0, 0, 'S');

        grid.columnFooters.setCellData(0,10,age);

};

And it works in preparation. However I can't update this line when I use the SendDirtyRowsButton function.

Can anyone help me?



Thanks.

Solution

Hello @Joana Martins 

If I could understand your needs you are trying to perform some Math referencing two columns (AVG1 = column1*column2/sum(column2)),  this is not support by default on our grid. Although you can achieve it with some code.

Looking to your code I was in doubt if you want to update only the footer or cells on the grid, so I will try to answer in a generic way.

Can you perform your math after a cell edition for example? Instead of waiting to hit the SendDirtyRows?

If so, you can try the code bellow:

    //Event triggered after cell edition
    var editCellHandler = function (grid, e) {
        if (e.getColumn().binding === 'Rate') {
            //How you can access row's data
            var dataItem = grid.rows[e.row].dataItem;
            
            //Your math goes here!
            //grid.columnFooters.setCellData(0,2,888);
        }
    }
    
    GridOS.ExternalAPI.afterGridInit((gObj) => {
            gObj.grid.cellEditEnded.addHandler(editCellHandler);
        }
    );

There is another option, but only if the Rate column is readonly, otherwise you will face problems. The formatItems event, will be triggered on any update (cell, scroll, etc), so be careful here to avoid performance issues.

//FormatItem will run for every cell on the grid, everytime you do a scroll for example,
    //But depending on how you have to do your math, it is the only way
    var customFormatItem = function (grid, e) {
        if (e.panel.cellType === wijmo.grid.CellType.Cell) {
            if (e.getColumn().binding === 'Rate') {
                //How you can access row data
                var dataItem = grid.rows[e.row].dataItem;
                //Your math goes here!
                //Bellow how you can set value on the cell
                e.cell.innerHTML = '777';                
            }
        }
    }
    
    GridOS.ExternalAPI.afterGridInit((gObj) => {
            gObj.grid.formatItem.addHandler(customFormatItem);
        }
    );

Please let me know if I could help you

Ricardo Valim


Hi Ricardo,

Thank you. What you proposed solved my problem.

I used the second code because I just wanted to update the values after updating the grid on the server. 

However, I had to adjust the code. With the formatItem event the screen didn't load the information and gave timeout. So I was experimenting with other events and the one that solved my problem was loadedRows.


Thank you very much for your help.

Joana

Hello @Joana Martins 

I didn't understand the SendDirtyRowsButton, sorry... Could you please provide a sample for us, this way I can be faster on answering your question.

Thank you

Ricardo Valim

Hi @Ricardo Valim,

Thank you for your reply.

The SendDirtyRowsButton is a datagrid action that updates in the database the data inserted/edited in the data grid. I would like this action to be able to update the value entered in the totals row.

I send attached an example: the rate column can be changed and when I send the values to the server through the SendDirtyRowsButton action I would like to update the value in the totals row, rate column.

Thanks,

Joana Martins

DataGridAddRowSum.oml

Solution

Hello @Joana Martins 

If I could understand your needs you are trying to perform some Math referencing two columns (AVG1 = column1*column2/sum(column2)),  this is not support by default on our grid. Although you can achieve it with some code.

Looking to your code I was in doubt if you want to update only the footer or cells on the grid, so I will try to answer in a generic way.

Can you perform your math after a cell edition for example? Instead of waiting to hit the SendDirtyRows?

If so, you can try the code bellow:

    //Event triggered after cell edition
    var editCellHandler = function (grid, e) {
        if (e.getColumn().binding === 'Rate') {
            //How you can access row's data
            var dataItem = grid.rows[e.row].dataItem;
            
            //Your math goes here!
            //grid.columnFooters.setCellData(0,2,888);
        }
    }
    
    GridOS.ExternalAPI.afterGridInit((gObj) => {
            gObj.grid.cellEditEnded.addHandler(editCellHandler);
        }
    );

There is another option, but only if the Rate column is readonly, otherwise you will face problems. The formatItems event, will be triggered on any update (cell, scroll, etc), so be careful here to avoid performance issues.

//FormatItem will run for every cell on the grid, everytime you do a scroll for example,
    //But depending on how you have to do your math, it is the only way
    var customFormatItem = function (grid, e) {
        if (e.panel.cellType === wijmo.grid.CellType.Cell) {
            if (e.getColumn().binding === 'Rate') {
                //How you can access row data
                var dataItem = grid.rows[e.row].dataItem;
                //Your math goes here!
                //Bellow how you can set value on the cell
                e.cell.innerHTML = '777';                
            }
        }
    }
    
    GridOS.ExternalAPI.afterGridInit((gObj) => {
            gObj.grid.formatItem.addHandler(customFormatItem);
        }
    );

Please let me know if I could help you

Ricardo Valim


Hi Ricardo,

Thank you. What you proposed solved my problem.

I used the second code because I just wanted to update the values after updating the grid on the server. 

However, I had to adjust the code. With the formatItem event the screen didn't load the information and gave timeout. So I was experimenting with other events and the one that solved my problem was loadedRows.


Thank you very much for your help.

Joana

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.