Skip to content
View in the app

A better way to browse. Learn more.

FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Format Calculation Field w/ different Data Types

Featured Replies

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

  • Author

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

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

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

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

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

I don't know... tongue.gif

Will have to check this back. mad.gif

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

)

  • Author

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

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 !

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

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!

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

Create an account or sign in to comment

Important Information

By using this site, you agree to our Terms of Use.

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.