Prevent calculated value from updating once form is approved

  • 18 November 2019
  • 3 replies
  • 10 views
C

Hi there,

 

I'm having an issue with a Global Expense form built using Nintex Forrms for Office 365. Expenses are calculated using a lookup from a list of currency rates which convert the cost to USD. 

 

The Fx Rate is updated monthly and this results in the calculated value changing on all previously submitted forms and not just the current form. 

 

I am using a repeating section to add expenses for approval as below: As you will see the 

 

5585i69E36E8437F93610.png

 

 

There is no copy item function in Nintex workflow for Office 365 in order to copy all approved expenses with attachments to a separate list as that would have been an option, The create item does not include attachments

 

Hope someone can tell me if I'm missing something easy here, I've also considered using workflow to store the FX rate to a text field on each row then changing the lookup to the text field value on form approval too.

 

Cheers

Chet

 

3 replies

V
Userlevel 3
Badge +9
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.
C

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

V
Userlevel 3
Badge +9
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

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings