[Solved] Connect spreadsheet to auxiliary database fails

[ErikJan]

Today I tried to do a mailmerge in OOo 3.2. In the letter I did not see my fields, and in the auxiliary database that is connected to my spreadsheet many columns remain empty, although they are filled with calculations or even simple texts in the original spreadsheet. I solved the p[roblem by reverting to version 3.1.1, although I prefer have my mailmerge working in the newest version.

Does anybody know what is wrong? Is something wrong in my configuration with the new version, or is the new version faulty?

Greetings,

Erik Jan.

[ErikJan]

I tried uninstalling the older java version (6.16) and installing the newer one (6.18), but this did not make a difference. When I open a document for a mail merge the registered database that is derived from a spreadsheet does not have all fields of a record filled with the content that is in the cells of the spreadsheet.

Experimenting I once got a derived/connected database that did have all fields in every record, but when I tried to replicate this success, I failed. So I believe the new version 3.2 of Openoffice.org is faulty. I have gone back to version 3.1.1.

Greetings,

Erik Jan.

[Villeroy]

Your calculated fields must not mix numbers with text. That's all.

[ErikJan]

Dear Villeroy,

I am afraid I do not understand what you mean. All calculated cells are either numbers, or text, and it is the same with the data that are used in these functions/calculations. In my spreadsheet there are columns with dates, that is: numbers. The dates do not appear in the derived database. A calculation made from these dates does appear, but other calculations do not appear. In the derived database all fields are either numbers or text, but not a mishmash of both in one field. In version 3.1.1 everything is normal.

Thank you for trying to help.

Erik Jan.

[Villeroy]

It is quite common practice in spreadsheets to do something like:
=IF(B1;A1/B1;"") [divide A1 by B1 if there is a non-zero number in B1, else return empty string]
This creates a column of mixed numbers and strings, which is not valid in any type of database.

In version 3.1.1 everything is normal.

Not quite. In my version 3.1.1 Base all values as strings, sequences of digits and empty strings, which displays the numbers just fine as long as you do not need to calculate with them. When you drag the table back into a sheet it imports as text, in the database you can not apply any number format. Additionally, Writer's date fields do not accept these strings as valid dates.

OOo 3.2 seems to invalidate the whole column.

=IF(B1;TEXT(A1/B1;"@");"") turns the whole column into text. You may replace the "@" with any number format string borrowed from the number format dialog.
=IF(B1;A1/B1;0) turns the whole column into numbers.

Whatever solution you prefer, you may want to add a simple query based on that table which filters out all the zeroes or empty strings respectively. Such a simple query can be used instead of the table.
In SQL view such a query looks like
SELECT * FROM "Sheet1" WHERE "Quotient"<>'' [the text solution]
SELECT * FROM "Sheet1" WHERE "Quotient"<>0 [the numeric solution]
Double quotes around names of tables and columns, single quotes around literal text ( '' is empty string)

Using a true database in the first place could prevent all this trouble. Spreadsheets are the worst, but most commonly used list keepers.

I filed issue http://www.openoffice.org/issues/show_bug.cgi?id=109726

[ErikJan]

Dear villeroy,

I see what you mean. I do not know how to adapt to this new restriction, because only part of the spreadsheet is "database". Calculations and functions are to the right of these columns, and under the "database" are the data for VLOOKUP. The "database" part is not yet filled up, so that there are at the moment about 20 rows with data, twenty records, and under these there is room for 30 more. You are right, the columns with dates and other numbers also contain empty, not yet filled, cells. It will be quite a lot of work to change the whole spreadsheet. I no longer have a tar.gz file of version 3.1.1, but perhaps I can find it on the internet somewhere and download it for later use, when my distribution (Mandriva Linux) has adopted the new version. Alternatively I could install wine and use MSOffice 2000.

Thank you for your clarification, and for reporting this as a bug. I hope the developers will pay attention to it.

Greetings,

Erik Jan.

[Villeroy]

Could anybody please extract the attached zip in one directory, open the database table in the odb and confirm the following with version 3.2?
- Q_Mixed is entirely blank (it used to be entirely text in previous versions)
- Q_Num consists of numbers entirely with number format currency [right-click column header, all column formats availlable]
- Q_Text consists of text entirely [right-click column header, column format text only]

Queries:
- Query qA_EmptyString returns an empty row set
- Query qA_Null returns one row with comment "Blank cell in A"
- qMixed_Null returns an empty row set
- qMixed_Empty_String returns 2 rows with comments "Empty String in Q_Mixed and Q_Text" and "Blank cell in B"
- qNum<>Zero returns 8 rows with no gaps (except in comments)
- qText<>Empy_String returns 9 rows

I see what you mean. I do not know how to adapt to this new restriction, because only part of the spreadsheet is "database". Calculations and functions are to the right of these columns, and under the "database" are the data for VLOOKUP.

We talk about different things. A spreadsheet can never be a database, even when you misuse it as such by means of VLOOKUP.
In a true database (not connected to a spreadsheet) you could link tables in a way which makes all lookups obsolete. A spreadsheet connected database makes your spreadsheet lists appear like a database, so it makes absolutely no difference if your serial letters are merged by a database server or spreadsheet or plain text files.

The attachment should demonstrate the problem with version 3.2 in a general way.
Columns Q_Num and Q_Text should solve the problem in the spreadsheet.
Queries qNum<>Zero and qText<>Empy_String refer to the respective numeric or textual solution, filtering out unwanted rows.

You are right, the columns with dates and other numbers also contain empty, not yet filled, cells.

I bet, there are at least some cell with space or empty strings. Formula =ISBLANK(A1) tests if a cell is really blank.
A database can be set up to reject incomplete records, so you can always be sure that a column is complete with nombers, dates, times, booleans only and that your "lookups" never refer to non-existing items in other tables.

[Villeroy]

Instead of Wine with MSO you can simply add another calculated column with
=IF(ISTEXT(X2);X2;TEXT(X2;"YYYY-MM-DD")
which gets any text from column X or in case of a number the text which represents that number as ISO-date. Replace YYYY-MM-DD with any number format string you like.

Alternatively:
=IF(ISNUMBER(X2);X2;0)
which gets all numbers (dates) replacing any text with 0.

[ErikJan]

Dear Villeroy,

you helped me a great deal. I removed any space that might accidentally be in one of the cells supposed to be empty and some comments in cells of the spreadsheet that must become numerical fields in the derived database. Now I can do my mailmerge again even in version 3.2. I shall keep the old version for the time being however, since it works without all these troubles/restrictions.

Thank you very, mery much.

Erik Jan.

[Villeroy]

Congratulations. It seems to be common practice to "clear" spreadsheet cells with a space. Backspace is the default key to clear all cell contents.