Brian Dunning's FileMaker Custom Functions

FormatNumberAsText ( number ; currency ; separator ; decimal ; precision )

Number to text, padded/rounded to a specified number of decimal places, user specified decimal and thousands separators, optionally displayed currency symbol

  Average rating: 3.9 (60 votes) Log in to vote

Geoffrey Gerhard   Geoffrey Gerhard
Creative Solutions Incorporated
http://www.creativesltns.com

Share on Facebook Share on Twitter

  Sample input:
FormatNumberAsText ( 12345678901234567.1234 ; "$" ; "," ; "." ; 2 )
==========
FormatNumberAsText ( 1234567.1234 ; "€" ; " " ; "," ; 2 )
  Sample output:
$12,345,678,901,234,567.12
==========
€1 234 567,12

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

This function formats numbers for use in text strings or calculations with a text result. It's especially useful when there are multiple numbers in a text string and at least one must be displayed in a different format than the others. It works on numbers of any length, and produces its result without recursion.

Separator and Currency parameters are optional.
Decimal parameter defaults to "." or "," (based on localization) unless rounding is <= 0, in which case it's omitted.
Precision defaults to 2 if parameter is not a digit or is unspecified.

UPDATED 11.20.2009
Added " + 0" to declaration of "n" variable to deal with cases where the number parameter is null or empty.

UPDTED 09.10.2015
Fixed a bug that resulted in malformed numbers ( like "€10..05,05" ) in some European FMP 12 (and higher) files.
Modified Decimal to use the correct integer/fraction separator [ "." or "," ] for the file's region when the decimal parameter is null.

 

Comments

TnIan   TnIan, Doofusburg
May 4, 2011
Awesome! That's exactly what I needed. Thanks!
 
Chris   Chris, Temecula
Jan 3, 2012
I am using your wonderful calculation but made a mistake somewhere. When i enter an amount i get a number of zeros after the desired amount:

$175.2300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Any way you can tell me where i messed up?

Let ( [
t = Int ( GetAsNumber ( amount_paid ) ) ;
prec = Case ( Length ( t ) ; t ; 2 ) ;
dec = Case ( Length ( "." ) ; "." ; "." ) ;
n = Round ( amount_paid + 0 ; prec ) ;
sgn = Case ( n < 0 ; "-" ) ;
n = Abs ( n );
x = NumToJText ( n ; 1 ; 0 ) ;
y = NumToJText ( n * 10^prec ; 0 ; 0 ) ;
z = Position ( x ; "." ; 1 ; 1 )
] ;

sgn & "$" &
Substitute ( Case ( z = 1 ; 0 ; z ; Left ( x ; z - 1 ) ; x ) ; [ "," ; "." ] )
&
Case (
prec > 0 ;
dec & Right ( 10^prec & y ; prec )
)

)

Thanks
 
Geoffrey Gerhard   Geoffrey Gerhard, Matthews, NC
Jan 5, 2012
Chris:

The value of "t" is the precision, so you're rounding to a number of decimal places equal to the integer value of the number you're formatting.


Geoff
 
Al   Al, Valais, Switzerland
Aug 24, 2012
Fabulous, thank you. Just what I've been looking for.

In cases where my number field is empty I'd like to be able to strip out the currency symbol in the output, but I can't work out how to amend the calc to do this.

Eg
*/
Let ( [
t = Int ( GetAsNumber ( 2) ) ;
prec = Case ( Length ( t ) ; t ; 2 ) ;
dec = Case ( Length ( "." ) ; "." ; "." ) ;
n = Round ( Flights Total + 0 ; prec ) ;
sgn = Case ( n < 0 ; "-" ) ;
n = Abs ( n );
x = NumToJText ( n ; 1 ; 0 ) ;
y = NumToJText ( n * 10^prec ; 0 ; 0 ) ;
z = Position ( x ; "." ; 1 ; 1 )
] ;


sgn & Currency &
Substitute ( Case ( z = 1 ; 0 ; z ; Left ( x ; z - 1 ) ; x ) ; [ "," ; "," ] )
&
Case (
Flights Total ≤ 0 ; "";
prec > 0 ;
dec & Right ( 10^prec & y ; prec )
)

)

Any ideas much appreciated.

Thanks million
 
Geoffrey Gerhard   Geoffrey Gerhard, Matthews NC
Aug 24, 2012
Changing the line...

sgn & Currency &

...to...

sgn & Case ( Length ( number ) ; Currency ) &

...should do it.
 
Al   Al, Valais, Switzerland
Aug 25, 2012
Yep, that's done it. Thanks so much for this, I've been trying to create this for, well, years! I finally have a multi-currency invoice system that only shows items with a value.

Hugely appreciated!
 
Roland   Roland, Berlin
Mar 10, 2014
Hiho over there,

tried this function but found out, that it doesn't work with the language systems where the Standard decimal is "," and the thousand sep is "."
E.g. if I have 654,21 EUR it converts to EUR654..21,21

Too weird for me to find a soultion, please help...

Thanks, Roland
 
Geoffrey Gerhard   Geoffrey Gerhard, Matthews, NC
Mar 10, 2014
It has worked for others using the notation you described, so i wonder what's different in your use case.... If you're willing to put together a sample file, I'd be glad to look at it--even if just to satisfy my own curiosity. Just two fields would be needed--a number field for the raw input and a calc field that shows what values (if any) you've assigned for each parameter.

The link to my web site on the CF page shows my email address.
 
Barney   Barney, Cornwall
May 5, 2014
Very handy function. Could it work with a repeating text field (i.e. the output of an ExecuteSQL call)?

Thanks,
Barney.
 
Nihm   Nihm, NY, NY
Jul 25, 2014
Thanks!
 
Chris   Chris, London
Dec 18, 2014
Works a treat. Thanks Geoffrey.
 
Brian   Brian, Eden Prairie, MN, USA
Mar 25, 2015
Geoffrey,

Thank you! Thank you!

Great custom function! My headache is diminishing!
 
Ernst   Ernst, Utrecht / The Netherlands
Sep 7, 2015
Hi !

Thanks for sharing, but unfortunately I ran into the same problem as Roland from Berlin;

I'll put an example together and mail it.

Best,
Ernst
info <at> clineco.nl
 
Mike   Mike, San Francisco
Dec 7, 2015
Thank you!
 
Robert   Robert, Caracas, Venezuela
Dec 14, 2015
Gracias...
 
Pep Espunyes   Pep Espunyes, Catalunya
Mar 2, 2016
Great!
 
Michelle Preston   Michelle Preston, Mill Bay BC
Mar 21, 2016
Thanks!
 
Joe   Joe, Newport Beach, CA
Apr 22, 2016
Thank you so much! This is a great and necessary function. Any idea why FileMaker hasn't provided this as a standard feature?
 
Ian   Ian, Melbourne Australia
Apr 27, 2016
Much appreciated. Just what I needed to correctly format currency amount totals within a text calculation field.
 
John   John
Jun 30, 2016
This function saved the day!!! Shame on filemaker for not including it.
 
Seamus Berkeley   Seamus Berkeley, Taos
Oct 17, 2016
Thanks so much for this function—works!
 
Luis Sardinha   Luis Sardinha, Gloucester, UK
Jul 14, 2017
Very nice custom function. Very useful. Works like a charm.
Cheers!
 
Alistair Hay   Alistair Hay, Harrogate, UK
Apr 10, 2018
Nice one.

Thanks very much
 
Chris   Chris, Whitianga, NZ
Apr 28, 2018
Brilliant! Many thanks.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.