Entering a number with or without currency symbol

[tinynumbers]

Hi. I've been trying to come up with the best way to approach this situation:

My table has a numeric value, call it "price". The price is a currency value, but the currency in which the items are priced can be variable.

I'd like for users to be able to type in a currency value and a number into a field on the layout, for example "$50" or "£30" or "GBP 30" or "USD 50" etc... - and to have that automatically stored as two items: (A) the numeric value (50 or 30 etc) and (: a link to an entry in the Currency table (i.e. the currency-code or some other unique ID in a table of all currencies).

So the question is how to parse that user input...

Or maybe it would be best just to force the numeric value of the price to just be a number (strictly validated) and to have the currency as a drop-down?

Thoughts>

Edited June 18, 2010 by Guest

[RodSierra]

I'd suggest using a separate table, CurrencyId, the currency name, abbreviation and symbol. This way the user can use whatever text the choose without effecting the relationship.

[Raybaudi]

FieldA = GetAsNumber ( Price )

FieldB = Trim ( Filter ( Price ; KanjiNumeral ( Price ) ) )

[comment]

I'd say go with two fields to begin with. It's much easier to put values together than to separate them.

FieldB = Trim ( Filter ( Price ; KanjiNumeral ( Price ) ) )

Check your result with "-$12.34" or "EUR (56,07)"

[Raybaudi]

Thank you.

( I suspect that a price isn't ever negative but the decimal point IS really a problem )

FieldB = Filter ( Price ; "$€£USDEURGBP[color:red]othercurrencies" )