Getting started with LibreOffice Base

Line by Line

Unlike the main form that displays only one record at a time, all the linked categories in the categories subform show up together. Instead of simple text fields, what you really need is a multi-line Table control element (Figure 9), like a drop-down menu. You can add this to the empty subform using the aforementioned More Controls tool. Be sure that the Categories subform is active in the Form Navigator, because only then will new control elements be created in the subform and have access to the data records.

Figure 9: A table control element shows all the records of a form or subform as table rows. Like ordinary forms, it recognizes different field types, such as the list box used in the example.

First, open up some space by minimizing the Comments field, then use the table control elements tool to open the Categories table, as in Figure 6. Cancel the wizard after it starts up. Then, right-click the table control element to set the Navigation bar and Record marker to No . The table control element shown in the figure appears but has no data fields in it.

Next, right-click the table header and choose Column… from the pop-up menu and create a List Box (that will show up as List Box 1 ) column. On the General tab, change the Title to Categories . On the Data tab, link the Data field to the column in ImageCategories that stores the linked categories, namely CategoryId .

You don't really want the CategoryId numerical key to appear in the list box, however. Instead of key integers such as 1 , 2 , or 3 , the category labels, such as "Portrait", "Landscape," and "Still Life" should be shown. Unfortunately, these category names don't appear in the ImageCategories table bound to the subform but in Categories instead.

That's why the List Box field type extracts the list items from an fixed data source, which is different from the Data Field that stores the list box fields, and which should have at least two fields per list entry (compare Figure 10, List Box Categories ). The first field of the data source is always made visible in the list. A different Linked Field provides the value that appears in the ImageCategories table after a selection. The Linked Field setting determines what the value is. The column count begins with zero.

Figure 10: The category mapping works as follows: A subform associates all the categories in the record via a link of id in Image with ImageId in Categories. A list box makes another internal link: It shows the names associated with the numerical CategoryId.

Not Asking Is Not Learning

If the order of fields in the Category table were Category then id , you could use the table directly for the contents of a list. However, the id is typically made the first field by convention.

To solve this problem, you need a simple query at this point to change the order of the fields. As a bonus, the query will sort the category names alphabetically.

Click Queries in the Base main window and choose Create Query in Design View . For the question of which table to use, choose Categories , click Add , and Close .

Next, all you need to do is drag the fields Category and id to two empty columns at the bottom of the Query Designer , so that Category is in the first columns and id is in the second. Then use Sorting with Category to sort ascending (ASC ) and save the query with the name CategoryList .

Go back to the form design (right-click and then Edit on the form). Right-click on the column header of the table in the subform and select Column… again to open the list box's properties.

Go to the Data tab and chose Query for Type of list of contents and the query CategoryList for List content . Type 1 in the Bound Field , that is, the second field CategoryList (i.e., the id column in the Categories table that we set up in the query above).

Think of CategoryList as a virtual table that reconstructs the contents of the Categories table. Queries are seldom as easy as that because most combine fields of many tables or filter specific records based on the Criterion setting in the query editor. One of these queries will be described in the second part of this workshop when programming a search function.