InfoPath Dev

 Ok, this is a strange one.  I hope I'm just overlooking something.

 I have 3 fields in each row of a table (non-repeating).  There are many rows, and the fields are ListPrice, Discount, and FinalPrice.  Of my many rows, the FinalPrice fields has the same formula, but they reference their respective ListPrice and Discount fields to get the calculation result.

 Here's the formula: ListPrice * (1 - Discount)

Pretty simple huh?  Now, all of the ListPrice and FinalPrice fields are set to "Decimal" Data Type and are "Currency" with 2 decimal places and a comma separator.

All of the Decimal fields are set to "Decimal" Data Type and are "Percentage" with 2 decimal places and a comma separator.

Of the 5 rows in my table, 3 of them calculate perfectly.  The strange part is that 2 rows do not.  They give me an error "NaN" when I preview.

I have tried different formulas to see if the field was somehow screwed up, and they worked.  I even tried setting their default values to equal ListPrice or Discount, and the numbers came through just fine.  I also tried multiplying ListPrice * Discount, and that did not come out correctly.  I think it has something to do with the number formatting, but I'm not sure.

I was wondering if anyone had any experience with this error, and why it occurs.

Thanks for any help!
 

Hi --

 Just a thought, are you using InfoPath 2007? Under Form Options/Advanced there is a checkbox to treat blank values as zero. If ListPrice or Discount are blank and this is not selected, you would get NaN.

Hi,

 Thanks for your response.  I am using IP 2007 and I looked in Form Options and found that the box is checked.  Also, ListPrice and Discount have values in them.  This is a strange one... 

I'm going to delete the fields and recreate them for a fresh look.  I'll post what happens.  In the meantime, if there are any other suggestions I'd gladly take a look. 

thanks

I finally got it!  It was the weirdest thing.  I have my data in an SQL server and the pieces of data that were being pulled into the Discount boxes were set to "float" in SQL.  I compared the values that were causing the error with the values that had no error and found that the "errored" data had one more digit in the decimal places than all of the rest in that column.

So I changed the type in SQL for that column to be "decimal(18, 5)" (decimal with precision 18 and scale 5. 

That fixed the problem.

My guess is that it was just too many digits for InfoPath to handle...maybe? 

I hope this helps anybody with a mysterious error like this in the future!
 

Thank you for posting your solution for others to see! I'm glad you were able to track down the problem. I found this post on the InfoPath team blog about floating point calculations that may explain the problem: http://blogs.msdn.com/infopath/archive/2008/04/17/invalid-data-error-when-calculating-the-result-of-2-or-more-fields.aspx

I had to use both the rounding function AND the decimal(18,5) in SQL to prevent the NaN