FM - Formatting Merge Fields

Posted on 2014-08-18
Last Modified: 2014-08-25
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.
Question by:rvfowler2
    LVL 2

    Author Comment

    Put the text into a field and formatted it that way, but is there a way to format the merge field?
    LVL 9

    Expert Comment

    by:Lieven Embrechts
    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.
    LVL 24

    Accepted Solution

    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

    LVL 2

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now