[Example] Relations reflected by list boxes in forms

[Villeroy]

Covering several of the most common misconceptions regarding relations in Base forms:

1) Base is a database program.
No, it isn't. HSQLDB is a relational database that can be wrapped into a Base document. You can connect your Base document to many other types of relational databases that are not wrapped into the document. Base is a tool set to work with databases in the context of this office suite.
Documentation of the built-in HSQLDB up to version 3.3 3.4: http://hsqldb.org/doc/guide/ch09.html (still the same in AOO 4.1)
Documentation of HSQLDB for future recent versions: http://hsqldb.org/doc which is the most recommended but not embedded database to be used with OpenOffice. But you are free to use MySQL, PostgreSQL, Oracle databases or whatever.

2) It is a good idea to build a nice looking form and attach a set of table data to the form.
Think the other way round. Base (sub-)forms and form controls are designed to reflect the relations of any relational database you connect at it. A relational database is a rigid structure of strictly typed fields. Base forms are a minimalistic set of tools to build fairly usable (rather than perfect) user interfaces for editable relations.
It is impossible to do anything useful with Base forms without having a perfectly normalized relational database.

3) The form wizard can help me to build a form to edit related data across tables
The wizard can reflect a single one-to-many relation with the one-side in main form and the many-side in the subform. Anything else requires a list box or a group of list boxes bundled in a table control. The form wizard never creates any list box. You've got to learn how to use the "Form Design" toolbar together with the controls on the "Form Controls" toolbar and its extension the "More Controls" toolbar.

4) Macro code can help me to build a form to edit related data across tables
This forum proves that this is an illusion unless you are an experienced programmer willing to spend a lot of time studying the API (application programming interface). Even then you won't get anywhere when you work against common rules of relational database design.

5) I need a combo box to select items from another table
No, you always need a list box to select items. A combo box is just an advanced text box with auto-complete. A combo box can be useful when you keep repetitive data in a field without implementing any relation. Sometimes simplicity is more important than integrity, but only sometimes.
Contrary to a combo box, a list box has two fields: A visible field (index #0) and a hidden field (bound field, mostly index #1). The visible field lets you pick another table's item by name, the bound field is the respective primary key of the selected item which gets written into the foreign key of the form's current record.
The recipe for a working list box is always the same:
Properties of your list box, tab "Data"

Variant #1:
Linked Field: XID [the foreign key where the other table's primary key gets stored]
Source type: SQL
Source: SELECT "Field A" AS "Visible", "Primary Key" AS "Bound" FROM "Your List" ORDER BY "Visible".
Bound Field: 1 [the other one would be 0]

Variant #2
Linked Field: XID [the foreign key where the other table's primary key gets stored]
Source type: Query
Source: MyListBox [name of the query SELECT "Field A" AS "Visible", "Primary Key" AS "Bound" FROM "Your List" ORDER BY "Visible"]
Bound Field: 1 [the other one would be 0]

For both variants, the visible field can include any calculated or concatenated string such as "Surname" || ', ' || "Forname" AS "FullName"

6) List boxes can filter record sets
A list or combo box selects a value into a field rather than selecting rows from tables. The form based filter provides a built-in but slightly clumsy method to filter by list box. The text fields in the attached database forms turn into list boxes when you turn on the form filter mode (3rd last button on the "Form Navigation" tool bar).
More on filtering: http://dl.dropbox.com/u/10552709/FilterExamples.zip

The ultimative Base tutorial: http://openoffice.org/projects/document ... 20tutorial
[Tutorial] Forms in OpenOffice.org Base helps to analyse and reproduce the forms in my attachment.
[Tutorial] Creating a form in Design view helps to analyse and reproduce the forms in my attachment.
[Example] Filter/Search with Forms (leveraging SubForms)
The attached Base document contains an embedded HSQLDB, a set of 4 tables constituting 2 relations one-to-many and many-to-many, 2 simple queries, 3 form documents with a README form and a simple grouped report from one of the queries.
In this context the report is not that important. It requires the report builder extension installed. Query "qReport" is a most simple demo how to merge data from all the tables into a virtual table. The report generates a printable Writer document from that record set. You may also create another report using the built-in simple report wizard or you may drag the query icon into some Writer or Calc document.

[mhatheoo]

well villeroy, thanks for sending this nice introduction,
but I think this is really the very beginning, a little too few,
as the abilieties of HSQLDB are much ahead of this,
so I am not that much fond of reading you (personal) statements
about the restrictions within OOO-Base.

Martin

[RoryOF]

If you do not wish to read any particular person's postings, adjust the settings in your Forum User Control Panel. As you are not getting answers to how to do what you want, consider that it may not be possible.

[ubiquity]

Thank you for these important considerations Villeroy.
As an MS Access user from its beginning and RBase & dBase before that, the information here is invaluable.
Although I enjoy using MS Access (poorly) it is now getting beyond my (virtually retired) pocket.
To be fair to MS Access it is 'flexible' but that really means that you can be a bit 'sloppy' with your design (particularly with regard to normalisation) so I was a little under-prepared when taking my first tentative steps with Base as the interface to HSQLDB.

In particular I, and I suspect many other Access users, was used to being able to use combo boxes with multiple visible fields and selectable bound fields.
It was extremely useful to find the explanation of List Boxes for that purpose here, especially given the limited and specialised use of list boxes in access by comparison.

I am progressively going through the other material you have listed and uploaded to begin to refocus my mind on good design first, before attempting to build systems in an ad hoc fashion as one is tempted to do in Access.

Thanks again.

[papijo]

Hi Villeroy and thanks for your detailed tutorial and the concrete example attached as relations2listboxes.odt.

Just wanted to let you know that the multiple select table Many T_IDS needs some refinement. It does not prevent the user from adding more than one identical "Thing" element from its dropdown list. As a consequence, if user navigates to the next (or previous) record, that extra element is automatically and silently removed, which is OK.
However, if, instead of navigating to another record the user clicks the Save record button, an error message is issued:
"Error inserting the new record. Violation of unique constraint SYS_PK_85: duplicate value(s) etc."
Any suggestions?
Thanks in advance.

[clavisound]

@Villeroy THANK YOU, you help is top-of-top.

One remark. I try to migrate from hsql to mariadb and I had problem with concatenate.

You write:

For both variants, the visible field can include any calculated or concatenated string such as "Surname" || ', ' || "Forname" AS "FullName"

Which is right for native engine, but whith mariadb it fails.

I was able to concatenate like that:
SQL edit:

Code: Select all

CONCAT( 'Kit: ', [KitAdapter], ' Bat: ', [BikeType] )

SQL view:

Code: Select all

SELECT CONCAT( 'Kit: ', `KitAdapter`, ' Bat: ', `BikeType` ) AS `BikeList`

You may want to update your guide on the first post.