You can go about it they way you are, its definitely the complicated way.
Why don't you place "M" in the field name, ie Price $'M, and in the field change the data type to numeric and use Number format on the layout object properties to set decimal places and insert $.
If I change the data type to numeric then the field returns a blank value when the result is "No Holding." I need to be able to return text or numbers in the field.
I'm not sure which is more complicated, yours or mine, and I'm sure there's a slicker Right,Left, Filter...etc. function nest, but another approach would be:
Make a second field for "No holding" which is calculated text as:
If (Field1 = "" ; "No Holding" ; "")
and place it over the field and "$" and "M" labels suggested previously.
Conditionally format the fill color to fill with the background color if "No Holding" should display (visually erase the labels).
Now when there's a value, "No Holding" is invisible. But when there's no value, "No Holding" covers the labels and the field.
Just thinking out loud...I'm sure there's a more direct way but wanted to get the juices flowin'...
Adapting an expression from: http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=25224&query.id=7595#M25224
int ( NumberField ) & If ( Mod ( NumberField; 1 ) ; Left ( Mod ( NumberField; 1 ) & "0"; 3 ); ".00" )
I may give that a try once my access to the database is restored (maintenance). However, I'm considering retrying Ninja's suggestion of overlaying fields, something I had attempted earlier but could not successfully implement. The benefit to that method is that then I can use the resulting numeric values in other calculations like summary fields (which I would like to be able to do).
With the calculation I posted, you can keep your unmodified value in NumberField and your formatted version in a second calculation field. That enables you to still have the original value for use in calculations and avoids the complexity of maintaining a layout with layered fields.
As I look at it, Phil's approach gives the all of the function without the maintenance hassle of stacked fields. I knew a nest of functions would be slicker...
Nice Job, Phil.