Sum Calculated Field from Related List Items

[enillrae]

Hi,

I would like to sum the related list items on a form. However, the column to be sumed is a calculated column. I'm using the following but my answer still comes up as '0'.

calculateTotals();

function calculateTotals(){

var subtotal= 0;

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

console.log("Sub-Total Score " +subtotal);

}end calculateSubtotals

fd.onsubmit(function(){
fd.field('B7bTotalScore').value(subtotal);


});

Help please. Thanks

[Nikita Kurguzov]

Dear enillrae,
The command should work with Calculated fields, make sure that the Intenal Name of the field is correct (do you need the dot in the end?).

Also, what about the console? Do you get 0 in the console as well? The issue I see here is that subtotal variable is initialized inside calculateTotals() function, but then used outside of it in fd.onsubmit event, but it would be unacessible by this point, so this might be the reason you are not getting the correct result.

Something like this might work better:

Code: Select all

var subtotal= 0;

function calculateTotals(){
    var rows=fd.relatedItems(0).data('ctx').ListData.Row;
    rows.forEach(function(item){
        subtotal += parseFloat(item['SubTotalScore.']);
    });
    console.log("Sub-Total Score " +subtotal);
}

calculateTotals();

fd.onsubmit(function(){
    fd.field('B7bTotalScore').value(subtotal);
});

[bnunweiler]

Hello Nikita, I have tried this code and it works - sort of. I've seen this posted a few times and I've never seen a proper solution. Here goes:

If I use a related-items control on an edit form and I create new items, and then submit it, it always counts the rows twice. If go back an edit the edit form, it properly counts the rows once.

Do you understand what I mean?

Thanks,

[Nikita Kurguzov]

Dear bnunweiler,
It does happen occasionally that items get duplicates, but it can be resolved by filtering rows to only the unique ones, like this:

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

[Wikus]

Hello Nikita, I am attempting the above, but cant seem to get it working.

I do have a few possible hurdles though, if you could please let me know if it is something that i need to change or if i can code around it?

I have 2 related-items lists, in 2 separate tabs. Currently i am doing a work around on the child-level, where a workflow updates the total on the parent list. It works fine, except when users start deleting items on the child-list and the totals are no longer accurate.

Apart from them being in tabs (for GUI purposes to save space) the names of the fields on both child-lists are exactly the same.
The script you offered above mentions fd.relatedItems(0), which i am assuming would be the first child list and fd.relatedItems(1) would become the second. How would i positively recognize which is which?

Long story short, i need to take the totals added in my Quotation Entries child-list (field called Amount) and update it to my parent list (field called Quote Value) The process for the Order part is exactly the same as the Quotation part.

Assistance with this would be greatly appreciated.
Thank you.

[Nikita Kurguzov]

Dear Wikus,
To find out which Related Items you select, try to enter this code into browser's console:

Code: Select all

fd.relatedItems(NUMBER_HERE);

It will return a JQuery element which corresponds to the particular Related Items control on the form. If you place the mouse cursor over it, it will highlight (done in Chrome):

FindRelatedItems.png (35.63 KiB) Viewed 4361 times

You can also check if all rows are available, if you enter the following code into the console:

Code: Select all

fd.relatedItems(NUMBER_HERE).data('ctx').ListData.Row;

[Wikus]

Thank you for the clarification Nikita.

It must be my poor coding skills but I dont seem to be able to pin this down.
I have created a test list just to try work this out, but not sure where I am going wrong.

Code: Select all

var subtotal= 0;

function calculateTotals(){
    var rows=fd.relatedItems(0).data('ctx').ListData.Row;
    rows.forEach(function(item){
        subtotal += parseFloat(item['[b]Field_I_Am_Totalling[/b].']);
    });
    console.log("[b]Field_To_Update_With_Total[/b] " +subtotal);
}

calculateTotals();

fd.onsubmit(function(){
    fd.field('[b]Field_To_Update_With_Total[/b]').value(subtotal);
});

Please could you kindly just check if I am on the right track with the fields I am updating?

Thank you.

Edit. I see it doesnt show the bold text, but i am looking for the values for Field_I_Am_Totalling and Field_To_Update_With_Total

[Nikita Kurguzov]

Dear Wikus,
The code looks more or less correct, please, check the browser's console for errors. I suspect that the Internal Names of the fields might be wrong. Also, remember that this code doesn't auto update the subtotal - it only calculates it when you save the form.

To check the InternalNames of the fields, you can go to List Settings, select the appropriate column and check its URL:

InternalNameColumn.png (17.04 KiB) Viewed 4349 times

P.S. Don't forget to run the actual calculation once again before saving the form:

Code: Select all

var subtotal= 0;

function calculateTotals(){
    var rows=fd.relatedItems(0).data('ctx').ListData.Row;
    rows.forEach(function(item){
        subtotal += parseFloat(item['Field_I_Am_Totalling']);
    });
    console.log("Field_To_Update_With_Total " +subtotal);
}

calculateTotals();

fd.onsubmit(function(){
    //add this here:
    calculateTotals();
    fd.field('Field_To_Update_With_Total').value(subtotal);
});

[Katy]

I am trying to follow this instruction and i am always getting the error that the field is not a number (NaN), but it is (they all are) i've rechecked it and even created new one... console gives the same error...

[mnikitina]

Hello Katy,

Could you please share the screenshot of the error and the code you are using, so I could troubleshoot the issue.

Thank you!