InfoPath Dev

Hi guys,

I'm developing a data form with nested repeating groups. I have all of the data coming across perfectly and it's a beautiful thing! The problem i am discovering in our form(s) is that when we calculate a new budget, simple addition, we get a  long decimal value. Not a big problem, or so i thought. I attempted a round function. It will work for a few fields of data but not all and then others that were once fine in double decimal were now calculating a long decimal value. So i attempted another fix by converting it into a string then taking out only the numbers before the decimal and only 2 after. The problem with this is that without the rounding we loose pennies with each new calculation of budget. I have done research and found that there is an issue with the floating arithemetic for calc values in xpath. Have any of you encountered this problem before? Is there a quick and easy fix? Any help is welcome.

number(substring(string(o_labour + o_expense), 1, string-length(substring-before(string(o_labour + o_expense), ".")) + 3))      --- to convert to string and truncate decimal (ex: 2.34566 to 2.34)

round(number(substring(string(o_labour + o_expense), 1, string-length(substring-before(string(o_labour + o_expense), ".")) + 3)) * 100) * .01 -- rounding function

I've applied both methods boht with and without the string number convert and with and without rounding functions at all field levels. I know for a fact that the data is not the cause of the problem. I have screenshots i can provide in email.

Michael

 K, I still haven't found a fix.

Would a rule be preferable over the default value for these functions? If so could you tell me why and to what would cause the calc value to behave in such a manner?

 I'd leave the value alone and just change the format on the control that displays the data to only show two decimal points. Does that work for you?

 No, unfortunately it does not. Since the formatting is only at face value it still throws a validation error. This is a sql integration data form. The budget changes occur in the form and are then put back into the server.

Knowing this, Everytime we try a submit of data back to the server it throws a validation error in InfoPath.

In example we will take two fields and add them together:

o_labour = 250.05  +  o_expense = 250.10

calc_value(Total) = 500.150000000004

That is about the jist of what's occurring. I have screenshots if you want me to email them to you. Just let me know, I believe I still have your email.

 Euw - got it - what's the data type in SQL? Can it be changed to allow more decimals?

 Its not a data issue, It allows 9 decimals the data comes out of SQL fine. It's in the calc value that InfoPath or rather xpath that for some reason is taking a regular number and making a long decimal value..

Ex:

Sql data = 154.200000000 and 200.290000000

in InfoPath the field data will be fine when we view the xml data(matches the SQL data). but in the Calc Value for the total it will have a trailing long decimal.   Total = 354.490000002

I thought maybe by rounding the field values containing the obtained SQL data  it would affect the calc value. (Failed)

I thought maybe rounding the calc value.( Failed)

I thought maybe converting to string  to truncate the decimal to a double decimal but the calc value STILL makes up a long decimal. I know its the calc value creating the problem. But I don't know how to fix it. It's only addition. If we were multiplying I could understand how it would extrapolate a long decimal value. Have you ever had this issue before?

Thank you Hilary.

 Sorry the Data type is a simpleType. I believe its int in SQL but it comes across as simpleType in InfoPath. It's all coming across fine and looks great. Its only the calculated value creating a very strange long decimal. That wont adhear to the round function.

But if the data type is an int in SQL, you'd need to make sure the value you are submitting has no decimals, right? It looks like everything you've tried thus far has 2 digits after the decimal? Sorry, I have a feeling I'm confused. Feel free to attach screenshots under the Options tab.

 It's a numeric 28, 9, sorry.. Long day.

So, what I'm wondering is - why not change that 9 to a 15? And adjust the 28 accordingly, if your numbers are that big. And walk through the data connection again so the form has the updated schema. It just seems simplest? 

 Because the problem isn't the data, The data is fine, upping the decimal point max wont change the calculated value from coming up with a number that doesn't exist or from the rounding function not rounding.

if we add 500.00 + 500.20 it will equal 1000.20

but when the calc value does this, it comes up with a sum with a long decimal. ex: 1000.200000000003

Increasing the server side data acceptance will not change the affect xpath is calculating in its floating arithmetic. It will either input bad data into the DB or worse Not round and completely loose money on a daily basis. 

I will talk with my partner and see what he says about your advice, Thank you. IF you come up with anything else that may result in my desired solution, please let me know.

 Hey Hilary,

Just spoke with my boss and he says we have some more hours to spend with your team. Would you like to schedule a conference and you can look over my shoulder? Would that help so you can get a better feel for what's going on?

I tried putting up the screenshots. I couldn't get them uploaded. Perhaps im doing it wrong.

You can email Support AT Qdabra DOT com to schedule time with a Qdabra resource to take a look at the problem (it may not be directly with me, but we have a lot of other knowledgeable InfoPath resources who can help you out).

 Will do, Thank you.

We would very much prefer you if you could.

Have you ever, or anyone else who is viewing this, had an issue like this before with calculated values? Are there any other type of rounding formulas that I haven't tried using?

 The only decimal related calc'd value issue I've had was because IPFS and Filler use a different scale (one uses 15, the other 16) and it was causing me issues in a signed section - that said, I don't usually work with data bound forms. I've not run into formulas behaving inconsistently, which is what is sounds like you are saying is happening for you.