# Newbie Calculation Question

This topic is 5759 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

I have a problem trying to use a number that needs to be formatted as currency in a new calculated text field.

The new field I'm creating will be a Text field containing the values of the Account, Date, Amount, Memo.

The Amount is in a field which is defined as a number (i.e. 1000 or 10.64). In the new calculated field, I need to add the currency sign, the commas and the decimal points.

I can't figure out how to do that. Whan I create my calculation in the new field, it formats the field just as it is stored. My calc is: Donor & "|" & Date & " - " & Amt & " - " & Memo.

Any help would be appricaited.

Thanks - kevin

##### Share on other sites

Maybe something like this:

Donor & "|" & GetAsText ( Date) & " - " & "\$ " & GetAsText ( Amt ) & " - " & Memo

which would return

Jim|12/12/2005 - \$ 12.40 - this and that

HTH

Lee

Edited by Guest
showed result
##### Share on other sites

Try:

"\$" &

Case ( Div ( Amt ; 1000 ) ; Div ( Amt ; 1000 ) & "," )

&

Right ( Int ( Amt ) ; 3 )

& "." &

Right ( "00" & Amt * 100 ; 2 )

This assumes that Amt is a positive number, rounded to the nearest cent, and smaller than million.

##### Share on other sites

Ah shoot. I didn't test for more than 1000.

So, if the thousand separator is important, then the amended calculation would be:

Donor & "|" & GetAsText ( Date) & " - " & "\$ " &

Case ( Div ( Amt ; 1000 ) ; Div ( Amt ; 1000 ) & "," )

&

Right ( Int ( Amt ) ; 3 )

& "." &

Right ( "00" & Amt * 100 ; 2 )

& " - " & Memo

I was excited to see when FileMaker changed the TextToNumber (v6) to GetasText(Number) in (v7), that they also changed it so that the decimals show (provided they were entered in the number field). Too bad the thousand separators don't carry over and we can stop having to use a workaround case statement.

oh well, maybe in version 9 or 10.

Lee

##### Share on other sites

the decimals show (provided they were entered in the number field)

I find this a very confusing "feature". I would expect a NUMBER to ignore any redundant digits that the user have chosen to input. You certainly cannot rely on it, because it doesn't work when the number comes from a calculation.

##### Share on other sites

I didn't say it was perfect. In fact I even modified the calculation to include your Case statement because I didn't like the same things.

Lee

##### Share on other sites

Maybe - just maybe - a calc isn't even needed. If this is for display, merge fields would work a treat for you (and save adding another field to your field definitions, as well as saving a bit of resource).

If you use merge fields, each piece can be formatted as its specific type, ie, the date could be written out or the number could be selected and formatted as dollars etc. It's surprising how many times I've created a calculation when it's sole purpose was to display. :wink2:

##### Share on other sites

• Newbies

Thanks for your advice. Tomorrow when I am back in the office, I will give it another shot implementing your suggestions.

##### Share on other sites

This topic is 5759 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account