Operand Type Gotcha

The Balance History from Date form previously had a bug in the case where a credit balance in the running balance column then gets a further credit added to it. The result is that the credit balance behaves as if it were positive and the running balance is thrown off.

The reason for this turns out to be that the running balance (Sub_Total column), uses values from other columns whose result type is inconsistent.

Sub_Total = OPENING + SubTotal("List.Debit") - SubTotal("List.Credit") + Debit - Credit

The Debit column is defined thus:

if(Transaction.AccRecMove >= 0, Transaction.AccRecMove, "")

i.e. when the transaction is a credit, the value of the Debit column is not a number, but a string: "". This is because we want the column to be blank in that case.

While the subtotals work just fine (they treat non-numeric values as zero), the second "+" in the Sub_Total formula is problematic when the Debit column is a textual value. This is because addition where one operand is text and the other is a number will convert both operands to text and perform a string concatenation†.

So

-100.35 + "" - 446.96

The left hand side of the addition is "promoted" to text so that it can be concatenated with the right hand side text "". The column format is used*.

"100.35 CR" - 446.96

Now we have a subtraction of a number from a text string. In this case, the text string on the left hand side is converted to a number, but this conversion is done by the operating system's number parser and does not respect the "CR" suffix — it just looks at the digits. The result is that the left hand side loses its negative sign.

100.35 - 446.96

And we get result 346.61 CR, when we should have got 547.31 CR.

In general, the safe thing to do is avoid doing any numeric arithmetic where one of the operands might be a text string. The solution in v6.1 is that the Balance History from Date form uses separate columns for calculating Debit and Credit and for displaying them.

The calculation (in a zero width column) is:

Debit = if(Transaction.AccRecMove >= 0, Transaction.AccRecMove, 0)

And the visible column is

Disp_Debit = if(Debit, Debit, "")

The Sub_Total column can then operate on values that are consistently numeric and need no unreliable text -> number conversions.

†possibly overloading the + operator for string concatenation as well as numeric addition was not the wsiest of ideas, but we are stuck with it.

*note that the simple fix to the problem is not to use the "CR" numeric format. a negative prefixed by a "-" is parsed fairly reliably (although that may be not be the case in all international number formats).

Posted in Esoterica | Comments Off on Operand Type Gotcha