Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

April, 2008

  • Microsoft InfoPath 2010

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

    • 1 Comments

    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

Page 1 of 1 (1 items)