Exporting Currency with two decimal places

[David Maffucci]

Hello,

I have what I hope is a simple issue but I can not figure it out. I'm using FileMaker to create an email message. It works like a champ except that part of what's created is a line of text that includes a number that's formatted as currency in my layout but often comes out wrong in email. So, for example, if the number (which is called "AmountDue") is $50.50, then it exports as 50.5 and drops the last zero. How can I get it to format correctly and show the two digits?

Thank you!

DM

[comment]

22 minutes ago, David Maffucci said:

a line of text that includes a number

Are you exporting your records? Or are you creating an email message, using  a calculation that concatenates text and a Number field? If the latter, you will have to format the number yourself within the calculation - see, for example:
https://fmforums.com/topic/85476-custom-function-for-formatting-decimal-place/?do=findComment&comment=393396&_rid=72594

Or, if the field is present on the current layout, you can give it an object name and then use the GetLayoutObjectAttribute() function to get the formatted number.

 

 

[David Maffucci]

I was creating an email via script. Your link gave me what I needed! I'm using:

"$" & Int ( Amount ) & SerialIncrement ( ".00" ; 100 * Mod ( Amount ; 1 ) )

And it works! I'm not quite sure how that last part works but it does!

Thank you,

[comment]

13 minutes ago, David Maffucci said:

I'm not quite sure how that last part works

Mod ( Amount ; 1 ) returns the fractional part of Amount. You multiply it by 100 to get the number of cents as an integer. Then you use the SerialIncrement() function to format the result as a 2-digit string, with a leading zero where necessary.

 

[David Maffucci]

An answer AND an eduction! This is terrific! I'm going to play with the formula a bit so I am sure I understand it.

Again, many thanks!

[comment]

10 minutes ago, David Maffucci said:

I'm going to play with the formula a bit so I am sure I understand it.

That's something I would strongly encourage you (and anyone else reading this) to do.

Make sure you recognize the stated limitations of the given solution: the number cannot be negative, and must be rounded to 2 decimal places. Otherwise you will need a more complex formula.

 

[David Maffucci]

I'm glad I played, I made a change. I added a Round function in the Mod calculation. Without it, the formula was only right when the amount was rounded down. Using Round solved that problem.

[comment]

1 hour ago, David Maffucci said:

added a Round function in the Mod calculation

Uhm, I am not sure what exactly you did, but if the input amount's precision is more than 2 decimal places, then you need to round it for the entire calculation, not just for the Mod part:

Let ( r = Round ( Amount ; 2 ) ; "$" & Int ( r ) & SerialIncrement ( ".00" ; 100 * Mod ( r ; 1 ) ) )

Otherwise you may get an unexpected result when the input is say 2.996.

 

[David Maffucci]

Bingo, I didn't do the define variable bit but yours is cleaner. Nonetheless it works!