Summarize related items in quick code

[Katerina.C]

Hi,

I have form with related items in Quick mode. Im summarizing field "Costs" from related list and setting value in parent list. Type of both field (field with result and "Costs") are Currency. If I add new items to related list and click save form, new rows are summed twice. Then if I edit form again sum is recalculate to right result.

Im using code:

var total = 0;
var rows = fd.relatedItems(0).data('ctx').ListData.Row;
rows.forEach(function(item) {
total += parseFloat(item['Costs.'])
});
fd.field('Costs').value(total);

Code Im using in Javascript editor and in Onclick. I tried to use code with fd.onsubmit, but the result was the same.

[Nikita Kurguzov]

Dear Katerina,
Yes, this is a known bug for newly added items, but you can filter out duplicates with the following code:

Code: Select all

var rows = fd.relatedItems(0).data('ctx').ListData.Row;
var dupes = {};
var singles = [];

$.each(rows, function(i, el) {
    if (!dupes[el.ID]) {
        dupes[el.ID] = true;
        singles.push(el);
    }
});

//do the calculations for singles variable, instead of rows

[Katerina.C]

It is not counting twice anymore. Thank you.

I just noticed that if I delete related item, total won´t recalculate. Could you tell me where is a mistake?

var total = 0;
singles.forEach(function(item) {
total += parseFloat(item['Costs.'])

});
total = total.toFixed(2).replace(".",",");
fd.field('Costs').value(total);


Thank you very much.

K.

[Dmitry Kozlov]

Hi!

It seems that the grid does not refresh ListData property dynamically. Please, use the code below to get the actual grid data:

Code: Select all

var ctx = fd.relatedItems(0).data('ctx')
var jsgrid = document.getElementById('spgridcontainer_' + ctx.wpq).jsgrid
jsgrid.GetAllDataJson(function(d) { 
    var data = JSON.parse(d);
    console.log(data.gridPaneData.matrix); 
});

[Katerina.C]

Should I change something in your code? Console writes error Unable to get property of an undefined or zero reference.


Thanks

[Dmitry Kozlov]

Hi!
Which property from my code is undefined? Could you also provide a screenshot of your form?

[Katerina.C]

Sorry, your code was OK. Thanks.
I hopefully have last problem. If Cost is in thousands or more, result of summarize is wrong. For example 50 + 1000 + 100 = 151. Type of column Cost is Currency.

Can you look at script, where is problem? Thank you very much.

Code: Select all

var jsgrid = document.getElementById('spgridcontainer_' + ctx.wpq).jsgrid
jsgrid.GetAllDataJson(function(d) { 
    var data = JSON.parse(d);

function sum(indexColumn) {
 var total = 0; 
for (var i = 0; i < data.gridPaneData.matrix.length - 1; i++)
{

              total = total + (parseFloat(data.gridPaneData.matrix[i][indexColumn]));
			
}
console.log(total); 
return total;
}

var Sum = sum(5);  
console.log(Sum);
fd.field('Cost').value(Sum);

Update: I tried to summarize column Number and result is wrong too.

[Katerina.C]

Maybe I get a solution. Can you check it? Thanks

Code: Select all

 total = total + parseFloat(data.gridPaneData.matrix[i][indexColumn].replace(/\s+/g, ''));	

[Nikita Kurguzov]

Dear Katerina,
The issue is the fact that the values are stored as text and that includes currency number, commas between thousands, etc. As you correctly noticed, this can fixed by removing all these characters, I'll just recommend using it like this:

Code: Select all

var ctx = fd.relatedItems(0).data('ctx');
var jsgrid = document.getElementById('spgridcontainer_' + ctx.wpq).jsgrid;

jsgrid.GetAllDataJson(function(d) { 
    var data = JSON.parse(d);
    function sum(indexColumn) {
        var total = 0; 
        for (var i = 0; i < data.gridPaneData.matrix.length - 1; i++){
            total = total + (parseFloat(data.gridPaneData.matrix[i][indexColumn].replace(/[^0-9.]*/g, '')));            
        }
    console.log(total); 
    return total;
    }

    var Sum = sum(5);  
    console.log(Sum);
    fd.field('Cost').value(Sum);
});

[Katerina.C]

Hi,

is it possible to add into the code condition to summarize only rows with Status Approved? Now We are loading all data.

Thank you very much,

Katerina