• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

Updating Infopath Repeating table fields

In my form I have the following fields:
Vendor (a dropdown field that gets its values from a secondary data connection)
VendorCurrency (value based on secondary data connection relative to the Vendor)
ExchangeRate (initially set via a secondary data connection to a sharepoint list)

And in a repeating table in the same form I have the following fields:

The idea is that the user will enter the LineItemCost and LineItemCurrency and the FinalLineItemCost would be calculated via rules added in both the LineItemCost and LineItemCurrency to caculate based on the exchange rate. The rules are as follows:
 •      If SupplierCurrency = LineItemCurrency then FinalLineItemCost = LineItemCost
•      If SupplierCurrency = “CAD” and LineItemCurrency = “USD” then FinalLineItemCost = ExchangeRate * LineItemCost
•      If SupplierCurrency = “USD” and LineItemCurrency = “CAD” then FinalLineItemCost = (1/ExchangeRate) * LineItemCost

Now I want to allow the user to change the ExchangeRate, however I cannot figure out how to update the FinalLineItemCost for each row of the repeating field since those fields are calculated via a rule in the other two LineItem fields.
1 Solution
Yah, as it stands, unless the other fields are touched then it does not work.

one idea is to add a exchange rate to your repeating table, it does not have to be shown in the view but used in the calculation.
then when the master/global exchange rate is changed it can change the repeating ones which would trigger the recalc.

hope that helps.
AlariusAuthor Commented:
That worked!
I added 'LineItemExchangeRate' with default value = ExchangeRate. (recalculate checked)
I had to copy the rules from LineItemCurrency into this field, so a change in ExchangeRate would change LineItemExchangeRate which would then in turn change FinalLineItemCost.

A little dirty but as long is it works :)

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now