Creating a Calculated Column for Annual Value in USD Based on Currency in Retool Table

[Yanis_Sid-Lakhdar]

Hello,

I'm currently working on a project where I need to create a calculated column in a table (table1). The goal is to convert a monthly value in various currencies to an annual value in USD. I have two columns in my table: "Monthly Value (in local currency)" which is an integer, and "Currency" which holds currency codes (e.g., NGN, GHS, EUR, ZAR).

I need to create a new column titled "Annual Value in Dollar". The idea is to multiply the "Monthly Value (in local currency)" by 12 (to get the annual value) and then convert it to USD based on the currency specified in the "Currency" column. The conversion rates are predefined (e.g., NGN at 750, GHS at 15, etc.).

I attempted the following code in a calculated column, but it's not working as expected – either resulting in errors or showing 0 values:

{{ self['Monthly Value (in local currency)'] * 12 / (self.Currency === "NGN" ? 750 : (self.Currency === "GHS" ? 15 : (self.Currency === "EUR" ? 1 : (self.Currency === "ZAR" ? 20 : 1)))) }}!

[ScottR]

Is ['Monthly Value (in local currency)'] a custom column?
and what are the default names of the columns (not Labels you entered) from the data set?
Screenshot will help because you could use currentSourceRow instead of self depending on the way the table is set up

[Yanis_Sid-Lakhdar]

Hi Scott! Thanks so much. Here attached the screenshot.

[ScottR]

In the Annual Value in Dollar column in the Mapped value field you can try
{{(currentSourceRow.value*12)}}/{{currentSourceRow.currency === 'NGN"? 750: currentSourceRow.currency === 'GHS" ? 15 : currentSourceRow.currency === 'EUR'? 1 : currentSourceRow.currency === 'ZAR'? 20: 1}}

[Yanis_Sid-Lakhdar]

Thanks. I get this error...

SyntaxError: Unexpected identifier 'GHS',SyntaxError: Unexpected identifier 'GHS',SyntaxError: Unexpected identifier 'GHS'

[Yanis_Sid-Lakhdar]

It looks like currentSourceRow is not defined

[ScottR]

Are you using the Legacy table component or the newer version Table component?
If Legacy, then currentRow
If newer Table then currentSourceRow
In the Mapped value field of the column

[Yanis_Sid-Lakhdar]

Hi Scott, apologise in advance, I'm new to Retool. Thanks for your help... I don't know how to check the nature of my table (Legacy or Newer). Just to be sure I also tried with currentRow. It seems the error is the same... Nor currentRow or currentSourceRow is defined.

[ScottR]

Can you post a screen shot of the Annual Value in Dollar column Inspect panel ? It doesn't seem that you are adding the information in the correct place.

[Yanis_Sid-Lakhdar]

Sure!

[ScottR]

Is that column a custom column and is it a dropdown in a legacy table? It just doesn't look right to be as you should be seeing a Mapped value field and if it is not a Legacy table field then you should be using currentSourceRow
Try adding a new table with the same Data source and then start over from where we atarted because something seems a bit off with the set up you have or I am completely missing the issue...

[Tess]

Hi @Yanis_Sid-Lakhdar,

If you haven't already solved this, you may want to try refactoring the logic like this:

{{(currentSourceRow.value\*12)/(currentSourceRow.currency === 'NGN' ? 750 : currentSourceRow.currency === 'GHS' ? 15 : currentSourceRow.currency === 'EUR'? 1 : currentSourceRow.currency === 'ZAR'? 20: 1)}}

[Yanis_Sid-Lakhdar]

Thank you @ScottR and @Tess ! It worked!