When you create a calculated field in an InfoPath XML node (field) you may find that some of the resulting calculations produce an “Invalid Data” error:
This behavior is a known issue when doing floating point calculations and is *not* specific to InfoPath or Microsoft for that matter. The floating point calculation behavior is explained in detail in several articles on the Internet; however, here are a few for reference:
Sun Microsystems: What Every Computer Scientist Should Know About Floating-Point Arithmetichttp://docs.sun.com/source/806-3568/ncg_goldberg.html
Lahey Computer Systems: The Perils Of Floating Pointhttp://www.lahey.com/float.htm
To create a sample of the above result:
- Create a new SQL Server or Access database table named: FloatingPointTest
- Add the following fields and data types:
- Create an InfoPath Form Template based on this table
- When complete, your data source should look like this:
- Add the “FloatingPointTest” repeating group to the View as Repeating Section with Controls
- Set the Default Value property of the Total field to the expression: Quantity * Price
- Preview the form
- Enter a value of 1 for the quantity and 2346.76 for the Price – result: the Total field displays the correct result
- Modify the quantity to a value of 6 – result: the Total field displays the correct result but the control has a red-dashed border indicating an invalid value.
The reason why the invalid data appears on some values and not on others has to do with the representation of the floating point value. For example, values ending in .25 (1/4 fraction or multiples of it) can be represented exactly, while other values cannot. The following link from the Microsoft Knowledge Base provides a Tutorial to Understand IEEE Floating-Point Errors.
Tutorial to Understand IEEE Floating-Point Errors
This behavior is easily corrected in InfoPath by modifying the calculation to use the “round” function. In this sample, we are looking to have a result with 2 decimal places –as such, our expression would be modified to: round((@Quantity * @Price) * 100) * .01
After making this modification and previewing the form, the same test values now produce a valid result:
Dragos BaracSenior Development LeadFiled Under: Controls, Formulas and XPath
So nice to see the IP Team blogging again.