Format Calculation Field w/ different Data Types

[jason25]

Hello,

I have a calculation field that incorportates both Text and Numbers. I would like to format the numbers to have thousands seperator, but the calculation result has to be Text; given that, is there anyway to format the numeric values within the calculation? I tried to format the field in the Layout view by clicking on the merge field and then going Format->Number, but it didn't do anything (which makes sense since the result is being formatted as Text). Is there a function that I can use line by line within the calculation?

Thank you for your assistance.

Regards,

Jason

[jason25]

Hello,

I have a calculation field that incorportates both Text and Numbers. I would like to format the numbers to have thousands seperator, but the calculation result has to be Text; given that, is there anyway to format the numeric values within the calculation? I tried to format the field in the Layout view by clicking on the merge field and then going Format->Number, but it didn't do anything (which makes sense since the result is being formatted as Text). Is there a function that I can use line by line within the calculation?

Thank you for your assistance.

Regards,

Jason

[Brian C]

The only way that I know of is to create a calculation to parse the number and insert the commas.

I am sure there is a cleaner and shorter way to do this, but this still works:

If the field name is: testnum

And the number is: 1256743.20

And the desired result is: 1,256,743.20

USE:

// Millions

if(

length(int(testnum)) > 9,

right(left(testnum, length(int(testnum)) - 6),3),

left(testnum, length(int(testnum)) - 6)

)

&

// Millions Comma

if(length(int(testnum)) >6 , "," , "")

&

// Thousands

if(

length(int(testnum)) > 6,

right(left(testnum, length(int(testnum)) - 3),3),

left(testnum, length(int(testnum)) - 3)

)

&

// Thousands Comma

if(length(int(testnum)) >3 , "," , "")

&

// Hundreds

right(int(testnum),3)

&

// Cents

case(

length(testnum - int(testnum)) = 3, (testnum - int(testnum)) ,

length(testnum - int(testnum)) = 2, (testnum - int(testnum)) &

[Ugo DI LUCA]

Here's one I adapted from a former post here on the Forums to handle different formatting according to the country specs.

May be it may help.

PriceFormat.fp7.zip

[comment]

What have you done to it? It breaks at 10 millions and at 100 millions.

(and you are doing a double Abs() function.)

[Ugo DI LUCA]

I don't know...

Will have to check this back.

[comment]

Try this:

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 ) )

)

[jason25]

This worked, thanks! I'm just going to insert the commas in the Grand Total because it would be quite tedious to stick that calculation for each of the line items. Thanks again!

Regards,

Jason

[Ugo DI LUCA]

Thanks.

I messed with the Abs( ) part in my calc. However, what was working with FM5 isn't anymore here.

Yours does the job, surely !

[comment]

Actually, not. I had a problem with e.g. -0.001. I have fixed it and edited the previous post.

[Brian C]

Ack! I accidently omitted the line for displaying the negative symbol. I probably should have just copied one of my old calcs that I use instead of attempting to make one on the fly. It would have been a lot more complete, though a bit longer as my skills have increased as more time passes.

No matter! Comment has done a far superior job in any case. As I suspected, there is a far better and shorter way to accomplish parsing a number to insert commas.

I will be making use of this in my conversions for certain.

Thanks Comment!

[comment]

The thanks should go to DJ (Djukic Goran) for coming up with the idea. I only cleaned it up a bit for ver.7.