Getting started with LibreOffice Base

Flexible Connections

As explained in the "Relational Databases" box, you'll need two new tables to associate any number of categories to a set of images. Categories includes the Category text field and – as already created in the Images table – an id column with an AutoValue of type Integer that you set as the primary key.

Save this table with the name Categories , close it, and reopen it in data entry mode. Enter the category names you want to use for your image collection – for example, "Landscape," "Portrait," "Still life," etc. In this case, for such a simple table, you will not need to create a form.

You link both tables with what is called a pivot table that you'll name ImageCategories . This connects the Images table with Categories through their id fields. To create this table, use the Create Table in Design View and enter two fields, ImageID and CategoryID , and make them Integer field types.

Be sure to click Yes when asked whether to automatically create a primary key for the ImageCategories table when you save the table. Remember also to set AutoValue to Yes for the new ID field the wizard creates.

It would be unwieldy to manually enter all the id fields for all the images in the ImageCategories table. Instead, I will show how you can integrate the Categories table into the form and display it like you see in Figure 6, surrounded by a red box.

Figure 6: The subform shown in red assigns as many categories as desired to an image.

The categories table embedded in a subform shows all the current categories related to the data set in ImageCategories – plus an empty row where you can add another category selecting it from a drop-down menu. You can modify existing entries the same way. I'll show how it's done.

Lower and Upper

LibreOffice Base connects each form to a table or query as data source. You can access the form's properties using the fourth button from the top, in Form in the left toolbar of the form editing view, on the Data tab (Figure 7). The wizard automatically creates the connection for the Images form.

Figure 7: Every form is bound to a data source (whether it's a query or a table). With embedded subforms, an entry mask still accesses multiple tables.

To include data from the Categories table in an entry form, you need to create a subform (i.e., a form within your main form) that connects with a data source independent from the main form. To create a subform, open the Form Navigator with the button in the bottom toolbar of the form editing view (Figure 8).

Figure 8: The Form Navigator shows an overview or the control elements in an entry mask. A right-click changes the settings or adds subforms.

In the Form Navigator, right-click MainForm , choose New | Form , and enter the name of your new subform. You can call your subform Categories . Right-clicking the Categories subform opens its properties, where you select Table as the Content type and the pivot table ImageCategories as the Content .

Now all the data fields in the subform access the ImageCategories table. At the moment, that's every single register in the table, but I'm interested only in the categories linked to a record opened in the main form. Open the subform's data properties and you should see the Link master fields and Link slave fields values.

Clicking the ellipses button (… ) after each of these properties opens the Link fields dialog, where you can choose the fields from the already defined ImageCategories and Images tables. Choose id for Link master fields and ImageId for Links slave fields . This selection restricts the subform to the rows in ImageCategories whose ImageId matches the id of the opened record in the main form.