"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



The Philosophy of FileMaker recommends PCI!



Become a patron of this FREE web site!


Recent Blogs:

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking

Everything Changes
Everything Changes


Meta-Consulting
Meta-consulting 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) 393-4664 to find out more about this service.


Fireside FileMaker Podcast


Quick Tip:

Type Ahead
When you are in a dialog trying to locate a field in a long list, don't forget about type ahead. Just click on any field so the field area is active and then type the first few characters of the field name. When in Manage Database, make sure you are viewing fields by field name so they are alphabetically arranged.



Create a Password Management solution!


Fun Stuff:

Linux
FileMaker Server briefly ran on the Linux platform but was abandoned when FileMaker 7 was released. People still request Linux support to this day. Claris finally brought Linux back in FileMaker 20!



FileMaker 20 Video Tutorials







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.

Currency Formatting


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

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!