• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 761
  • Last Modified:

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.
0
rvfowler2
Asked:
rvfowler2
  • 2
1 Solution
 
rvfowler2Author Commented:
Put the text into a field and formatted it that way, but is there a way to format the merge field?
0
 
Lieven EmbrechtsCommented:
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.
0
 
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: http://www.briandunning.com/cf/436   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:
http://help.filemaker.com/app/answers/detail/a_id/2938/

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) ;
                           xInt 
                       ) & Case(ShowDecimal; xDec) & Trail

] ;

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

)

Open in new window

0
 
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now