Udostępnij za pośrednictwem

“Invalid Data” error when calculating the result of 2 or more fields

  • Artykuł

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:

InvalidDataError

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 Arithmetic
http://docs.sun.com/source/806-3568/ncg_goldberg.html

Lahey Computer Systems: The Perils Of Floating Point
http://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:

  • ID (Int, No Nulls, Primary Key)
  • Quantity (Int)
  • Price (Money)
  • Total (Money)

- Create an InfoPath Form Template based on this table

- When complete, your data source should look like this:

DataSource

- 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

DefaultValue_TotalNode

- 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.

SampleErrorResult

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

http://support.microsoft.com/kb/42980

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

Round_Expression

After making this modification and previewing the form, the same test values now produce a valid result:

ValidResult

Dragos Barac
Senior Development Lead
Filed Under: Controls, Formulas and XPath

Comments

  • Anonymous
    June 09, 2008
    So nice to see the IP Team blogging again.