Hi there,
I have a table and it has a field as "price". I configured that field as "€" currency, so i can see numbers with "€" symbol in tableview and detailview. But sometimes i can use "$" or another currencies. So how can i configure each records with different currencies on a field?
Best regards.
A Currency Field with more than one option
Re: A Currency Field with more than one option
The problem you are describing here is actually valid for all tasks where something has to be versioned - here: Exchange rates depending on date
I such cases I am usually adding a lookup table for currencies and store the currency_id next to the price.
Example
Table "invoicess"
Having the normalized price-column will help you for sums, for reporting etc. because the normalized price will already contain the correct currency rate at a certain date and not the actual currency rate which might be completely different when viewing the record later.
Extension for more flexibility
In case you need some kind of currency-rate history or if you'd like to import or edit the rates manually, I recommend having another table for currency-rates per date/period of time:
Table "currency_rates"
The solution i have described here might look a bit complicated. but if you want to work with different currencies, you have to store the exchange rates somewhere. Either you have to store the exchange rate directly in addition to the price and/or in a separate lookup table.
From my experience, it makes sense to store the calculated price in local currency in addition to the price in foreign currency and in addition to the currency. This makes it easier in the following calculations.
I such cases I am usually adding a lookup table for currencies and store the currency_id next to the price.
Example
Table "invoicess"
- id
- status_id
with this field you could control whether the prices of this invoice should be recalculated when saving or should remain unchanged - ...more fields...
- id
- invoice_id [int fk referencing invoices table]
- position
- ...
- price [editable input field, decimal 10,2 for example]
- currency_id [int fk referencing pk of currencies table]
- rate [readonly, populated on insert or update with actual rate from currencies table]
- price_local [readonly, calculated on insert or update]
this is the calculated price in local currency as a result of price in foreign currency x quantity x currency-rate at a certain date - ...more fields...
- id (1)
- name ("Euro")
- abbreviation ("EUR")
- symbol ("€")
- actual_rate (1.00000)
Having the normalized price-column will help you for sums, for reporting etc. because the normalized price will already contain the correct currency rate at a certain date and not the actual currency rate which might be completely different when viewing the record later.
Extension for more flexibility
In case you need some kind of currency-rate history or if you'd like to import or edit the rates manually, I recommend having another table for currency-rates per date/period of time:
Table "currency_rates"
- id
- currency_id
- date_from
- date_till
- rate
The solution i have described here might look a bit complicated. but if you want to work with different currencies, you have to store the exchange rates somewhere. Either you have to store the exchange rate directly in addition to the price and/or in a separate lookup table.
From my experience, it makes sense to store the calculated price in local currency in addition to the price in foreign currency and in addition to the currency. This makes it easier in the following calculations.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: A Currency Field with more than one option
Thank you so muchjsetzer wrote: ↑2020-10-13 11:32The problem you are describing here is actually valid for all tasks where something has to be versioned - here: Exchange rates depending on date
I such cases I am usually adding a lookup table for currencies and store the currency_id next to the price.
Example
Table "invoicess"Table "invoice_positions"
- id
- status_id
with this field you could control whether the prices of this invoice should be recalculated when saving or should remain unchanged- ...more fields...
Table "currencies"
- id
- invoice_id [int fk referencing invoices table]
- position
- ...
- price [editable input field, decimal 10,2 for example]
- currency_id [int fk referencing pk of currencies table]
- rate [readonly, populated on insert or update with actual rate from currencies table]
- price_local [readonly, calculated on insert or update]
this is the calculated price in local currency as a result of price in foreign currency x quantity x currency-rate at a certain date- ...more fields...
On insert/update, if price-calculation is required, you can calculate a "normalized" price in local currency (for example in EURO) by multiplying the price and the actual currency rate of the selected currency. The rate can be read from another table or for example from a financial webservice API.
- id (1)
- name ("Euro")
- abbreviation ("EUR")
- symbol ("€")
- actual_rate (1.00000)
Having the normalized price-column will help you for sums, for reporting etc. because the normalized price will already contain the correct currency rate at a certain date and not the actual currency rate which might be completely different when viewing the record later.
Extension for more flexibility
In case you need some kind of currency-rate history or if you'd like to import or edit the rates manually, I recommend having another table for currency-rates per date/period of time:
Table "currency_rates"
Then, in "invoice_items" table next to curreny_id and price you should store the date. Having date and currency_id, you will be able to fetch the rate for a specific date and currency from "currency_rates" table. Having the rate for that day, you can calculate the normalized price event later.
- id
- currency_id
- date_from
- date_till
- rate
The solution i have described here might look a bit complicated. but if you want to work with different currencies, you have to store the exchange rates somewhere. Either you have to store the exchange rate directly in addition to the price and/or in a separate lookup table.
From my experience, it makes sense to store the calculated price in local currency in addition to the price in foreign currency and in addition to the currency. This makes it easier in the following calculations.
Re: A Currency Field with more than one option
jsetzer, is there any option for currency format? I mean i'm using euro format as "123,456.00" but i want use it as "123.456,00"
So how can i do that?
Thanks a lot.
So how can i do that?
Thanks a lot.
Re: A Currency Field with more than one option
Please have a look at dataformat options.
This could be a starting point:
viewtopic.php?f=11&t=1614&p=11142&hilit ... cfg#p11142
This could be a starting point:
viewtopic.php?f=11&t=1614&p=11142&hilit ... cfg#p11142
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: A Currency Field with more than one option
This is the real solution. Thank you so much.jsetzer wrote: ↑2020-10-15 08:25Please have a look at dataformat options.
This could be a starting point:
viewtopic.php?f=11&t=1614&p=11142&hilit ... cfg#p11142
Have a nice day.
Re: A Currency Field with more than one option
I just stumbled upon two javascript libraries that might be interesting if one of you needs to convert amounts from one currency to another:
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools