[Solved] Show text in field in query depending on boolean

[MattB]

Hi all.
I'm new to OOo and databases in general and therefore still a little confused.
I'm not sure where to find the solution to my problem, as I'm not sure where my problem lies!

I created a members list that holds personal information (name, address etc.) about each member as well as certain preferences they defined (preferred location, preferred building type etc.)
In the form I use for entering data these preferences are made up of Boolean Fields, like "rural", "urban", "central" and "detached", "semi-detached", "terraced" and "apartment".
I thought this would be the simplest option for a multiple selection.

My problem now is: In my report I would like to have 2 columns named preferred location and preferred building type that list the texts "rural", "urban", etc. depending on if the relevant box was ticked or not. (instead of having all fields separat showing a ticked or un-ticked box.)

I created the fields "preferred location" and "preferred building type" (text [varchar]) in my table,
but I don't know how to tell them to display the above.

Would be great if someone could help me with that, or point me in the right direction. I suppose this has come up before. (couldn't find an answer )

Thanks a million. Matt

[Villeroy]

One table of persons [ID, name, birth,...], another table of preferences [ID, name], a third table which maps person IDs and preference IDs, a form with subform and listboxes to edit the relations. Topic: Creating form for many-to-many relationship

[MattB]

Hi Villeroy.

Thanks for the quick reply.

I'm not too familiar with Base yet, so bear with me for a second:
1. is it really necessary to create separat tables? (thought that wouldn't be necessary in my case)
2. will listboxes allow me to have multiple selections (say: rural and urban)?

Will check out the link you sent straight away....

[Villeroy]

I'm not too familiar with Base yet, so bear with me for a second:

No excuse. Database construction has nothing to do with editing office documents. Base is a development tool when you create a database with it. Otherwise Base is a more simple bridge between existing databases and office documents.
When your create a database you have to follow some abstract rules in order to construct a functional database. There is absolutely no alternative to the old fashioned hard way of learning. Base is far too simplistic to be an intuitive freehand tool. It simply assumes that you already have a well formed database with all the tables, fields and relations. It does not help you how to create such a structure. If you have such a thing, it lets you add fairly usable input forms (far from perfect) and it lets you create office documents with database contents..

[MattB]

I started creating a database with Base and I suppose the hard way of learning is the only way for me to find out if it's the right tool and how deep I have to get in!

What I had done so far was creating a table with all the fields of information I need for the respective members. Having looked at other tutorials I gathered a single table holding all the information is the most logical and simple way for my scenario as the combination of preferences is fairly unique to every member. (compared to the movie - genre example) I also had created a form which allows me to input all this data in a fairly simple way.
I thought the problem I had described in my first post was a matter of 'output' as it was refering
to the way my data was displayed in a report: How to use the true-false statement of a field to display a certain value or text in another field.
It would be great if this would be possible without becoming an expert, but it is not essential for the workings of my database.
I just had a read through the topic you just send me. The issue there seems to be the form and subform itself, but I'm happy enough with the way my form looks and what it displays.
____

Would be great if you - or anybody else - could let me know what would be involved to achieve this, or if I would need to get in too deep for my requirements.

Thanks, Matt

[Villeroy]

You work with 2 different applications when you connect a "Base document" to a new database which is wrapped into the odb-file.
The database application (backend) is http://hsqldb.org, the database access tool (frontend) is openoffice.org.

HSQLDB is quite a vanilla RDBMS which supports the very same Structured Query Language like any other database since the early 90ies. All relational databases are managable through the same simple language and Base offers a simplistic (incomplete) GUI which issues SQL statements to the underlying database.
All relational databases are built on normalized tables. A very short introduction by Microsoft
Having a relational database with normalized tables, fields and relations, OpenOffice.org offers to attach form controls to office documents, so you can edit your database and you can import database contents into office documents. Since version 2 you can wrap such input forms and reports together with query definitions in a single "database document" (a zip archive with some configuration and Writer documents). As a questionalble gimmick you may wrap an entire HSQLDB into the same file. OOo comes with it's own HSQLDB server to extract and connect these wrapped databases with OOo.

This is what it's all about. Anybody being familiar with some commercial database, say Oracle or MySQL, can connect a "Base document" to his Oracle server, add some queries to retrieve task oriented data sets, use data sets in arbitrary office documents, create special Writer documents (reports) embedded in the "database document" and input forms. Well, there might be far better input forms for Oracle databases, but OOo offers them as a nice extra and they are not too bad unless you are after the perfect database application.

[MattB]

Look.
Thanks very much for the lecture.
But that doesn't help me a bit with my issue. If you don't wont to help me, that's ok.
Just don't reply. So you save your time and I save my time!

I tried to learn something new. I had also seen a tutorial which dealt with a similar scenario. A simple members list. Only 1 table that held all the information. The guy used it for organization:
Letters, Birthdays etc. Simple queries and reports. That's exactly what I'm looking for.

I'm well aware that this is the lowest end of what can be done with databases, but that's all I need and all I'm interested in.

CAD programs can be used for all sorts of things too. From 3D to Quantities and a lot of other things.
If my neighbour wants to draw up his garage I don't lecture him on the history of CAD and all the things that can be done with it!

Aber vielleicht ist das ja ne persoenliche Einstellungssache....

Ich bin hier auf dieses Forum gekommen um Hilfe mit ersten Schritten in Base zu bekommen und denke, dass ich hier eigentlich richtig bin. Vielleicht ist ja jemand anderes bereit mir zu helfen.

Maybe someone else can help me.

Thanks, Matt

[eremmel]

Most people active on this list are enthusiastic users with lots of experience. We are eager to let you make a sound implementation and have gone through many pitfalls ourselves. Also database design is a kind of engineering and that needs a more fundamental approach. But we can give you what you are asking for. BTW the example you are giving about the check boxes, looks to me as mutual exclusive selection so a list box might be more appropriate. But here is the way to go according your question.
You have to define a VIEW and not a QUERY with the following to translate a series of booleans into a concatenation of strings

Code: Select all   Expand viewCollapse view

SELECT ...
, CASE "b_rural" WHEN true THEN 'Rural' else '' end || CASE "b_urban" WHEN true THEN ' Urban' else '' end as "Pref.Location"
...
FROM ...

Here the field name "b_rural" is translated into the word Rural when true else into an empty string. This is glued with '||' the next field and can be extended to all the values you like.
Note that you need to save this view as a "Run SQL Command Directly" type of query: enable the checkbox with SQL and the green V.

[MattB]

Hi eremmel,

thanks for the reply.
I thought already it would be some kind of WHEN CASE command.
Just wasn't sure how and where.
The events are not mutual exclusive. It is possible to pick eiher or all of the preferences.
That's why I thought a list box wouldn't be the right thing.
Will try to get my head around this.

Again. Thanks a million for your help.

Matt

[Villeroy]

I thought already it would be some kind of WHEN CASE command.
Just wasn't sure how and where.

Look at the status bar of your database. You are working with a HSQLDB which is fully documented in http://hsqldb.org/doc/guide/ch09.html

The events are not mutual exclusive. It is possible to pick eiher or all of the preferences.

This is called a many-to-many relation in database terms and I outlined how it has to be implemented in Base.

[MattB]

Hi eremmel,

thanks a million for your help. Got it sorted

Matt

[MattB]

BTW eremmel.
I can run this code as view and as query.
Both is working and seem to deliver the same result.

Matt