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

[Eike Rathke <erack@sun.com>]

Hi David,

On Wednesday, 2007-03-21 13:34:37 -0500, David A. Wheeler wrote:

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

Except for the exceptional behavior of AND and such..

> 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.

Sure. That's why I would make it a RECOMMENDATION instead of MANDATING
it.

> 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.

That's where I pulled my proposal from ;-)

> 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.

Here's what I have now:

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 (ignoring case) to a logical value (and fall back
to False or error if it fails to do so). Conversion depends on the
actual locale the application runs in. Note that portable spreadsheet
files cannot depend on any particular conversion, and shall avoid
implicit conversions from text to logical. The recommended behavior
would be to generate an error for an attempted conversion.

I added the sentences about locale dependency and the recommended
behavior also to "Convert to Number".

Is that ok with the recommendation?

> (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.)

Added. I also added a table that illustrates the behavior of different
applications, needs to be updated with the results from Kspread 1.6,
which I don't have.

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.