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:
LineItemCost
LineItemCurrency
FinalLineItemCost
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.
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:
LineItemCost
LineItemCurrency
FinalLineItemCost
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
Thanks!