Newbie Calculation Question

[kevharts]

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

[Lee Smith]

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 December 12, 2005 by Guest
showed result

[comment]

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.

[Lee Smith]

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

[comment]

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.

[Lee Smith]

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

[LaRetta]

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:

[kevharts]

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