Numeric Field Formatted For Currency Used In A Text Formula Field Looses Formatting?

My current tightest formula is this one





var number Value = Round([currency number],0.01);

var text Decimals = "." & Right(ToText(Int($value * 100)),2);

var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));

var text Hundreds=Right(ToText(Int($Value)),3);

If($Value=0,"$0.00",

If($Value<0, "- ")

&

"$" & List(",",$Thousands,$Hundreds) & $Decimals)

Thanks again Mark ....

This looks like what I need but I'm having difficulty understanding how to utilize it. If I have a text formula field that chooses between two fields based on whether a box is checked, how does the above relate to my formula? Any help would be really appreciated!

In answer to AJ ...



You could do this

var number MyValue = IF([My checkbox]=true, [My value for true], [My value for false]);


var number Value = Round($MyValue,0.01);

var text Decimals = "." & Right(ToText(Int($value * 100)),2);

var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));

var text Hundreds=Right(ToText(Int($Value)),3);

If($Value=0,"$0.00",

If($Value<0, "- ")

&

"$" & List(",",$Thousands,$Hundreds) & $Decimals)

You would then replace the "My" fields with your own fields.

Awesome! Thanks for the help - I'll do that right now.

Incredible.  I thought every program had a format function!

Brad, out of curiosity, how would this be done in Excel?

I guess its this https://support.office.com/en-us/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

=text(+[number],"$#,###.##)

I did it with one formula one line in one cell. done.

=text(+[number],"$#,###.##")   Sorry, left off the closing quotation mark.

FYI - I was using this formula to display a discount (negative number) and it was dropping out the thousands place (i.e. instead of showing "-$1,500.00" it was showing "-$500".  The way the Thousands variable is set up, it kicks in if the number is greater than 1,000.  Well, it's also true if it's less than -1,000.  I added the Abs function to get it to work:

var text Thousands =
 If(Abs($Value)>=1000,ToText(Int(Abs($Value)/1000)));

Hey there! I have some currency fields that will include dollar amounts in the millions, requiring more than one comma ($3,000,000.00 for example). Anyway there is something I can add to the above formula to make this work for those values?

I believe you can simply use this formula at this point: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188