Saturday, October 1, 2011

When is 100 not 100?


By Mick Moignard

Question: When is 100 not 100?
Answer: When it's calculated by Notes.

This one started when a customer called me to report something very strange in an application we've done for them. The application, among other things, creates bills. Being in the insurance industry, this bill may need to be spread around several customers.

The form has the ability for the user to split the bill up to five ways, and for each split, to define a percentage of the total -- up to 4 places of decimals, too.

The app helps the user get the bill split set up by totalling up these percentages. It stores the total in a computed field, and then uses two computed text fields to display the result -- a red-coloured one when the total isn't 100, and a black one when it is.

The call was to say that they had a display of a red 100. This wasn't supposed to happen. So I investigated.

First off, I looked at the computed field formula for the red field:

@If(SplitPercTotal != 100;SplitPercTotal;"")

That looked pretty clear. If SplitPercTotal was 100, then the computed text would be "". But a red "100" was being displayed.

Then I checked the formula for SplitPercTotal. That's also pretty clear:

s1:= @If(splitperc_1 = "";0;splitperc_1);
s2:= @If(splitperc_2 = "";0;splitperc_2);
s3:= @If(splitperc_3 = "";0;splitperc_3);
s4:= @If(splitperc_4 = "";0;splitperc_4);
s5:= @If(splitperc_5 = "";0;splitperc_5);

Next up, get the split values from the customer, and try those on my test copy. Sure enough, the split values he gave me: 29.97, 59.94, 9.99 and 0.1 totalled up in the app to 100, and displayed it in red, which at least meant that I could reproduce the issue.

I tried instead 29.97, 59.94, 9.89 and 0.2. Those gave me a black 100.

This was time for some serious digging -- which for a while led mostly nowhere. In the end, I crafted a LotusScript agent that showed me what exactly was going on.

Let me walk you through some snippets from it, and hopefully at the end of this piece EditorDave [yep, will do!] will list the complete text for you to try it yourself.

I started off creating a NotesDocument, and added some fields with these four values to it:

Dim sess As New notesSession
Dim y As single
Dim x As Single
Dim doc As New NotesDocument(sess.CurrentDatabase)

doc.num1 = 29.97
doc.num2 = 59.94
doc.num3 = 9.99
doc.num4 = 0.1

Then I totalled up those values:

x = doc.num1(0) + doc.num2(0) + doc.num3(0) + doc.num4(0)

And looked at it with the 'Script Debugger. X contained 100.

I also tried, to be sure:

x = 29.97 + 59.94 + 9.99 + 0.1

And x still contained 100. But the form in the application used @Sum, so next I coded:

Dim z As variant
z = Evaluate("@sum(num1:num2:num3:num4)", doc)

Still, Z stubbornly contained 100. Starting to question a few things now. The only thing I could think was that somehow, the result wasn't actually 100, but was very, very close to it, and the debugger wasn't telling me the full story.