Page 1 of 1
Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 25 Apr 2018
by shakonarson
Hi
I have a form which is registration of Audits and audits results/findings.
For a given time period, say 2-4 weeks people are adding to the related items on that form and I need to maintain a count of the items in the master form/list.
In the related Items list I have two types of findings and on the master form I have two columns for those different types of findings.
So I need to calculated the number of findings from type A in the related Items list and store that in column A in master form and do the same for type B. This needs to be done any time a new item is added to the related items list.
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 26 Apr 2018
by Nikita Kurguzov
Dear Shakonarson,
Please, check out this topic for calculating Sum -
viewtopic.php?f=1&t=1094&p=4546
You should also be able to retrieve the other field and use it as a condition. Running the code when new item is added is tricky though. I would recommend to do it each time you open/save the form, so you always get the correct value saved, but also add a re-calculate button on the form that will allow users to see the latest value.
Let me know if you have any question!
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 12 Sep 2019
by Katy
Hi,
Sorry to reanimating this topic again (and i've read all the other forum treads about that)
but i am trying to do the calculation in related item based on the other field in the same related item, here is the picture:
- 9-12-2019 1-52-22 PM.png (47.51 KiB) Viewed 4824 times
So i need to sum the amount based on the classification. Could you help me with the condition based on related item field?
Regards,
Katy
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 16 Sep 2019
by mnikitina
Hello Katy,
Please see below the code example which filters array and calculates the total. Please replace 'number' and 'choice' with your column names.
Code: Select all
var rows = fd.relatedItems(0).data('ctx').ListData.Row;
var filteredArray = [];
var subTotal = 0;
$.each(rows, function(i, el) {
if (el['choice'] == "Choice 1") {
filteredArray.push(el);
}
});
filteredArray.forEach(function(item) {
subTotal += parseFloat(item['number.']);
});
console.log(subTotal);
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 18 Sep 2019
by Katy
Unfortunately this is not working, i've tried different variations... And there are some unexpected behavior:
1. When i add the script to the form the related item table is displayed at the bottom of the form :
- scriptissue1.png (124.47 KiB) Viewed 4770 times
2. Console log shows some errors and doesn't show subtotal:
with the script
- scriptissue2.png (29.21 KiB) Viewed 4770 times
without the script
- scriptissue3.png (20.8 KiB) Viewed 4770 times
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 19 Sep 2019
by mnikitina
Katy,
Are you using SharePoint Online or On-premises?
Could you please share your code so I could test it.
Thank you!
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 23 Sep 2019
by Katy
I am using On-Premises. The code is basically only your sum, by the way it is now showing in the correct place and even tries to calculate, but the total is =0 :
Code: Select all
$('#_fd_parent_temp > input').val(window.top.fd._tempParentId());
var rows = fd.relatedItems(0).data('ctx').ListData.Row;
var filteredArray = [];
var subTotal = 0;
$.each(rows, function(i, el) {
if (el['Classification'] == "Capitalized") {
filteredArray.push(el);
}
});
filteredArray.forEach(function(item) {
subTotal += parseFloat(item['InvAmount.']);
});
console.log(subTotal);
fd.field('MTD_x0020_Capitalized_Check').value(subTotal);
fd.onsubmit(function(){
if (fd.relatedItems(0).data('ctx').ListData.Row.length == 0) {
alert('You need to submit at least one invoice information');
return false;
}
return true;
});
Or did you mean the source code? Can i send it to support rather then share it here?
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 24 Sep 2019
by mnikitina
Katy,
The code is correct. The problem is in the column name.
Please try to replace 'InvAmount.' with the name of your column without spaces 'InvoiceAmount.'
Also, double-check the name of the column with the Choice field and the value.
If it does not calculate the total after that, please share the console error.
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 24 Sep 2019
by Katy
Sorry, this is my mistake: the name of the column is actually correct in the code: i replaced the column thinking that the space in the name and all that _x200_ symbols are causing trouble, So on the screenshots above i have the old column, but the new one is actually InvAmount.
Re: Need to calculate the sum of a column in related items based on a condition on the same related items and save it to
Posted: 24 Sep 2019
by Katy
Oh my...... sorry !!!!!!!! You were absolutely right! I am just helping with this project and other person created lists, so i just found out that the internal name of the related item field is different because they renamed the column! ((( ohhhh.... it is working great!!! thank you!