Need to calculate the sum of a column in related items based on a condition on the same related items and save it to

[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.

[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!

[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 6858 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

[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);

[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 6804 times

2. Console log shows some errors and doesn't show subtotal:
with the script

scriptissue2.png (29.21 KiB) Viewed 6804 times

without the script

scriptissue3.png (20.8 KiB) Viewed 6804 times

[mnikitina]

Katy,

Are you using SharePoint Online or On-premises?

Could you please share your code so I could test it.

Thank you!

[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?

[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.

[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.

[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!