Help! Loss of number formatting

[gbrozak]

I am working on a database to generate scholarship award letters. I have the caluculations working correctly (i.e., award amount, number of semesters awarded, housing scholarship, etc.) and have the figures (via the numbers format) adding "$", "," and "." decimals.

This all works beautifully until I try to merge the resulting calculations into my award letter. Intead of getting "$16,000.00", I'm getting "16000". I notice when I click on the field, I see "16000" as well, but I cannot find a way to have FMP take the formatted number and merge it into the letter. If I try to place fields in the letter, the resulting appearance is not aligned, and would not be feasible to use.

I would greatly appreciate any help!

George

[Raybaudi]

Actually your number field contains only:

16000

even if you see it as: $16,000.00 (due to your formatting options).

So you have to make a new calculated field (result text) with the help of this custom function or this

[gbrozak]

Daniele,

THANKS for your reply. This makes sense, but I'm not sure how to make it work. I don't see a means of referencing my field (you said create a new calculation field with result text using of of those solutions). If I create that caluculation field, what is it looking for?

I want it to take the result of my first field ($5,000) and then do the calculation (for example, $5000.00 * 4 (number of semeters) to arrive at a FINALCALC. This is the field (FINALCALC) that needs to be merged in the letter.

Does that make sense?

George

[Raybaudi]

Hi George

don't merge the FINALCALC but merge this one (POSTFINALCALC ? ; this will work till $ 999,999,999,999.99):

------------------------------------------------

Let ( [

ThSep = "," ;

A = Int ( FINALCALC ) ;

L = Length ( A ) ;

D = Mod ( FINALCALC ; A );

R = If( D = 0; ".00";Left ( D & "0"; 3 ))

] ;

"$ " & Case (

L = 12 ; Left ( A ; 3 ) & ThSep & Middle ( A ; 4 ; 3 ) & ThSep & Middle ( A ; 7 ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 11 ; Left ( A ; 2 ) & ThSep & Middle ( A ; 3 ; 3 ) & ThSep & Middle ( A ; 6 ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 10 ; Left ( A ; 1 ) & ThSep & Middle ( A ; 2 ; 3 ) & ThSep & Middle ( A ; 5 ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 9 ; Left ( A ; 3 ) & ThSep & Middle (A ; 4 ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 8 ; Left ( A ; 2 ) & ThSep & Middle ( A ; 3 ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 7 ; Left ( A ; 1 ) & ThSep & Middle ( A ; 2 ; 3 ) & ThSep & Right ( A; 3 ) ;

L = 6 ; Left ( A ; 3 ) & ThSep & Right ( A ; 3 ) ;

L = 5 ; Left ( A ; 2 ) & ThSep & Right ( A ; 3 ) ;

L = 4 ; Left ( A ; 1 ) & ThSep & Right ( A ; 3 ) ;

A

) //end Case

& R

) //end Let

------------------------------------------------

[comment]

You can format merged fields in the same way like "regular" fields, with one exception - the formatting applies to the entire text object. For example, a text object containing:

"the amount of <>, times <> semesters, <> in total"

can be formatted to display EITHER as:

"the amount of $5,000.00, times $4.00 semesters, $20,000.00 in total"

OR as:

"the amount of 5000, times 4 semesters, 20000 in total"

IOW, all NUMBER FIELDS merged in the object take on the Number format applied to the object.

If you create a calculation field SemestersAsText (result is Text) = Semesters, and merge it into object, it will not be formatted as number.