FM - Formatting Merge Fields

Trying to format my merge fields for currency, but right clicking on them does not help.  Am I going to have to create a separate field formatted for currency?  Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rvfowler2Author Commented:
Put the text into a field and formatted it that way, but is there a way to format the merge field?
Lieven EmbrechtsSenior IT ConsultantCommented:
think of merge fields as raw data, there is no formatting for raw data.
an extra calculation field can change it the way you want.
Will LovingPresidentCommented:
If you click on the Text block containing the merge fields and then set the "Number" settings in the Inspector palette (just as you would with a selected field) to either Decimal or Currency, then all number fields within that text block will format according to those settings. This also works for date fields.

However, if you have multiple number fields in a particular block of text, they will ALL format as currency. If you don't want that then you will need to create a calculation field that formats for the kind of currency you wish to display, symbol, whether the symbol comes before or after, commas, and the number of decimals. The best way to do this is to use one of the Currency Custom Functions available such as can be found here:   However, Custom Functions require FileMaker Advanced in order to be installed (just to install, you can use them in any copy of FM Pro).

If you (or a friend/colleague) don't have FileMaker Advanced to install a custom function, you can use the a calculation to generate a standard US style currency text string (or modify it for other currencies). This calculation may require modification to work with very large numbers or other situations like negative numbers.

FormattedPrice (Calculation, Text result)

If ( IsEmpty ( Price ) ; "" ; "$" & If ( Price > = 1000 ; Int ( Price / 1000 ) & "," ; "") & Right ( Int ( Price ) ; 3 ) & Left ( Price - Int ( Price ) + .001 ; 3 ) ) & " US"

Open in new window

See this FileMaker article for additional details:

You can also generate a string by substituting into the calculation actual values for the parameters that are specified within the Custom Function calculation, but it's a bit more complex.

Let ( 

    xNeg = Case(Amount < 0; 1);
    xAmt = Abs(Round ( Amount ; Case(ShowDecimal; 2; 0) )) ;
    xInt = Int ( xAmt ) ;
    xLen = Length ( xInt ) ;
    xDec = If ( xAmt <> xInt ; Left ( xAmt - xInt & "000" ; 3 ) ; ".00" );

    xFormat = CurrencyCode & Case(CurrencyCode <> ""; " ") & Case(xNeg; "-") & Lead &
                       Case (
                           xLen=9 ; Left(xInt;3) &","& Middle(xInt;4;3) &","& Right(xInt;3) ;
                           xLen=8 ; Left(xInt;2) &","& Middle(xInt;3;3) &","& Right(xInt;3) ;
                           xLen=7 ; Left(xInt;1) &","& Middle(xInt;2;3) &","& Right(xInt;3) ;
                           xLen=6 ; Left(xInt;3) &","& Right(xInt;3) ;
                           xLen=5 ; Left(xInt;2) &","& Right(xInt;3) ;
                           xLen=4 ; Left(xInt;1) &","& Right(xInt;3) ;
                       ) & Case(ShowDecimal; xDec) & Trail

] ;

    Case(RedNegative and Amount < 0; TextColor ( xFormat ; RGB ( 150 ; 0 ; 0 ) ); xFormat)


Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvfowler2Author Commented:
Thanks, this was really helpful.  As you say, I did have other fields that I did not want formatted as currency, so went ahead with creating a field to format the info.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.