Datatable custom math function to sum row values.

Webix JS
#1

I am trying to make a custom math function for datatable to sum up certain cells in each row if the id contains “hours”. In order to do so, I need to retrieve the cells in a row along with their id’s. I have tried assigning the datatable to a variable and using getItem(rowID) to access the values, however it doesn’t work on load as it seems to be calling my custom function before the dataTable variable gets assigned. Is there another way for me to retrieve these row values?

dataTable = webix.ui({

//later in webix config
{id: “Sum”, math: “sumHours($r)” }

function sumHours(rowID) {
//rowID does get passed, but the function breaks on the getItem() call.
var row = dataTable.getItem(rowID);
}

Additionally, my math function does not seem to run on any new rows that I add to the table.

#2

Hi,

The code works for me, when I’m using the id of datatable instead of the reference

function sumHours(rowID) {
	return $$("d1").getItem(rowID).id; 
}
webix.ui({
  view:"datatable",
  id:"d1"
  /* other config here */
});

Also, you can use scheme to define auto-calculated values

http://webix.com/snippet/fd503333

#3

Using the id instead of the reference did the trick. Thanks.

#4

Hi, I have now run into another issue regarding the math function. I have defined in my column configuration as mentioned in my previous post.

{id: "Sum", math: "sumHours($r)"

But now the problem I have is that the math rule from my column configuration does not apply when I add new rows.
I’m using:

newRowID++;
var newRow = {id: newRowID, ... }
$$("timecard").add(newRow, 0);

All the rows that were loaded on start work, but the new rows won’t run my math function. Do I have to apply the math rule for each row I add?

#5

Please grab the updated package ( Webix 2.3.14 ) where this issue must be fixed.

Latest package is available on site ( pro version can be taken from client’s area ) as well as through Bower or NuGet

#6

Thanks, updating fixed it.

#7

Hello!

I have a little problem with the sum values in a row when I use my custom function. I assign this function in columns configuration of a DataTable.

Here is a part of the declaration:

{
  id: "grid_TransactionsOfPerson",
  view: "datatable", 
  columns:[
   { id: "id", hidden: true},
   //...
   { id: "FULL_SUM", header: "Sum", width: 100, math: "CalcSumOfFunds($r,   'grid_TransactionsOfPerson')"} 
]
...
}

And this is my function:

function CalcSumOfFunds(pn_RowId, pc_DataTable)
{
  var mo_DT = $$(pc_DataTable);
  if (!mo_DT)
    return 0; 
  var mo_Row = mo_DT.getItem(pn_RowId);
  console.log("mo_Row: ", mo_Row); 
  if (!mo_Row)
    return 0;

  var mn_Sum = 0; 
  Object.keys(mo_Row).map(
  function(k) 
  { 
    if (k.substring(0, 5) == "FUND_")
    { 
       mn_Sum += isNaN(mo_Row[k]) ? 0 : mo_Row[k] * 1; 
    } 
   }
  ); 
  return mn_Sum;
}

As you can see this functions calculates of the sum of the “FUND_” cells.

It is working well in a normal row. But when I insert a new record/row and this new record get the final id from the serverside (MySQL) the pn_RowId is not updated that’s why getItem cannot find the row.

Is there any good solution to solve it?

Thank you!

#8

I found a solution but I am not sure that this is the best solution for this problem.

Here is the code:

grid_TransactionsOfPerson_dp = webix.dp(grid_TransactionsOfPerson);

grid_TransactionsOfPerson_dp.attachEvent("onAfterInsert", 
function (id, status, response, details)
{
var rowid = grid_TransactionsOfPerson.getSelectedId(true);
var record = grid_TransactionsOfPerson.getItem(rowid);	
record["$FULL_SUM"] = "=CalcSumOfFunds(" + id.newid + ", 'grid_TransactionsOfPerson')";
grid_TransactionsOfPerson.updateItem(rowid, record); 
}
);
#9

Hello @David82,

Math is recalculated on the onStoreUpdated event and item data is accessible by the old client-side ID then.

If you still want to trigger recalculation, you can call the .refreshColumns() function of the master grid after the data is inserted.

Please, check: http://webix.com/snippet/e73d1c0f (In the snippet tool server-side ID is always ‘0’)

#10

Hello @Helga,

thanks your answer but I do not understand why DataTable does not handle it automatically? Why is the extra programming necessary?

In this case the refreshColumns() is not fully perfect solution for me because I also modified the behavior of the ENTER key. When the user press the ENTER the selection jump to the next column and automatically put the cell into edit mode. But if I use your solution the cell does not change to edit mode.
Of course, I am happy that my solution is also working.

So, I suggest to your team that please think about the automatic working (id changing <-> math functions).

Other topic:
If I can ask other thing please see this also (editable property):
http://docs.webix.com/api__link__ui.datatable_editable_config.html

I think this problem is also a little bit important. Thank you! :slight_smile:

#11

The above behavior (not recalculating math for newly added rows) is a bug and will be fixed. The solution with refreshColumns is a quick workaround and not a final solution of course.

#12

It was fixed in 5.2.2. Thanks. :slight_smile: