Apache OpenOffice Community Forum

I have created a form to put a new adress into the database.
There is a field "information" that should be "Y" for yes or "N" for no.
I want to press the lowercase letter "y" and convert it automatically to uppercase "Y" before sending to the database.
I tried to use the macro capitalise.bsh in different actions but it doesn't do anything.
How can I solve this problem?

Change the type of the underlying field to VARCHAR_IGNORECASE, so "y/n" or "Y/N" does not matter. What if someone enters neither "y" nor "n" but any other letter? Are you shure you don't want a boolean field, which allows two states only?

The solution with the VARCHAR_IGNORECASE would work but it looks not fine in the report.

But you're right, actual I need a binary field. But I imported this char from an Excel-sheet.
Now I've changed the "y" into "1" and converted the field into integer.
Then I wanted to convert into fix binary but I got a message, that the field couldn't be converted.
I had to delete this field an add at the end of table.
So I tried this and now I have the text <OBJECT> instead of my data in the database.
I created a check box in my form but this insert no data too.


What can I do?

Your report could use a query with a calculated field:
SELECT "Field1", ..., UPPER("FieldX") AS "Info" FROM ...

Binary is for pictures and other things you would store as a separate file otherwise. Boolean fields allow two values Yes and No. In a form they appear as check boxes.

Thank you I confounded boolean with binary.

Now I tried to use this query in my report, but where can I put the query?

Test this query with all double-quoted names adjusted to your needs:
Queries>Create query in SQL view...
Querie's menu: Edit>"Run SQL command directly" (gives you all the functions of the underlying HSQL database engine, avoiding the lousy Base tools). returns all fields from "Table1" plus a calculated field named "Bool" having TRUE for all 'Y' or 'y' in a field named "YN". All other values including Null (empty) return FALSE.
Main menu:Tools>SQL... allows you to run queries to manipulate the database. adds a boolean column named "Bool" to "Table1" with a default value of False. Puts a boolean True into new field "Bool" where the field named "YN" is 'Y' or 'y' (derived from our first test-query) now we have filled all "Bool" with True/False values we can make "Bool" mandatory for all new values to come. Otherwise the boolean field can have True, False and Null (empty). Finally we may drop the old "YN" field.
I'm not shure how to change the source of a report. You may replace your report with a new one.

Edit: If you want to map True/False to words Yes/No, use a query as source of your report:

So you end up with a boolean field which allows only for 2 values True/False, putting a default value as specified on field creation. You can map those two values to arbitrary text or numbers by means of queries to be used with reports.

Thank you for your detailed answere, but your query

SELECT *, CASEWHEN(UPPER("YN")='Y',TRUE,FALSE) AS "Bool"FROM "Table1"

creates a syntax error.
The mistake is the *. Without the * or replaced by a field the query works correct.

I solved the boolean problem similar by creating a new boolean column. Then I cleared the column with an update "set = 0".
Then I made a query because I have until now only 'Y'

Now to the last problem:

I'm not shure how to change the source of a report. You may replace your report with a new one.
Edit: If you want to map True/False to words Yes/No, use a query as source of your report:

When I want to create a report I have only one item to select:
I can only use an assistant.

My last open question is:
How can I use a query for my report?
Thank you

How can I use a query for my report?

Store the query which includes all the fields and rows you need for the report.
When I call "Use wizard to create report...", the first step of that wizard shows a list of all tables and queries to choose from.

Great, that's it.
Thanks.