An workaround to do similar to excel Sumproduct function in InfoPath 2007
We had a requirement to calculate the sum of product of 2 column in a repeating table. See the screen shot below
Sales Tax is a Decimal field with Percentage format enabled
Quantity is an integer
Purchase Price is Decimal field
Monthly Total is Decimal field
We want the Monthly Total value to be
Monthly Total = Sum(Quality * Purchase Price) * percentage of Sales Tax
Since there is no option to do the sum(Quality * Purchase) in InfoPath as SUM XPath function takes only a node set as parameter, I've created a field named QuantityPurchasePrice to have the product of Quality and Purchase Price and made the field hidden by setting the conditional formatting as below
Now apply the following formula in the Total field
sum(QuantityPurchasePrice) * (1 + SalesTax)
..and with some sample value, the output is
We had a requirement to calculate the sum of product of 2 column in a repeating table. See the screen shot below
Sales Tax is a Decimal field with Percentage format enabled
Quantity is an integer
Purchase Price is Decimal field
Monthly Total is Decimal field
We want the Monthly Total value to be
Monthly Total = Sum(Quality * Purchase Price) * percentage of Sales Tax
Since there is no option to do the sum(Quality * Purchase) in InfoPath as SUM XPath function takes only a node set as parameter, I've created a field named QuantityPurchasePrice to have the product of Quality and Purchase Price and made the field hidden by setting the conditional formatting as below
sum(QuantityPurchasePrice) * (1 + SalesTax)
..and with some sample value, the output is
Hello! I tried this and can't get it to work. I added the hidden multiplication field (and actually have it showing to make sure the calculation runs correctly), and I have the sum of that hidden field, and nothing shows up. I've read on other posts that it isn't possible to perform calculations on calculated fields. Any ideas?
ReplyDeleteThe calculations are done in the normal text fields. Are you able to resolve the formula in the field?
Delete