Formatting Number Fields

[Genx]

hey just a quick question, i want to get the number from a number field that may be in the format $299,000 or 299000 or 299,000 etc etc.. and put it in a hard format of $299,000 within a calc field, what number formatting functions if any would i use to do this?

cheers for help,

~genx

[Genx]

If no-one is going to reply i'm going to use a work around and i know no one likes it when i do that hehe...

anyway, im thinking of just using a calc

Let([Price = GetAsNumber(PriceField); PL = Length(GetAsNumber(PriceField)] ;

Case(PL <= 3 ; "$" & Price ;

PL > 3 And PL <= 6 ; "$" & Left(Price ; PL-3) & "," & Right(Price;3) ;

PL > 6 And PL <= 9 ; "$" & Left(Price ; PL-6) & "," & Left(Right(Price ; 6); 3) & "," & Right(Price;3) ;

"Invalid Price" )

)

[si469]

Not sure if this is what you mean but select the number field that you want in Layout & then 'Number' under 'Format'.

Click the 'Format as decimal' button & tick fixed number decimal digits & key in '2'.

Click 'Use notation' with leading currency sign & choose '$' sign.

Then use the thousands separator a bit further down.

So a calculated result of eg 2987.46669 will display as $2,987.47.

Hope this helps

[Genx]

... No, i need to hard format it with something like the textstyleadd function. Thanks anyway

Genx

[LaRetta]

The best one I've seen is by Comment:

Let ( [

// USER DEFINED:

input = numberfield ;

precision = 2 ;

currency = "$" ;

separator = "," ;

decPoint = "." ;

// DO NOT MODIFY FROM THIS POINT ON

inputRound = Round ( input ; precision ) ;

Amt = Abs (inputRound ) ;

Dollars = Int ( Amt )

] ;

// PREFIX

Case ( inputRound < 0 ; "-" )

& currency

&

// BILLIONS

Choose ( Dollars < 10 ^ 9 ; Right ( Div ( Dollars ; 10 ^ 9 ) ; 3 ) & separator )

&

// MILLIONS

Choose ( Dollars < 10 ^ 6 ; Right ( Div ( Dollars ; 10 ^ 6 ) ; 3 ) & separator )

&

// THOUSANDS

Choose ( Dollars < 10 ^ 3 ; Right ( Div ( Dollars ; 10 ^ 3 ) ; 3 ) & separator )

&

// UNITS

Right ( Dollars ; 3 )

&

// FRACTION

Choose ( precision < 1 ; decPoint

&

Right ( 10 ^ precision & Amt * ( 10 ^ precision ) ; precision ) )

)

Sorry I can't refer you to the post which generated it. This is the most flexible because you can define your own formats.

LaRetta :wink2:

[comment]

The thread is here

http://www.fmforums.com/forum/showtopic.php?tid/152750

and it's important, because it gives credit to DJ as the original author.

[Genx]

...Why are my calculations so ******* stupid looking compared to other peoples hehe.

Cheers guys, its perfect

~Genx

[Genx]

Finally got around to implementing it, works a charm.

Cheers,

~Genx