Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

Currency ( Amount, Code, Lead, Trail, ShowDecimal, RedNegative )

Format currency as text in many formats (inc. red negative)

  Average rating: 4.0 (41 votes) Log in to vote

Tony Tanevski - Show more from this author Hi-Voltage http://www.hi-voltage.com.au

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

// Currency (Amount, CurrencyCode, Lead, Trail, ShowDecimal, RedNegative ) // // This custom function takes the following parameters: // // Amount - The currency amount to display // CurrencyCode - (Optional) The currency code i.e. US, AU, GBP // Lead - The leading currency symbol i.e. $ // Trail - The trailing currency symbol i.e. $ // ShowDecimal - Rounding ... the number of decimals // RedNegative - 1 or 0, shows negative values in red. Let ( [ xNeg = Case(Amount < 0; 1); xAmt = Abs(Round ( Amount ; Case(ShowDecimal; 2; 0) )) ; xInt = Int ( xAmt ) ; xLen = Length ( xInt ) ; xDec = If ( xAmt <> xInt ; Left ( xAmt - xInt & "000" ; 3 ) ; ".00" ); xFormat = CurrencyCode & Case(CurrencyCode <> ""; " ") & Case(xNeg; "-") & Lead & Case ( xLen=9 ; Left(xInt;3) &","& Middle(xInt;4;3) &","& Right(xInt;3) ; xLen=8 ; Left(xInt;2) &","& Middle(xInt;3;3) &","& Right(xInt;3) ; xLen=7 ; Left(xInt;1) &","& Middle(xInt;2;3) &","& Right(xInt;3) ; xLen=6 ; Left(xInt;3) &","& Right(xInt;3) ; xLen=5 ; Left(xInt;2) &","& Right(xInt;3) ; xLen=4 ; Left(xInt;1) &","& Right(xInt;3) ; xInt ) & Case(ShowDecimal; xDec) & Trail ] ; Case(RedNegative and Amount < 0; TextColor ( xFormat ; RGB ( 150 ; 0 ; 0 ) ); xFormat) ) Click to copy

Format currency as text in many formats. Suitable for displaying currency for multiple countries. Option to display trailing characters. Option to display red negative amounts.

 

Comments

		tom, ShiftPoint Jul 31, 2009
With FileMaker 10, "Code" is no longer allowed as a parameter name since it is now the name of an internal function. A simple change of "Code" to something like "CodeLabel" everywhere in the function will suffice.

		Fergus Black, Peterborough UK Jan 29, 2010
Very many thanks - an excellent resource.

		Braxton, Sacramento Mar 29, 2010
This certainly beats: "$" & Int (Round (Number;2)) & "." & Right (Round (Number;2) * 100;2) Thank you very much!

		Steve Hearn, London ON Mar 11, 2011
Thanks for this. I added another parameter for thousands separator so it has the option of using one or not.

		Joshua Paul, Neo Code Sep 10, 2012
Love this function! Thanks Tony!!!

		Adam Glick, HelpDesk, Inc. Feb 29, 2020
great little function that solved a need in some custom text blocks I generate in a quoting solution for a client. 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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 21 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: