Thursday, January 26, 2012

'NaN' in InfoPath Decimal/ percentage field

In InfoPath 2007, we used to get NaN in our calculated decimal fields. So, I tried to add a Rule to check if the value is NaN and replace it is with '0' if it is true. However, there is no option to check if the value of a decimal field with a text NaN
NaN error in calculated decimal fields

So the workaround for this is, change the field type to text and add the rule as below. After creating the rule, again change the field type to Decimal.

NaN error in calculated decimal fields

Wednesday, January 25, 2012

Sumproduct in InfoPath 2007

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

Sumproduct in InfoPath 2007

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

Sumproduct in InfoPath 2007

Now apply the following formula in the Total field

sum(QuantityPurchasePrice) * (1 + SalesTax)

..and with some sample value, the output is

Calculate Sumproduct in InfoPath 2007

Monday, January 23, 2012

unbound (control cannot store data)

Scenario: In Design view of a InfoPath 2007 form, had several controls within a table and the table is placed within a section control. Deleted the section control and got the following error in all the controls within the section

Issue: Error in controls as "unbound (control cannot store data)"

Reason: The controls has unbind

Resolution: Right click each control, click Change Binding and from the data source, select the appropriate field in which to store the control data .

InfoPath Change Binding