How to Manage Multiple Currencies

If you need to manage multiple currencies, such as when your products or services are priced in local currencies across different markets or when transacting with suppliers in various currencies, Ragic enables you to achieve this functionality through form design.

Since Ragic is a "database builder", its multiple currency setup differs from typical software workflows. You can’t simply enable a setting to specify currencies. Instead, you need to identify the currencies required and apply the corresponding fields, formulas, and settings to your sheets.

For example, if your company sells products to Europe and South America, you’ll need to record prices in multiple currencies starting from the product sheet. Later, quotes, orders, shipping, procurement, and financial sheets may also require multi-currency design.

This document explains Money fields, Numeric fields, using formulas for currency conversion, and principles for cross-currency calculations to help you design and manage multiple currencies.

The Difference Between Money and Numeric Fields

The Money field is simply a Numeric field with a dollar sign ($) before the numeric value by default. This symbol does not represent any specific currency. You can customize the currency symbol in the field format settings, such as changing $ to £, €, or other currency symbols.

Since the Money field works the same as a Numeric field, you can also use a Numeric field and specify the currency in the Field Header.

Both the currency symbol in the Money field and the field name in the Numeric field serve only to help users identify the currency of the displayed value. Beyond the symbol and field name, the field simply stores a numerical value.

Managing Multiple Currencies

When managing multiple currencies, you can create fields to store values in different currencies. For example, a product's price can include values in JPY, USD, and EUR.

Various situations may require applying multiple currencies. Refer to the following scenarios to find the approach that best suits your needs.

Using Money and Numeric Fields Directly

If your products or services have fixed pricing in different regions and do not fluctuate with exchange rates in the short term, you can simply add fields and enter the corresponding values. For example, if there are three currency prices, you would need three separate Money or Numeric fields.

Using Exchange Rate Fields

The Exchange Rate field can retrieve real-time exchange rates from Open Exchange Rates. When combined with formulas, this enables you to calculate amounts in different currencies. Here are some example scenarios:

1. Converting Currencies with Current Exchange Rate

Create an Exchange Rate field and configure it with the original and target currencies. When creating a new record, this field will automatically populate with the exchange rate at that time.

You can then apply a formula to multiply the original currency field by the exchange rate field to calculate the amount in the target currency.

2. Converting Currencies Without Using the Current Exchange Rate

Sometimes, the exchange rate used may not be based on the current rate. For example, when recording past transactions, receiving supplier quotations (which use their rates), or referring to a specific bank's exchange rate. In such cases, enable the Fetch exchange rate of selected date option in the Exchange Rate field.

For instance, you can specify the "Quotation Date" as the selected date. This allows different exchange rates to be applied based on the quotation date, using the rate from that specific time.

You can then apply the formula in the same way, multiplying the original currency field by the exchange rate field to obtain the amount in the target currency.

3. Multiple Currency Conversion

If you need to convert multiple currencies, such as converting the currencies used by different customers into EUR or USD, you can use the Exchange Rate field's Select Referenced Field feature.

For example, to convert five currencies into USD, first create a "Currency" field and set it as a Selection field to choose the currencies to convert.

Note: The referenced fields can only be either Free Text or Selection fields. The field value must be three uppercase letters, such as USD, JPY, EUR, etc.

Next, check the Select Referenced Field option in the Exchange Rate field to specify the reference field as the "Currency" field. Then, set the target currency as USD.

You can then select different currencies, and the corresponding exchange rate will be automatically applied to convert them into USD.

If the target currency for conversion includes multiple options, you can add another "Target Currency" field, set it as a Selection field, and then configure the Select Referenced Field for conversion.

You can then select different currencies as needed, and the corresponding exchange rate will be automatically applied to convert the amount accordingly!

Using Link and Load to Apply Custom Exchange Rates

If you want to manage multiple currencies with custom exchange rates, create a separate sheet for the currencies and rates, then set up Link & Load relationships on the relevant sheets.

First, create a sheet to record all required currencies and their respective exchange rates.

Next, set up Link & Load relationships on sheets requiring currency conversion, using the "Currency" sheet as the linked source.

When a currency is selected, its exchange rate will be loaded, allowing you to apply formulas referencing the loaded exchange rate as needed.

Using Global Constant to Define Exchange Rates

If you need a fixed exchange rate for a specific period, such as a year or quarter, set up a Global Constant.

For example, to apply a unified EUR rate for 2025, define a "EURExchangeRate" constant and its time range.

Then, apply the "EURExchangeRate" constant in the formula to calculate the amount.

If there are multiple currencies, simply add different constants for each currency.