Custom form calculated field - Help | Community
Skip to main content
R
RichardHu2
May 28, 2021
Question

Custom form calculated field - Help

  • May 28, 2021
  • 1 reply
  • 655 views

Can anyone see any issues with the calculation below. Formula aims to convert a Local currency into GBP Sterling. User inputs value in local currency (New Credit Limit), selects currency from a drop down field e.g. AUD - Australian Dollar and the formula should divide local currency by exchange rate.

I've tried so many iterations but i just get the very unhelpful red box. Any guidance much appreciated

ROUND(IF(Currency="AUD - Australian Dollar",DIV({New Credit Limit},{AUD - Exchange rate}),

IF(Currency="EUR - Euro",DIV({New Credit Limit},{EUR - Exchange rate}),

IF(Currency="USD - US Dollar",DIV({New Credit Limit},{USD - Exchange rate}),

IF(Currency="CHF - Swiss Franc",DIV({New Credit Limit},{CHF – Exchange rate}),

IF(Currency="HKD - Hong Kong Dollar",DIV({New Credit Limit},{HKD – Exchange rate}),

IF(Currency="CZK - Czech Koruna",DIV({New Credit Limit},{CZK - Exchange rate}),

IF(Currency="JPY - Japanese Yen",DIV({New Credit Limit},{JPY - Exchange rate}),

IF(Currency="GBP - Great British Pound",DIV({New Credit Limit},{GBP - Exchange rate}),

IF(Currency="RMB - Chinese Yuan Renminbi",DIV({New Credit Limit},{RMB - Exchange rate},""))))))))),0)

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

skyehansen
Community Advisor
skyehansenCommunity Advisor
Community Advisor
May 28, 2021

quick question - I noticed the CHF and HKD exchange rates both have a longer (n-dash) dash instead of a hyphen. Is this really the case?

second quick question: did a shorter version of this calculation work and now you're having problems adding new rates to it, or is the entire thing always failing?

R
RichardHu2Author
May 28, 2021

Thanks Skye, yes good spot. Fixed that but still not working. I've tried shortened versions of the formula with less currencies but to no avail, I'd reached the conclusion there must be something fundamentally wrong with my formula. For example the following doesn't work either

ROUND(IF(Currency="AUD - Australian Dollar",DIV({New Credit Limit},{AUD - Exchange rate}," "),0)

skyehansen
Community Advisor
skyehansenCommunity Advisor
Community Advisor
May 28, 2021

I'm getting results with the following:

ROUND(IF({Currency}="1",DIV(New Credit Limit,{Exchange Rate})," "),0)

Created in my system:

-Field called "Currency" with dropdown choices 1 and 2

-Number Field called New Credit Limit

-Number Field called Exchange Rate

-Calculated Number Field (calculation above)

Switching between 1 and 2, selecting 1 gives me a result, and selecting 2 gives me 0.

Terms & ConditionsAccessibility statement

Loading footer...