General

Re: Error with double decimal Calculated Values

Patrick Halstead — Mon, 17 Jun 2013 22:08:29 GMT

Hi CCBSMF,

We can't find the support email. Which email address did you use?

Thanks!

Re: Error with double decimal Calculated Values

Hilary Stoupa — Wed, 12 Jun 2013 19:03:27 GMT

Hey- I'm sorry you didn't get a reply from your support email - I'll follow up and see what happened there. Thanks for the follow up!

Re: Error with double decimal Calculated Values

CCBSMF — Wed, 12 Jun 2013 18:45:44 GMT

So i emailed qdabra support, never heard back from anyone.

I decided to spend some time trying to dechiper this problem as i am pressed to get this project finished. AND BOOM, the answer hit me. here it is for anyone ever encountering the same problem :

number(substring(string(round((my:o_labour + my:o_expense) * 100) * .01), 1, string-length(substring-before(string(round((my:o_labour + my:o_expense) * 100) * .01), ".")) + 9))

once i nested the rounding formula in the truncating decimal formula, i no longer experienced any more long decimal sums from a simple addidtion calculation. I've applied this to all fields or rules that invlove a calculation.

for a little more indepth syntax with copying of data by respective row:

number(substring(string(round((../../../../my:zumi/my:effort[../my:row = current()/../@line_nbr] + ../../../../my:zumi/my:effort_to_complete[../my:row = current()/../@line_nbr]) * 100) * .01), 1, string-length(substring-before(string(round((../../../../my:zumi/my:effort[../my:row = current()/../@line_nbr] + ../../../../my:zumi/my:effort_to_complete[../my:row = current()/../@line_nbr]) * 100) * .01), ".")) + 9))

I hope this helps anyone who ends up in this situation.

Re: Error with double decimal Calculated Values

Hilary Stoupa — Mon, 10 Jun 2013 23:54:57 GMT

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.

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 23:48:07 GMT

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?

Re: Error with double decimal Calculated Values

Hilary Stoupa — Mon, 10 Jun 2013 22:56:10 GMT

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

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 22:42:49 GMT

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.

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 21:24:33 GMT

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.

Re: Error with double decimal Calculated Values

Hilary Stoupa — Mon, 10 Jun 2013 21:07:24 GMT

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?

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 19:54:22 GMT

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

Re: Error with double decimal Calculated Values

Hilary Stoupa — Mon, 10 Jun 2013 19:27:54 GMT

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.

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 19:00:09 GMT

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.

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 18:56:32 GMT

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.

Re: Error with double decimal Calculated Values

Hilary Stoupa — Mon, 10 Jun 2013 18:39:05 GMT

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

Re: Error with double decimal Calculated Values

CCBSMF — Mon, 10 Jun 2013 17:58:51 GMT

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.