Number expected error in Lotuscript (Incorrect data type for operator or @Function: Number expected)

This entry was posted in Programming Web on by

I came across this when attempting to add some simple percentages to a Lotus Notes 8 form with columns of numbers totalled at the base. The totals were then calculated into a grand total and the percentages were to show the breakdown of each of the subtotals.

On opening the form (not on previous forms with data on them) I get incorrect data type for operator or @Function: Number expected for the first field where I was calculating the percentage..

Now given that I thought I’d done this a dozen times before – it seemed that something had changed….. last time was in an earlier version of Notes ……

So Lotus’ explanation ..

Problem

When you attempt to open or create a document one of the following error occurs:

“Field ‘‘: Incorrect data type for operator or @Function: Number expected”

“Field ‘‘: Incorrect data type for operator or @Function: Text expected”

“Field ‘‘: Incorrect data type for operator or @Function”

In one customer’s application, the following field formulas resulted in the error when a document was composed.

FieldA: 0
FieldB: 0
FieldW: FieldA/FieldB
FieldX: @If(FieldZ=””;”@Error”; ….)
FieldY: FieldW-FieldX

This same error did not occur in Lotus Notes releases prior to Notes 6.0.

Solution

This behavior occurs in cases where the field’s formula attempts to perform an arithmetic operation with a text value and a value that has evaluated to @Error, which includes a referenced field that contains the @Error function itself. The behavior occurs in Notes 6.0.2 CF2 and later releases.

In the customer example above, FieldW evaluates to @Error and FieldX the text “@Error”. When FieldY attempts to perform the calculation @Error-“@Error”, then the error message occurs. Note: The issue would occur regardless of the text within FieldX.

This issue was reported to Quality Engineering in SPR # KHIA6GL59B; however, it was determined that Notes/Domino is working as designed. The error message returned is expected.

Note: It is also expected that when a formula directly references the @Error function, the formula results in @Error regardless of any further arithmetic operations. In this scenario an error dialog is not expected.

For example, in the scenario below FieldB will result in @Error, but no error dialog appears.

FieldA contains a formula which calculates to 0/0, which will result in a @Error value.
FieldB contains the formula: @If(@IsError(FieldA);@Error;FieldA)+” some text”

Workaround

Formulas should be written to return values corresponding to the field’s type, as follows:

– If the relative field is a numeric field, then the formula should not be referencing text in its formula.

In the customer example above, the resolution is to modify the formula so that it no longer contains quotes around the Then action, so that FieldY returns a numerical (although error) value rather than Text: @If(FieldZ=””;@Error; ….).

– If the relative field is a text field and there is the possibility that a reference may return an error, then use the @IsError to check for this condition.

For example, let’s say a field’s formula is designed to display some text and refer to a field containing sales numbers. The second field’s calculation could return @Error if no sales occurred that month. The first field’s formula could be constructed to check if the sales field is error or not:
“Sales this month: ” + @If(@IsError(salesnumbers);”0″; @Text(salesnumbers))
Supporting Information
The variations in the error message will occur as follows:

“Number expected” when the formula refers to: error-text or error+text
“Text expected” when the formula refers to: text+error
No additional text when the formula refers to: text-error

The behavior described in this document does not pertain to Notes/Domino releases 6.0 and 6.0.1. For additional details on behavior relative to those releases, refer to the following document: “Use of @Error Function Results in Runtime Error and/or “Error Specified in Formula”” (#1116464).

For additional details on issues involving unexpected behavior and error results in Notes/Domino releases prior to 6.0.2 CF2 and 6.0.1 CF3, refer to the following document: “Notes 6 Comparisons, Conditionals, HideWhens, and Selection Formulas that Result in an Error Have Unexpected Results” (#1107706).

So in my case – I needed to amend the calculations from

@If(TS_SupportHoursTot=””;0; (TS_SupportHoursTot/TS_TOTALHOURS)*100)

from

(TS_SupportHoursTot/TS_TOTALHOURS)*100)

for these computed fields

(Although equally you could have

@If(TS_AdhocHours_Tot=””;@ERROR; (TS_AdhocHours_Tot/TS_TOTALHOURS)*100)

with the @error appearing in the field instead of 0 if appropriate.