Dynamic Currency Display

[catwoman]

Dear Forum -

Our users are running FileMaker 9 on Windows XP and have their PCs set for various countries. FileMaker is great about displaying dates in the correct format according to the PC settings, but not currencies. Is there some way to display a number field in US currency format (1,000.00) to some users and EU currency format (1.000,00) to others in an elegant manner?

Here are things I have already tried / contemplated:

- I know that if I leave a number field in "General Format", then the PC country settings will be considered for a decimal point, if entered, but then the number does not always show the decimal nor a thousands separator and does not appear as currency. If I remove "General Format" and select 2 decimal places in the Number Format window, then I must select a decimal and thousands operator character, which is not dynamic by user and appears to override the users' PC country settings.

- I know that I can use conditional formatting based on a global setting and overlap two fields, etc, but that is very messy and there are hundreds of layouts in the solution and tens of tables with price fields... ugh. This is a last-resort solution!

Please advise, thanks!

[RodSierra]

We've designed our systems to have a selectable base or home currency, and also provided the ability to have any pricing or costing selectable as to currency. The setting, name of currency, its symbol or symbols and short name are in their own table, with another table for exchange rates. The currency symbols are then related fields based on the currency selected for the record via the currency id. Of coarse these can be combined in a calc field or a merge field also.

By using currency id's you can do automatic currency conversions via relationships to the exchange rate table. Perhaps a bit of a hassle to retro fit, but once it is setup it make currency conversion almost transparent to the user.

[stanley]

Hi Rod:

The problem is actually the number formatting. In the system catwoman is talking about (which I also work on), the currency is defined via its 3-letter code (USD, GBP, etc), which is stored in a separate table. So, in the US, to show $10,000.00, you have:

Currency = "USD"

Price = 10000

Displays as: USD 10,000.00

But, in Europe, they're stuck with the same formatting in the number field, so they get:

Currency = "EUR"

Price = 10000

Displays as: EUR 10,000.00

What we're after is the ability to display that number field in the right format.

Thus: EUR 10.000,00

The problem is we're using FMP number formatting, set to 2 decimal places, '.' for decimal divider and ',' as thousands divider. This means our currencies are always displaying in the US format.

Any suggestions?

Thanks

Stanley