LibreOffice Base

The first part of this tutorial [1] explained how to create an image database with easy input masks and a tagging system (Figure 1). It satisfied the Base [2] claim that you can do database programming using mouse clicks instead of having to resort to SQL in Linux. Wizards and a graphical query editor do the work and let even users without programming experience create a database, forms, and queries.

Figure 1: Created from three tables, a query, and input form, the image database from the previous article was lacking only a search function.

The Base application from the first article lacked just one important feature: a search function (Figure 2). However, this task is one where the graphical helpers in Base are not going to help much – you can't do it without getting down and dirty with SQL.

Figure 2: No one wants to leaf through data records to find a particular image. The title search at the top of the form is what helps.

Manual Labor

A query that filters matching records by keyword is not something the graphical editor can handle. It requires SQL code – such as programmers use to communicate with the database – fortunately, you're only going to need homeopathic-like dosages.

The task of inserting a user search string in an otherwise fixed query can be implemented in Base only through a back door. Figure 3 shows how it works. The Filter table (in yellow) contains only a record with a search string – in this example, the Title .

Figure 3: A subquery (in blue text) provides the main query (in black text) of the Title value as the single record of the Filter table. A subform that accesses Filter changes its value, thereby launching a search for the matching title.

A subform changes the Title stored in Filter – similar to the Categories subform shown in the first part of this tutorial. The search query (in red) compares the Filter title with the Title fields in the Images table (in blue) and returns a record where the two match.

Figure 2 shows an extended version of the Images form. At the top, you can see the filter function highlighted in blue that searches for images with a particular Title . Leaving the field empty means scrolling through all the records as usual. Entering a value returns only those records matching the search string.

Data Buffer

To build on the familiar, you can first create a table called Filter , which contains a VARCHAR field called Title that takes the modifiable search string. Allow Base to create a primary key for it automatically; otherwise, the content can't be changed by the form.

Next, you'll need another form that accesses the Filter table. Open the form navigator and right-click Forms to choose the Filter form. Because this form controls the data display in the previous main form, drag the MainForm to Filter to subordinate it.

The next step is to bind the Filter form with the table of the same name. Use the Forms button in the left button bar (the fourth from the top) to open the settings palette for the form.

Figure 4 shows the required settings for the Data tab. After selecting the table, you'll find the Cycle setting for Active record . This ensures that you don't add any new records but only change existing ones – because only valid entries can be used as search strings.

Figure 4: The Active record cycle limits data entry to actual records.