AutoCurrency ( number )
Auto Format Currency as you type
Average rating: 4.6 (22 votes) Log in to vote
|
Jeep Watson Uptown Press http://www.uptownpress.com |
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, 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, 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, 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, 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, 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, Baltimore, MD Aug 5, 2016 |
|
| Scott, El Paso I reactivated the dropbox link above. Download the demo file and see if it helps. |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.