Post

4 followers Follow
0
Avatar

Change format currency in calculation European style

In a calculation field i want to change the format of numbers. I use the following:

"+"€ " + (@All of Toe te kennen budget (project)).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ".")

This works fine but only 1 thing i need more:

@ this moment the outcome is € 1.000.00 (see attached screenshots)

I need this outcome: € 1.000,00

The separator voor decimals is now a point instead of a comma.

Pleas advise in this matter.

KR

Mark

Functioneel Beheer

Please sign in to leave a comment.

15 comments

1
Avatar

Hi Mark,

instead of .toString() you can use .toFixed(), and instead of the ".", ",". 

"+"€ " + (@All of Toe te kennen budget (project)).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")

Rainer

Rainer Grabowski 1 vote
0
Avatar

Hi Rainer,

Thanks for your help. I've tried your suggested way and got error:

Syntax-error script: Unexpected token ILLEGAL

; see attachment.

What to do?

KR

Mark

Functioneel Beheer 0 votes
0
Avatar

And without +" @ the end i got

Script error: TypeError: undefined is not a function

 

Functioneel Beheer 0 votes
0
Avatar

Hi Mark, 

there are 2 errors: 

1. The error "Unexpected token ILLEGAL" in your first screenshot is caused by a missing quotation mark at the end (there seems to be only one quotation mark at the end, must be 2). If you add the second one you'll get the same notification as in screenshot 2 - caused by the main error in the code 

2. Main error: Sorry, my mistake.  Didn't look right at the numbers in your question. What kind of field are the fields where you pull the numbers from ( like : @All of Toe te kennen budget (project)) - I assume text fields?  And in which format are the numbers entered there? Like: 1,000 and 1,023,10? Are all numbers entered in the same way?

Rainer

Rainer Grabowski 0 votes
0
Avatar

Hallo Rainer,

Thanks a lot for helping me out with this puzzle ;-) 

Your question:

"What kind of field are the fields where you pull the numbers from ( like : @All of Toe te kennen budget (project)) - I assume text fields?  And in which format are the numbers entered there? Like: 1,000 and 1,023,10? Are all numbers entered in the same way?"

Answer:

It's a bit complex i think. I hope i can explain in to you. Al source values are currency fields. But for example the maximum tax is calculated in a calculation field by [currency field] x [number field @BTW-verrekenpercentage (%)]. See also the attached files/screenshots.

I hope it makes sense to you. 

KR

Mark

 

Field [@All of Aangevraagd bedrag] (requested projectbudget ex tax) = currency field

Field [@All of Toe te kennen budget (project)] (meaning projectbudget including tax) = a calculation field which is currency field [Aangevraagd bedrag] + calculation field [maximaal te betalen BTW]

Field [@All of Beschikbaar budget] (meaning: available budget) = a calculation field which is referenced/incoming from another app where it’s also a calculation field


Field in referenced app:

 

Functioneel Beheer 0 votes
0
Avatar

Which format have the results of the first two calculation fields and of the last one?  Like 1,000.23 or 1.000,23 or 1000.23? And which format have the results of the other 2 (with @sum of)? And are all fields number type?

Rainer Grabowski 0 votes
0
Avatar

 

 

 

Result of the first 2 fields:

Result of the first sum of field:

Result of the second sum of field:

Functioneel Beheer 0 votes
0
Avatar

I think al field are numbers

€ 100.000,00 is the result i need.

KR

Mark

Functioneel Beheer 0 votes
0
Avatar

In your field "Financien" use @sum of ... instead of @all of ... 
Then:

"+"€ " + (@Sum of Toe te kennen budget (project)).toFixed(2).replace(".",",").replace(/\B(?=(\d{3})+(?!\d))/g, ".")

The trick does .replace(".",","). If you need the decimals  in all your other calculation fields (whre only one number is in) just control the decimals via the field settings.

Rainer
rg@delos-consulting.com 

Rainer Grabowski 0 votes
0
Avatar

 

 

 

 

Hi Rainer,

 

 

It almost works! The only thing is that when the number is with 2 decimals (see amount Aangevraagd budget (project)) the next number is represented after the comma with 1 dot? See number 'Toe te kennen budget (project)'

 

Results source fields:

Functioneel Beheer 0 votes
0
Avatar

Hi Rainer,

 

I've copied your last code:

"+"€ " + (@All of Aangevraagd bedrag).toFixed(2).replace(".",",").replace(/\B(?=(\d{3})+(?!\d))/g, ".") = not working

"+"€ " + (@All of Aangevraagd bedrag).toString().replace(".",",").replace(/\B(?=(\d{3})+(?!\d))/g, ".") = working but after the comma not (see last calculation field)

Functioneel Beheer 0 votes
0
Avatar

Hallo Rainer,

It works!!!

I solved it...

Changed @all in @sum and it works ;-)))

Thank you very much for helping me out!

 

"+"€ " + (@All of Toe te kennen budget (project)).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",") = not working

"+"€ " + (@sum of Toe te kennen budget (project)).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",") = working
Functioneel Beheer 0 votes
0
Avatar

Hi, rather than start a whole new post, I wonder if you'd mind if i continue the conversation with another question? 

I'm trying to average out 3 prices to provide a final entry which might look like "$3,500.00"

I've managed to work out how to insert the "$" at the front of the calculation field (after a little jiggery pokery), but am not having any luck at all with inserting the comma in the correct space or the decimal places at the end.

So far I have the following:

"$" + (@Avg @Price1 + @Price2 + @Price3)

My returned result is: $3500

 

Any help greatly appreciated.

Steve 0 votes
0
Avatar

Hi Michelle,

this should get the right formatted output like $3,500.00: 

"$" + (@Avg @Price1 + @Price2 + @Price3).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")   

Rainer

Rainer Grabowski 0 votes