NaN Error, Calculating default value - InfoPath Dev
in

InfoPath Dev

Having trouble finding a blog or post that answers your question? Check out our Custom Search Page

NaN Error, Calculating default value

Last post 04-27-2009 08:41 AM by brandtsmith. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next
  • 07-09-2008 10:06 AM

    • Patrick
    • Top 200 Contributor
      Male
    • Joined on 11-04-2007
    • Texas
    • Posts 51

    NaN Error, Calculating default value

     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!
     

    -Patrick
  • 07-09-2008 11:51 AM In reply to

    Re: NaN Error, Calculating default value

    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.

    Hilary Stoupa
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™

  • 07-09-2008 12:00 PM In reply to

    • Patrick
    • Top 200 Contributor
      Male
    • Joined on 11-04-2007
    • Texas
    • Posts 51

    Re: NaN Error, Calculating default value

    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

    -Patrick
  • 07-09-2008 01:26 PM In reply to

    • Patrick
    • Top 200 Contributor
      Male
    • Joined on 11-04-2007
    • Texas
    • Posts 51

    Re: NaN Error, Calculating default value

    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!
     

    -Patrick
  • 07-09-2008 01:42 PM In reply to

    Re: NaN Error, Calculating default value

    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

    Hilary Stoupa
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™

  • 04-27-2009 08:41 AM In reply to

    Re: NaN Error, Calculating default value

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

Page 1 of 1 (6 items)
Copyright © 2003-2012 Qdabra Software. All rights reserved.
View our Terms of Use.