Calculation needed to format $ amount

[dschaal]

I need to know if there is a way to format a field using a calculation that would resemble the "format field" commands for money.

I need my calculated field to return: $123,456.00

My calculation right now only returns: $123456.00 (which leaves out the comma).

The formula that I have is:

"$" & [fieldname] & ".00"

Does anyone have a suggestion on how I would insert the comma in the proper place?

You might be wondering why I'm not using the field format. I need this calculated field to display in my web page. Web pages do not acknowledge FileMaker field formats.

Any help would be appreciated. Thanks! Donna

[slstrother]

This calc assumes that you will always have even dollars (.00) and it goes to 9 digits.

Case(

Length(fieldname)=4, "$"&Left(NumToText(fieldname),1)&","&Right(NumToText(fieldname),3)&".00",

Length(fieldname)=5, "$"&Left(NumToText(fieldname),2)&","&Right(NumToText(fieldname),3)&".00",

Length(fieldname)=6, "$"&Left(NumToText(fieldname),3)&","&Right(NumToText(fieldname),3)&".00",

Length(fieldname)=7, "$"&Left(NumToText(fieldname),1)&","&Middle(NumToText(fieldname),2,3)&","&Right(NumToText(fieldname),3)&".00",

Length(fieldname)=8, "$"&Left(NumToText(fieldname),2)&","&Middle(NumToText(fieldname),2,3)&","&Right(NumToText(fieldname),3)&".00",

Length(fieldname)=9, "$"&Left(NumToText(fieldname),3)&","&Middle(NumToText(fieldname),2,3)&","&Right(NumToText(fieldname),3)&".00",

"$"&NumToText(fieldname)&".00"

)

[RussBaker]

Donna,

This calc will work with both positive and negative numbers, including cents, for up to 12 digits left of the decimal (it will actually work up to 15 but the accuracy of the displayed cents may be out by 1c up there).

Assuming your numerical value of the cost is in a field called "Money_Number_Field", then the Text_Price field will equal...

If(Money_Number_Field<0,"-","") &

"$" &

If(Length(NumToText(Int(Abs(Money_Number_Field))))>12,Left(NumToText(Int(Abs(Money_Number_Field))),Length(NumToText(Int(Abs(Money_Number_Field))))-12)&",","") &

If(Length(NumToText(Int(Abs(Money_Number_Field))))>9,Left(Right(NumToText(Int(Abs(Money_Number_Field))),12),Length(Right(NumToText(Int(Abs(Money_Number_Field))),12))-9)&",","") &

If(Length(NumToText(Int(Abs(Money_Number_Field))))>6,Left(Right(NumToText(Int(Abs(Money_Number_Field))),9),Length(Right(NumToText(Int(Abs(Money_Number_Field))),9))-6)&",","") &

If(Length(NumToText(Int(Abs(Money_Number_Field))))>3,Left(Right(NumToText(Int(Abs(Money_Number_Field))),6),Length(Right(NumToText(Int(Abs(Money_Number_Field))),6))-3)&",","") &

If(Length(NumToText(Int(Abs(Money_Number_Field))))>0,Left(Right(NumToText(Int(Abs(Money_Number_Field))),3),Length(Right(NumToText(Int(Abs(Money_Number_Field))),3))-0),"") &

"." &

Right("00"&NumToText(Round((100*(Abs(Money_Number_Field) - Int(Abs(Money_Number_Field)))),0)),2)

BUT - if you can script your requirement, here's a tricky way.

Create a new blank layout (body part only) and place only the Money_Number_Field on it - NOTHING ELSE - not even the field name, with the number formatted to show $ commas and 2 decimal places. In your normal layout, have a text field called Text_Money. Now run this script.

Go To Layout [blank layout]

Copy Record

Go To Layout [normal layout]

Paste [select,"Text_Money"]

Text_Money will now contain the number as a text value, using the number formats applied in the number field on the blank layout.

If you want to use this method for all your records, you will need to create a script to loop through them.

[goostree]

I'm curious. Why do you need to do this with a calculation instead of the format field options?

[dschaal]

I was able to get the calculation to work. Thanks to all! As to why I needed to do the field this way.....I wanted the format to appear on my web page. The Web Companion does not translate field formats (at least as far as I know).

I wanted my money field to be formatted correctly when viewed from the web.

Thanks again to all! Donna

[Lee Smith]

Hi Russ,

Nice calculation. I agree with the scripted method, as it is the only method that doesn't break. The only breaking place on your calculation is rounding up to the next dollar. (i.e. .999 will round to .00) other than that, it works pretty good using my standard break-the-calculation numbers.

Lee