Page 1 of 2
Summarize related items in quick code
Posted: 13 Aug 2018
by 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.
Re: Summarize related items in quick code
Posted: 13 Aug 2018
by 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
Re: Summarize related items in quick code
Posted: 17 Aug 2018
by 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.
Re: Summarize related items in quick code
Posted: 20 Aug 2018
by 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);
});
Re: Summarize related items in quick code
Posted: 23 Aug 2018
by Katerina.C
Should I change something in your code? Console writes error Unable to get property of an undefined or zero reference.
Thanks
Re: Summarize related items in quick code
Posted: 23 Aug 2018
by Dmitry Kozlov
Hi!
Which property from my code is undefined? Could you also provide a screenshot of your form?
Re: Summarize related items in quick code
Posted: 05 Nov 2018
by 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.
Re: Summarize related items in quick code
Posted: 05 Nov 2018
by 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, ''));
Re: Summarize related items in quick code
Posted: 06 Nov 2018
by 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);
});
Re: Summarize related items in quick code
Posted: 12 Apr 2019
by 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