A Currency Field with more than one option

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
yonder
Posts: 28
Joined: 2018-05-01 12:19

A Currency Field with more than one option

Post by yonder » 2020-10-13 10:15

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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: A Currency Field with more than one option

Post by jsetzer » 2020-10-13 11:32

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"
  • 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 "invoice_positions"
  • 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...
Table "currencies"
  • id (1)
  • name ("Euro")
  • abbreviation ("EUR")
  • symbol ("€")
  • actual_rate (1.00000)
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.

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
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.

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

yonder
Posts: 28
Joined: 2018-05-01 12:19

Re: A Currency Field with more than one option

Post by yonder » 2020-10-15 06:17

jsetzer wrote:
2020-10-13 11:32
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"
  • 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 "invoice_positions"
  • 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...
Table "currencies"
  • id (1)
  • name ("Euro")
  • abbreviation ("EUR")
  • symbol ("€")
  • actual_rate (1.00000)
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.

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
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.

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.
Thank you so much :)

yonder
Posts: 28
Joined: 2018-05-01 12:19

Re: A Currency Field with more than one option

Post by yonder » 2020-10-15 06:37

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.

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: A Currency Field with more than one option

Post by jsetzer » 2020-10-15 08:25

Please have a look at dataformat options.

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

yonder
Posts: 28
Joined: 2018-05-01 12:19

Re: A Currency Field with more than one option

Post by yonder » 2020-10-15 12:11

jsetzer wrote:
2020-10-15 08:25
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 is the real solution. Thank you so much.
Have a nice day. ;)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: A Currency Field with more than one option

Post by jsetzer » 2020-10-21 08:22

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 [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Post Reply