In your FxRate list insert the concatenation of year with month number in front of the currency value all in one field.

Next, in your list lookup for FXRate($) change the "Equals value" property to be the formula:
ifElse(Form mode.Is Display mode,FxRate
,formatDate(Context.Current date,"YYYYMM")+parseLookup(Currency,true))

Now the exchange rate will always be the year and month for the selected currency when the form is new or being edited.

---

Hey thanks for the quick response,

I have spoken to the customer who need more instructions on how this will impact the Finance Department who ultimately will be responsible for updating the FX rate for all 170 currencies each month.

 

So when you say "In your FxRate list insert the concatenation of year with month number in front of the currency value all in one field." do you mean GBP would change to 201911GBP, and what about the December update??

 

i did have an idea to create a separate column for each update eg 201911 would have this month's FXRates, then a new column 201920 for December etc. But how to reference that in the formula??

 

There is an added layer of complexity however, the form has 2 stages, Travel Authorisation where estimated expenses are submitted and approved in advance of travel, this then activates the Travel Claim process where actual expenses are submitted and the FX rate may have changed. 

 

 

They can't have staff being left out of pocket because of the change in FX Rate.

 

Your advice here has been really helpful Thanks!

Thanks
Chet

---

Here is a refined approach that involves a List, a variable and a Rule.

1) create a List called FxRateList with 2 columns:
- YearMonthCurrency as single line of text, required
- FxRate as Number with automatic number of decimal places, required

Note: You could create a Site Workflow that's scheduled to execute monthly to automatically populate the next set of exchange rates from a CurrencyList using the "Call HTTP Web Service" action to a exchange rate service provider like https://exchangeRatesAPI.io/ or https://fixer.io/ or https://free.CurrencyConverterAPI.com/
Training on "Call HTTP Web Service" from Nintex is: https://www.youtube.com/watch?v=YE8ncB9LqZw

2) So the variable that will do the following:
a) return the current value of the FxRate on the form if the form is opened in read-only mode (meaning all creation and editing/approval has been completed)
OR
b) use the lookup function to leverage FxRateList and filter on the YearMonthCurrency combination column and matching the current year and month and currency (on the form)

The variable definition is:
ifElse([Form mode].[Is Display mode],[Form].FxRate
,lookup("FxRateList"
,"YearMonthCurrency"
,formatdate([Context].[Current date],"YYYYMM")+parseLookup(Form.Currency,true)
,"FxRate")
)

3) the Rule logic is:
if Currency is filled then
- FxRate value is assigned the variable defined in step 1

Note: The "else" case is handled by the variable definition

---