"No Holding Back FileMaker Blogging"

Navigation:

Support this site by clicking on a sponsor below or becoming a patron!
FileMaker 20 Video Tutorials
Become a patron of this FREE web site!


Recent Blogs:

Gathering Portals

Multiple Choice Picker

Popups and Pickers

Window Locking

Everything Changes


MetaConsulting
Metaconsulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 3934664 to find out more about this service.

$10.00 Beginner Video Training

Quick Tip:

Set Field Peculiarities Maybe everyone else knew this, but I just discovered it: If you don't specify a target field with Set Field then it will set the calculation to the currently selected field. I always knew this to be true with Insert Calculated Result, but I had just assumed that Set Field had to have a field reference to work. 

FileMaker 20 Video Tutorials

Fun Stuff:

Dull Boy FileMaker files were stored in clear text prior to FileMaker 7 so, if you opened a FileMaker file in a text editor, you could see the phrase "All work and no play makes Jack a dull boy" in the header repeated over and over. No, it's not a virus! My understanding is the developers just needed to take up some space in the header and that's what they came up with. Today, modern FileMaker files are stored in Unicode so I'm not sure if the phrase is still there. 

Tips & Tricks Videos

RSS Feed



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*/



