Currency Conversion Using Custom Formula Fields

In the Dataset Builder, you can create custom formula fields with the context value #converted to change field values that are in different currencies.

For example, to show remaining balances on invoices no matter the original currency, you can create a custom formula field like this:

          TO_NUMBER({foreignamountpaid#converted}) - TO_NUMBER({foreignamountunpaid#converted})

The function TO_NUMBER is needed because the current Formula Builder doesn't support the CURRENCY output type, so TO_NUMBER converts these values to FLOAT. In single currency accounts, you could use this formula without the #converted context value:

          TO_NUMBER({foreignamountpaid}) - TO_NUMBER({foreignamountunpaid})

Depending on your formula, you can convert field values to your account's base currency or a specific currency you pick. You can also choose the exchange rate based on a date you enter in the formula.

Use these sample formulas to convert the values of the total amount (transaction currency) field. The output is FLOAT:

Note:

To convert values from other fields, replace foreigntotal with a different field ID.

          TO_NUMBER({foreigntotal#converted})

Displays the currency amounts in your account's base currency. The conversion uses the exchange rate that's active when you run the query or workbook visualization.

          TO_NUMBER({foreigntotal#converted[1]})

Displays the currency amounts in the currency ID specified in the brackets [1]. The conversion uses the exchange rate active on the date when you run the query or workbook visualization.

          TO_NUMBER({foreigntotal#converted[1;2017-01-30]})

Displays the currency amounts in the currency specified in the brackets [1], using the exchange rate set for a specific date. Enter the date in YYYY-MM-DD format.

Related Topics