|
|
|
|
Currency Formatting
|
Level: Intermediate
Version: FileMaker 21
Category: Design
Thursday, July 18, 2024 |
|
|
|
|
It's pretty easy to format a number field as currency, especially if it's a standard field. Once you enter the realm of merge fields, formatting a number can sometimes become difficult when multiple merge values need to display in a different format. For example, you might need a single text block to display one number field as it was entered and another as currency. This article will discuss several ways to assign the appropriate formatting to multiple number fields in a single text block.
The Example
Let's say you are creating a legal document that outputs with static and variable data. The static text being the information that's the same for everyone and the variable text being FileMaker merge fields. Legal documents can get pretty lengthy, so you are bound to have currency, decimal and "as entered" variable number fields. The problem is, a single text block can only have one number, date or time format applied. Either all your number fields are currency or decimal or "as entered".
Simplest Solution?
The simplest solution is to use multiple text blocks but this doesn't always work out well with complicated text blocks. For instance, what if the two differently formatted numbers can't be split by paragraph? No amount of sliding is going to make those two paragraphs reconnect in a seamless manner.
Even if it is easy to split the text blocks, you still have to deal with sliding objects. It's not that sliding objects are hard to setup, it's getting the spacing in the document to look uniform. It can be a difficult process, especially if you have a client that likes to make a lot of changes. Still, this approach can be the easiest and should be used when it actually makes the following calculation method more of a hassle.
Calculated Formatting
I always have a custom function in my database solutions to combat currency formatting issues. I started using currency calculations way back in the FileMaker Pro 3.0 days with the following formula:
If(
IsEmpty(number);
"";
"$" & If(number >= 1000; Int (number/1000 ) & ","; "") & Right(Int(number); 3) & Left(number - Int(number) + .001; 3)
)
The first thing you'll see is the IF statement. While this formula works perfectly fine for numbers up to 999,999.99, the If function is legacy code, existing for simple compatibility. Today we use the Case statement for speed and simplicity. See an article I wrote previously titled Logically Speaking for more information about why Case is better than If. In fact, this formula is still being published on the Claris web site:
Formatting a number as currency for use in a text field
I've updated the formula with simple substitution of Case for If (including the removal of the default result of blank) and the addition of the Let function:
Let(
@Number = number;
Case(
not IsEmpty(@Number);
"$" & Case(@Number >= 1000; Int (@Number/1000 ) & ",") & Right(Int(@Number); 3) & Left(@Number - Int(@Number) + .001; 3)
)
)
This formula is very easy to understand, using basic division to accomplish the task of formatting a number in US Currency up to 999,999.99. Go to a million and it fails. If your numbers are less than a million, you only need US formatting and you want to easily understand the formula then this calculation works great!
Custom Function
I recommend using currency formatting formulas in custom functions so you can centralize it. If any change is needed, it's done in one place and flows out to all the calculations where the custom function is referenced. If you're only using the formula once then there's probably no need for a custom function. But... beware. FileMaker solutions have a way becoming more complicated so best to plan for the future.
The next formula is quite a bit more complex but handles any length number and any currency type with parameters for separator, decimal, precision and more. This is the formula I currently use. It's tried and true so it's unlikely it will need to be modified in the future. All customizations can be handled when calling the custom function where it is housed.
Let ( [
t = Int (GetAsNumber(Precision )) ;
prec = Case (Length(t); t; 2) ;
dec = Case(Length(Decimal); decimal; Filter(1/2; ".,"));
n = Round(Number + 0 ; prec ) ;
sgn = Case(n < 0; "-") ;
n = Abs(n);
x = NumToJText(Int(n); 1; 0) ;
y = NumToJText(n * 10^prec; 0; 0)
] ;
sgn & currency
& Substitute(x; ","; separator)
& Case (
prec > 0;
dec & Right(10^prec & y; prec)
)
)
The custom function is called using a function named NumberFormat:
NumberFormat(number; "$"; ","; "."; 2)
I'm not going to spend a ton of time going over the details of this formula. Instead, I'll focus in on the NumToJText function. It's defined as "Converts arabic numerals to Japanese text". In other words, the definition means nothing for this article, lol. It has one nice little feature which places separators every three characters. For instance, if we have the number 123456789.00, the formula will be...
NumToJText(number; 1; 0)
... to convert it to...
123,456,789.99
Doing this with the While function or custom function recursion is a pain in the ass. I know, I've done it before. This formula just makes life easy. Now all we have to do is add a dollar sign. In other words, if you don't need the flexibility of different currency, separators, decimals and precision, the following formula will suffice:
"$" & NumToJText(number; 1; 0)
In other words, if all you are doing is adding commas and dollar signs, the formula above is simple and efficient. No need to incorporate a complex formula and only use a fraction of it's abilities.
Bonus
Just in case you're wondering about the While loop version, just want to see how it's done or maybe you could even modify this solution for some other use. For whatever it's worth, here's the While version:While(
[@Number = number;
@Int = Int(@Number);
@Remainder = Case(Mod(@Number; 1) = 0; ".00"; Mod(@Number; 1));
@Result = ""];
@Int > 0;
[@Result = Right(@Int; 3) & Case(not IsEmpty(@Result); ",") & @Result;
@Int = Left(@Int; Length(@Int) - 3)];
"$" & @Result & @Remainder
) I guess the real reason why I offer this convoluted solution is to remind folks to always choose the simplest solution for the job. There are many tools in FileMaker and when used properly can create a straightforward and efficient solution.
Let me Know
This solution was designed for complex text blocks that contain more than one formatted number merge field. If you have a different solution where you use calculated currency formatting, please let me know in the comments below.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
Ralph Learmont
|
07/28/2024
|
|
|
Here's a custom function I came up with
Syntax: Commas_Included (Number)
--------------------
Let ( [
N = Abs ( Number ) ;
Mantissa = Abs ( N ) - Int ( N ) ;
PaddedNumber = " " & Int ( N ) ;
ChoppedNumber = Right ( PaddedNumber ; 12 ) ;
String = Replace ( Replace ( Replace ( ChoppedNumber ; 10 ; 0 ; "," ) ; 7 ; 0 ; "," ) ; 4 ; 0 ; "," ) ] ;
Case ( N > 10^12 ; "Hit range limit" ;
If ( Number < 0 ; "-" ) & Trim ( Substitute ( String ; " ," ; "" )) & If ( Mantissa ; Mantissa )))
/*A non iterative way of inserting commas into a number.
Handles numbers up to 10^12, positive or negative.
Handles decimals of any length.
By Ralph Learmont 25th July 2024*/
/*Note: Later realised Japanese function
NumToJText does the same thing*/
|
|
|
|
