28 Maret 2012 16:39
I have a field that is calculated based off 4 other fields. The formula is on the Variance field, and equals: Proof - Card - Non-Cash - Deposit. I have all fields characterized as US Dollars, and all are limited to 2 decimal places. Even still, I will occassionally get a variance that should equal 0 (or some nice number), but I get a very long scientific notation number. For example, in the picture shown below, the variance should equal 0, but instead calculates to 4.547435088e-13
Any idea how I can prevent this from happening and ensuring they are all rounded to 2 decimal places?
28 Maret 2012 17:11
Hightlight the field -> Text Box Properties -> Format -> specify your requirements. here's a snapshot:
28 Maret 2012 17:18
Thanks for the reply.
That's how I have it set up right now, but the problem persists.
28 Maret 2012 17:34
Can you check and provide formula and formating of the variance field?
Following is what I get using the same values you've used (i'm using repeating table):
And here is the formula/format of the last field
28 Maret 2012 17:41
28 Maret 2012 17:46
Do you have access to defintion of "CurrencyType"?
28 Maret 2012 17:51
I'm not sure what you mean by "definition," but it is a site column - I selected currency for the type
Site column settings shown below
28 Maret 2012 18:49
In the past, I’ve run into some issues while doing calculations on Currency Fields. I believe it has something to do with how the system saves currency field (using comma) and issues encountered during internal conversion which is required for numeric computation.
If you are gonna do calculation on fields using InfoPath (and not use sharepoint "calculated value" option), I would suggest you to define them as Number field instead.
Probably not the answer you were looking for, but give it a shot.
30 Maret 2012 0:41
round((my:f1 - my:f2) * 100) div 100
jliu - http://johnliu.net - http://sharepointgurus.net
02 April 2012 19:11
Thanks Sanjeev, that worked perfectly. I changed the site column to a number field, but kept the InfoPath field as a currency, that way the calculation is working properly, but the field is still displaying as a currency.
03 Mei 2012 21:57
I thought this worked, but I've found an error is occuring at times.
If the variance is not equal to 0, they must fill out the "over/short" section of the form. There have been times where those fields say they are required even though the variance section displays as a 0.
Also, the "Total Variance" adds all of the variances together. Sometimes they will all show as 0, but the total displays as "NaN" because of an error. The information given above is still accurate - does anyone have any ideas as to why I'm getting this error and how to fix it?
03 Mei 2012 22:05
The following shows the form with the variance automatically calculated (proof list - card trans - non-cash pmt - deposit amt)
It shows the Over/Short Section at the bottom as required
I manually entered the variance as 0 - the Over/Short Section is not required
10 Mei 2012 21:04
For anyone interested, I was finally able to fix it. I just set a rule on each variance field that stated:
If variance < 0.009 AND variance > -0.009
Then set value = 0
- Ditandai sebagai Jawaban oleh Brett N 13 10 Mei 2012 21:04