Re: [office-formula] Conversion to Logical from Text

["David A. Wheeler" <dwheeler@dwheeler.com>]

Thankfully the conversion stuff is isolated to one area of the spec, so we can address this stuff once.

Eike:
> My recommendation for all those cases, also in OOo, would be to generate
> an error whenever text is to be converted to number or logical. Doing so
> would at least clearly indicate to the user that something in the
> document was setup in a way it's not supposed to. The way things are
> converted now make it work sometimes and may fail in other cases.

But MANDATING that in the spec would make it impossible to implement an ODF-compliant app that could automatically handle the many spreadsheets that DEPEND on automatic conversion from text to number (for example).  We're not REQUIRING implementations to do that, but we've also been careful to not FORBID it.  Excel and Gnumeric currently do such automatic conversions, and I doubt that Gnumeric will stop doing so in the future.

Indeed, at this late date, I'm wary of being TOO harsh on implementations; it's easier to mandate a specific algorithm once there's widespread agreement on it, but currently it appears that there is NOT such an agreement... so it's better to make it clear that this is implementation-defined.  I'd rather give a little excessive wiggle-room than make a clear mandate of the WRONG thing.

So, I believe we should handle "convert to logical" the same way as we currently handle "convert to number".  Basically, it's implementation-defined, with one of 3 permitted options: 0/False, error (always), or an "attempt to convert" (which may produce an error).

We do that already for Convert to Number; here's that section:
Text: An application may return 0, an error value, or the results of its attempt to convert the text value to a number (and fall back to 0 or error if it fails to do so).  Applications may apply VALUE() or some other function to do this conversion, should they choose to do so.  Note that portable spreadsheet files cannot depend on any particular conversion, and shall avoid implicit conversions from text to number.

I think we should have similar claims in 'Convert to Logical'.

Here's what I propose for 'Convert to Logical' (correcting the "level 3" stuff):

=======================

Text: The specific conversion is implementation-defined; an application may return False, an error value, or the results of its attempt to convert the text value to a logical value (and fall back to False or Error if it fails to do so).  Note that portable spreadsheet files cannot depend on any particular conversion, and shall avoid implicit conversions from text to number.

(NOTE: A typical algorithm for converting a text value to a logical value is to determine if the text matches the text "TRUE" ignoring case, then return TRUE, if the text matches the text "FALSE" ignoring case, then return FALSE, otherwise return Error. Typically case is ignored no matter what the value of “case-sensitive” is.  It is implementation-defined what happens if the text does not match “TRUE” or “FALSE” in a case-sensitive manner- it may return a logical value or an Error.)

{We should add the prefix "The specific conversion is implementation-defined" to the text about Text->Number too, so that searches for the term "implementation-defined" will reveal it.}

--- David A. Wheeler