Forcing currency format on numbers in a 'text' field

[db_tragic]

Hi folks,

 

I have a field which is defined as a 'text' field as it will contain both numbers(currency) and text.

 

The field is defined by the following calculation:

Case(IsEmpty(_pk_InvoiceID); "(Not Invoiced)"; Paid - Subtotal)

Because this field is defined as a text field (to allow insertion of "(Not Invoiced)" string), I can't use the currency formatter in the Inspector. 

 

Any suggestions on how I can force a currency format on the numerical values in this field?

 

TIA

[Wim Decorte]

check out www.briandunning.com in the custom functions section. I'm sure there are few that can take a number and fomat it as currency text

[comment]

The easy way:

 

1. Redefine the field  as =

Case ( not IsEmpty ( _pk_InvoiceID ) ; Paid - Subtotal )

and make the result type a Number.

 

 

2. Place the text "(Not Invoiced)" on the layout and format it conditionally to have a font size of 500pt when =

not IsEmpty ( _pk_InvoiceID )

BTW, this is not only the easy way, but also the correct one: a field should not be "multi-tasking" like that. Just consider what if you ever wanted to summarize this field.

[db_tragic]

check out www.briandunning.com in the custom functions section. I'm sure there are few that can take a number and fomat it as currency text

 

Thanks for this(hadn't found this before) - yes there is a custom function for that there (plus a heap of others I'm going to trawl though now!)

 

The easy way:

 

1. Redefine the field  as =

Case ( not IsEmpty ( _pk_InvoiceID ) ; Paid - Subtotal )

and make the result type a Number.

 

 

2. Place the text "(Not Invoiced)" on the layout and format it conditionally to have a font size of 500pt when =

not IsEmpty ( _pk_InvoiceID )

BTW, this is not only the easy way, but also the correct one: a field should not be "multi-tasking" like that. Just consider what if you ever wanted to summarize this field.

 

Thankyou for that - I thought I was going to have to end up reverting the field back to a 'number' - your suggestion works nicely.