Calculate related item Not a Number error

Discussions about Forms Designer for SharePoint 2013 / 2016 and Office 365.
TWendt
Posts: 109
Joined: Mon Sep 08, 2014

14 Aug 2020

Hi Nikita,

here are the screenshots.
Calculate-Field-error.jpg
Calculate-Field-error.jpg (63.83 KiB) Viewed 11127 times
Calculate-Field-error.jpg
Calculate-Field-error.jpg (63.83 KiB) Viewed 11127 times
Best wishes and a nice weekend
Tom
Attachments
Calculate-Field-Script-error.jpg
Calculate-Field-Script-error.jpg (38.66 KiB) Viewed 11127 times

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

14 Aug 2020

Dear TWendt,
As I said, I don't need screenshots of the error or of the code - you can comment it out for now, it doesn't matter.

What I need, is for you to copy this text:

Code: Select all

fd.relatedItems(6).data('ctx').ListData.Row[0];
And open the form, open browser's console and paste it there, and press Enter.

Then, expand the object, so we can see the properties it has and how the information is stored in the column that interests you, like this:
RelatedItemsConsoleCalcAfter.png
RelatedItemsConsoleCalcAfter.png (47.52 KiB) Viewed 11125 times
Cheers

TWendt
Posts: 109
Joined: Mon Sep 08, 2014

14 Aug 2020

Hi Nikita,

sorry. I hope now its okay.
Calculate-Field-Script-error-01.jpg
Calculate-Field-Script-error-01.jpg (122.36 KiB) Viewed 11124 times
Best wishes
Tom

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

17 Aug 2020

Dear Tom,
Thank you for this! Now, please, try the following in console as well, see what it gives you:

Code: Select all

var currency = fd.relatedItems(6).data('ctx').ListData.Row[0].Kosten;
var number1 = parseFloat(currency.replace(/[^0-9,-]+/g,""));
var number2 = parseFloat(currency.replace(/[^0-9,-]+/g,"").replace(",","."));
console.log(number1 );
console.log(number2);
For this test to be more informative, you can also try changing Kosten in the first row of the table from 1.000,00 euro to 1.000,01 euro (or some other value with cents).
Cheers

TWendt
Posts: 109
Joined: Mon Sep 08, 2014

17 Aug 2020

Dear Nikita,

here is the modified script. Now the NaN error dows not occur.

//Calculate Kosten interner IT Aufwand
var total = 0;

var currency = fd.relatedItems(6).data('ctx').ListData.Row[0].Kosten;
var number1 = parseFloat(currency.replace(/[^0-9,-]+/g,""));
var number2 = parseFloat(currency.replace(/[^0-9,-]+/g,"").replace(",","."));
console.log(number1 );
console.log(number2);

//var rows = fd.relatedItems(6).data('ctx').ListData.Row;
rows.forEach(function(item) {
total += parseFloat(item['Kosten.'])
total_overall += parseFloat(item['Kosten.'])
});
fd.field('Kosten_x0020_IT').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');
Calculate-Field-Script-error-02.jpg
Calculate-Field-Script-error-02.jpg (17.62 KiB) Viewed 11121 times
But the 2000 € will not be added.

Best wishes
Tom

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

17 Aug 2020

Dear Tom,
I've previously asked to run the commands in console as well, not modify anything in the code yet. It's better to test everything first, and when we're certain that it will work - apply it to the code.

Right now, I'm still not certain that this will works, so I've asked you to modify value in the row from 1.000,00 to 1.000,01 and run the console commands like you did before:

Code: Select all

var currency = fd.relatedItems(6).data('ctx').ListData.Row[0].Kosten;
var number1 = parseFloat(currency.replace(/[^0-9,-]+/g,""));
var number2 = parseFloat(currency.replace(/[^0-9,-]+/g,"").replace(",","."));
console.log(number1);
console.log(number2);
So it looks like this, and we can check the result:
RelatedItemsConsoleCalcAfterConversion.png
RelatedItemsConsoleCalcAfterConversion.png (43.61 KiB) Viewed 11120 times
Cheers

TWendt
Posts: 109
Joined: Mon Sep 08, 2014

17 Aug 2020

Dear Nikita,

sorry my mistake.
Calculate-Field-query.jpg
Calculate-Field-query.jpg (50.93 KiB) Viewed 11120 times
Best wishes
Tom

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

17 Aug 2020

Dear Tom,
Okay, so I think you should be able to run the following code, please, try it*:

Code: Select all

//Calculate Kosten interner IT Aufwand
var total = 0;
var rows = fd.relatedItems(6).data('ctx').ListData.Row;
rows.forEach(function(item) {
   var currencyIT = item['Kosten'];
   var numberIT = parseFloat(currencyIT.replace(/[^0-9,-]+/g,"").replace(",","."));
   total += numberIT;
   total_overall += numberIT;
});
fd.field('Kosten_x0020_IT').value(parseFloat(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');
*code has been fixed after feedback
Cheers

TWendt
Posts: 109
Joined: Mon Sep 08, 2014

17 Aug 2020

Dear Nikita,

many thanks. It works after I add IT to the currency

var numberIT = parseFloat(currencyIT.replace(/[^0-9,-]+/g,"").replace(",","."));

Many thanks for your assistance. You are the best. :D

Best wishes
Tom

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

17 Aug 2020

Oh, yes, my bad! I will fix it in the previous post as well.

Glad to hear that it works. It's always best to investigate such issues via console first, and when you get to the working variant - implement it in the global code. Just allows to see the problems before they block all the progress.

Let us know if you need anything else - we'll be happy to help!
Cheers

Locked
  • Information
  • Who is online

    Users browsing this forum: No registered users and 4 guests