Sum in realted item

Discussions about Forms Designer for SharePoint 2013 / 2016 and Office 365.
RMIC
Posts: 96
Joined: Sun May 10, 2015

02 May 2018

Hello,

I want to make the sum of a column with type of number, in a related item. This goes with:
var total = 0;
var rows = fd.relatedItems (0) .data ('ctx'). ListData.Row;
rows.forEach (function (item) {
     total + = parseFloat (item ['HardwareSelection_x003a_Price_x002'])
});
alert (total);


My first question:
Unfortunately, no decimal places are calculated. The column "Hardwareauswahl:Preis (Zahl)" should be calculated (theoretically 225.50). However, he only indicates 225. How can I include the decimal places?


My second question:
How can I calculate the sum of the "Hardwareauswahl:Preis (Zahl)" column of only certain elements in a related item. Namely only of elements in which the value "Genehmigt" is contained in the column "Genehmigt"?

Many Thanks!
Attachments
related_item.PNG
related_item.PNG (10.51 KiB) Viewed 2146 times

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

02 May 2018

Dear RMIC,
Not sure why does the calculation not work correctly. I've just tested it myself and all the decimals should be included correctly. Try to run the following code from the console, what will it show?

Code: Select all

var total = 0;
var rows = fd.relatedItems (0) .data ('ctx'). ListData.Row;
rows.forEach (function (item) {
    console.log( parseFloat(item['HardwareSelection_x003a_Price_x002']));
    console.log( parseFloat(item['HardwareSelection_x003a_Price_x002.']));
    total + = parseFloat(item['HardwareSelection_x003a_Price_x002']);
});
console.log("Total: " + total);
Please, send us a screenshot of the result.

As for your second question, it should be rather easy, you just need to add a condition for the Row, something like this:

Code: Select all

rows.forEach (function (item) {
    if(item['Genehmigt'] == 'Genehmigt'){
    	total + = parseFloat(item['HardwareSelection_x003a_Price_x002']);
    }
});
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

02 May 2018

Attached is the picture for the first question.

Your answer to the second question works perfectly. Thank you!
Attachments
related_item_console.PNG
related_item_console.PNG (14.06 KiB) Viewed 2135 times

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

02 May 2018

Dear RMIC,
Thank you for the screenshot. Can you try this code as well?

Code: Select all

var total = 0;
var rows = fd.relatedItems (0) .data ('ctx'). ListData.Row;
rows.forEach (function (item) {
    console.log(item['HardwareSelection_x003a_Price_x002']);
    total + = parseFloat(item['HardwareSelection_x003a_Price_x002']);
});
console.log("Total: " + total);
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

02 May 2018

Of course.
Attachments
related_item_console2.PNG
related_item_console2.PNG (6.26 KiB) Viewed 2131 times

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

02 May 2018

Dear RMIC,
Please, try the following code as well:

Code: Select all

var total = 0;
var rows = fd.relatedItems (0) .data ('ctx'). ListData.Row;
rows.forEach (function (item) {
    var price = parseFloat(item['HardwareSelection_x003a_Price_x002'].replace(',', '.'));
    console.log(price);
    total + = price;
});
console.log("Total: " + total);
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

03 May 2018

It is working. Perfect! Thank you!

I have still one question. Currently the result is "225.5". Is it possible that "225.50" will be issued?

User avatar
Nikita Kurguzov
Posts: 889
Joined: Mon Jul 03, 2017

03 May 2018

Dear RMIC,
Just use the following code before setting the field:

Code: Select all

total = total.toFixed(2);
Cheers

RMIC
Posts: 96
Joined: Sun May 10, 2015

03 May 2018

It works. Thanks for your help.
Fantastic JavaScript :)

RMIC
Posts: 96
Joined: Sun May 10, 2015

20 Dec 2018

Hello,

Now I want to calculate the total of a number column in a Quick Edit. This works well with the following code:

Code: Select all

var total = 0;
var rows = fd.relatedItems(0).data('ctx').ListData.Row;
rows.forEach(function(item) 
{
	total += parseFloat(item['BetragHauswaehrungNetto'].replace(',', '.'));
	alert("total: "+total);
});
total = total.toFixed(2);
total = total.replace('.', ',');
fd.field('Gross_Amount').value(total);
But I have two mistakes.
First mistake:
He calculates the number 1000.10 internally with "1". Which is clear because of the replace (',', '.').
As a total I receive 201.2.
How do I get the grand total of 1200.3?

Second mistake:
When I delete an element in the Quickedit, the deleted element is also used in the calculation. How can I prevent this?

Many thanks!

Best Regards,
RMIC
Attachments
calc_quick_edit.PNG
calc_quick_edit.PNG (48.45 KiB) Viewed 2009 times

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 14 guests