Calculate related item Not a Number error

[TWendt]

Hi all,

i have an related item in a form, where the costs for the training is calculated. Then all costs should be added together in the "Cost Training" field.
With this script:

//Calculate Kosten Training
var total = 0;
var rows = fd.relatedItems(7).data('ctx').ListData.Row;
rows.forEach(function(item) {
total += parseFloat(item['Kosten.'])
total_overall += parseFloat(item['Kosten.'])
});
fd.field('Kosten_x0020_Training').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}));

But in the field 'Kosten_x0020_Training only NaN is displayed. When i use a normal text field the script works correctly.

Please help

Best wishes
Tom

[mnikitina]

Hello TWendt,

What is the type of the Kosten_x0020_Training field?

[TWendt]

Hi,

i think its a problem with the related item list. See screenshot. When I use a normal text field in the related item, the script works.

Best wishes
Tom

[mnikitina]

Hello TWendt

You need to remove € sign like this:

Code: Select all

//Calculate Kosten Training
var total = 0;
var rows = fd.relatedItems(7).data('ctx').ListData.Row;
rows.forEach(function(item) {
total += parseFloat(item['Kosten.'])
total_overall += parseFloat(item['Kosten.'].replace(/€/g, ''))
});

[TWendt]

Hi,

when I use your script, the field is empty. Here is my complete script:

Code: Select all

var total_overall = 0;
var total_savings = 0;

//Calculate Hardware
var total = 0;
var rows = fd.relatedItems(3).data('ctx').ListData.Row;
rows.forEach(function(item) {
    total += parseFloat(item['Kosten.'])
	total_overall += parseFloat(item['Kosten.'])
});
fd.field('Kosten_x0020_Hardware_x0020__x00').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//Calculate Consulting
var total = 0;
var rows = fd.relatedItems(4).data('ctx').ListData.Row;
rows.forEach(function(item) {
    total += parseFloat(item['Kosten.'])
	total_overall += parseFloat(item['Kosten.'])
});
fd.field('Kosten_x0020_Beratungsaufwand_x0').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//Calculate Maintenance / Service
var total = 0;
var rows = fd.relatedItems(5).data('ctx').ListData.Row;
rows.forEach(function(item) {
    total += parseFloat(item['Kosten.'])
	total_overall += parseFloat(item['Kosten.'])
});
fd.field('Kosten_x0020_Wartung_x0020__x002').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//Calculate Kosten interner IT Aufwand
var total = 0;
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 Kosten Training
var total = 0;
var rows = fd.relatedItems(7).data('ctx').ListData.Row;
rows.forEach(function(item) {
total += parseFloat(item['Kosten.'])
total_overall += parseFloat(item['Kosten.'].replace(/€/g, ''))
});
fd.field('Kosten_x0020_Training').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//OVERALL
fd.field('RAM_x002d_Cost_x002d_Total').value(Number(total_overall.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//Calculate Benefit
var total = 0;
var rows = fd.relatedItems(8).data('ctx').ListData.Row;
rows.forEach(function(item) {
    total += parseFloat(item['Wert.'])
	total_savings += parseFloat(item['Wert.'])
});
fd.field('Einsparungen_x0020__x0028_Gesamt').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

//OVERALL SAVINGS
fd.field('RAM_x002d_Savings_x002d_Total').value(Number(total_savings-total_overall).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

Best wishes
Tom

[Nikita Kurguzov]

Dear TWendt,
Hmm, rather curious that it works for some Related Items and doesn't work for others. I don't think that you need to remove the euro sign, but I think that you need to double check the Internal name of the Kosten field for the Related Items where it doesn't work.

Please, enter the following code into console when the form is opened (in full screen mode) and show us the expanded object:

Code: Select all

fd.relatedItems(6).data('ctx').ListData.Row[0];

Like this:

RelatedItemsConsole.png (52.12 KiB) Viewed 6865 times

[TWendt]

Hi Nikita,

I think its a problem with the calculated field in the related items list.

Calculate-Field.jpg (78.14 KiB) Viewed 6859 times

When I use a normal text field, everything its okay. Here is the screenshot from the error message.

Calculate-Field-error.jpg (63.83 KiB) Viewed 6859 times

Error message: The object does not support the "foreach" property or method.

Is it possible to use a calculated field in the related items list?

Best wishes
Tom

[Nikita Kurguzov]

Dear TWendt,
Oh, yes! Actually, I think we've both missed that it's a calculated field. I guess the German text confused me at first and I didn't notice the calculation, I was under impression that it's a currency field...

Calculated columns are storing their values differently:

RelatedItemsConsoleCalc.png (38.45 KiB) Viewed 6855 times

You can try code like this:

Code: Select all

total += parseFloat(item['Kosten.calculated'].replace('float;#',''));
total_overall += parseFloat(item['Kosten.calculated'].replace('float;#',''));

[TWendt]

Hi,

nothing changed. I made the changes that you send me.

//Calculate costs internal IT effort
var total = 0;
var rows = fd.relatedItems(6).data('ctx').ListData.Row;
rows.forEach(function(item) {
//total += parseFloat(item['Kosten.'])
//total_overall += parseFloat(item['Kosten.'])
total += parseFloat(item['Kosten.calculated'].replace('float;#',''));
total_overall += parseFloat(item['Kosten.calculated'].replace('float;#',''));
});
fd.field('Kosten_x0020_IT').value(Number(total.toFixed(2)).toLocaleString("de-DE", {style: "currency", currency: "EUR"}) + ' €');

What is wrong?

Best wishes
Tom

[Nikita Kurguzov]

Dear Tom,
I don't know - because I haven't seen the structure. Send me a screenshot with browser's console, where you've input:

Code: Select all

fd.relatedItems(6).data('ctx').ListData.Row[0];

And show the various fields, how they're stored, like on my screenshots. This will allow us to know what we're working with here, otherwise, it's good as an example, but completely blind to the real picture.