Brian Dunning's FileMaker Custom Functions

AutoCurrency ( number )

Auto Format Currency as you type

  Average rating: 4.6 (22 votes) Log in to vote

Jeep Watson   Jeep Watson
Uptown Press
http://www.uptownpress.com

Share on Facebook Share on Twitter

  Sample input:
123456
  Sample output:
$1,234.56

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

I use this CF to automatically format currency as the user types in a number field. The decimal point is automatically entered (the last 2 digits are the cents). It is used in conjunction with an OnObjectModify script trigger attached to the numeric field. The script called by the trigger consists of only one line:

AutoCurrency ( Filter ( table::field ; "0123456789" ) )

Note the filter to strip out everything except numbers.

In Layout Mode: Do NOT select entire contents on entry
Also set up the Data Formatting panel as follows:

Format as Currency
Fixed number of decimals = 2
Symbol = $
Decimal = .
Use thousands separator = ,

Editing an already populated field is a little tricky but could be done by highlighting and changing one digit at a time. Frankly the function is so fast that it is simpler to just re-enter the whole number or backspacing to the point where the change is needed and re-entering the remainder.

MANY thanks to Daniele Raybaudi for lots of brilliant tips over the years including the use of the NumToJText function as a formatting tool.

Here’s a dropbox link to a demo file: https://www.dropbox.com/s/hx51ny67n6fdlp3/AutoCurrency.fp7?dl=0

 

Comments

comment   comment, VR
Mar 6, 2015
So why not simply:

"$" & NumToJText ( number / 100 ; 1 ; 0 )

--
P.S. With all respect to Daniele, I believe the credit here belongs to Agnes Barouh.
 
comment   comment, VR
Mar 6, 2015
Note that you have a problem when the last entered digit is a zero (or two zeros). This could be solved by using:

NumToJText ( Div ( number ; 100 ) ; 1 ; 0 ) & SerialIncrement ( ".00" ; Mod ( number ; 100 ) )
 
Jeep Watson   Jeep Watson, Baltimore
Mar 8, 2015
Sorry Michael, no disrespect to Agnes, I simply ran across a comment by Danielle in http://www.briandunning.com/cf/1707 that reminded me of this functionality.

Thanks for the input. I had forgotten about the SerialIncrement function. Over the years I have always appreciated your ability to create a solution using the simplest, most concise methods. Years ago you helped me reduce a long, verbose calculation I was using to create the Sum of the Digits for a checksum to a single line using the complementary Div and Mod functions.

I will amend this function with the proper attributions. Thanks again for your contributions over the years.
 
Alejandro   Alejandro, Quito, Ecuador
Feb 22, 2016
People like you make my days so much better!

Thanks for sharing your work, I really appreciate this function.
 
Scott   Scott, El Paso
Aug 5, 2016
When I use this function with a calculation, It comes out as $3,705.55 instead of $370,555.00. Seems like there should be a work around, but I'm brand new to custom functions.

Thanks!
 
Jeep Watson   Jeep Watson, Baltimore, MD
Aug 5, 2016
Scott, El Paso

I reactivated the dropbox link above. Download the demo file and see if it helps.
 

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.