LibreOffice Base

No More Mouse Clicks

Significantly more complicated is the query that returns all records in Images that match the search string in Filter . That's where you need to write some SQL code. In the Base main window under Queries , select the Create Query in SQL View option.

The Base handbook [2], under the heading General remarks on database tasks and Data filtering , mentions having the SQL code you need to start with. Listing 1 shows an example based on our field and table name structure.

SELECT * FROM "Images" WHERE "Title" = IFNULL( ( SELECT "Title" FROM "Filter" ), "Title" )

The entry SELECT * FROM selects all fields in the Images table, and the WHERE clause filters for specific record in which "Title" has to match the results from a second SELECT . This clause references the Filter table and specifies the value to match. The query thus extracts only those Title field values in Images that have the same value for the field in Filter . The IFNULL function, however, makes an exception if the Title field in Filter has a null value.

IFNULL returns the value from the first parameter, as long as it's not null; otherwise, it returns the Title value in the second parameter. The quoted string is evaluated as a field name from the first SELECT , so that it compares Title with itself, which is always "true" as long as the same field in Filter has a null value. The result is the filter function returns all records when a null filter value exists – at least in theory.

Unfortunately, SQL idiosyncrasies regarding the IFNULL function thwart the seemingly obvious assumption that a self-comparison always returns true . If the field is empty, for example, SELECT returns a NULL value, but the IFNULL gets an empty value, which is not the same as <NULL> as far as the database is concerned. Thus, an empty filter field can be missing all records where the Description field was not filled in.

First Aid

If the workaround doesn't work for excluding the Title field in Images while designing the table with Input required = yes , the only solution is to encapsulate the return value for SELECT also with the IFNULL function (Listing 2).

SELECT "Images".* , IFNULL( "Title", '' ) AS "T" FROM "Images" WHERE "T" = IFNULL( ( SELECT "Title" FROM "Filter" ), "T" )

Unlike the handbook, the example uses "Images" before the asterisk (* ) that is the wildcard for all fields in Images , even though it's clearly already indicated by the FROM "Images" clause. Without this workaround, Base 4.2.3 returns a syntax error, presumably from a bug in the SQL parser, because the redundancy makes little sense. Other Base versions don't have this problem, not even earlier ones.

The WHERE clause uses AS to compare the result of the IFNULL function with the result of the alias T value assigned to a new IFNULL function. The result is never NULL , but rather the empty value '' , which makes the filter query succeed.

Thus, MainForm no longer extracts its data directly from the Images table, but through the intermediate filter query. Therefore, you can change the data binding from MainForm to Filter: Query .

To have the filter function work from the form, you will need to do one more little thing. Give the MainForm a button (using the Push Button option in the Form Controls toolbar) and then choose the action Update form in the properties on the General tab. Only after the first click on this button will MainForm show the matching record in the filter field.

The search function can be extended to other data fields. Each additional field calls another IFNULL function in the SELECT clause at the beginning of the query, as well as an IFNULL function in the SELECT clause that encapsulates the subquery. Listing 3 shows how this is knit together.

SELECT "Images".* ,
  IFNULL( "Title", '' ) AS "T" ,
  IFNULL( "Author", '' ) AS "A"
FROM "Images"
WHERE
  "T" = IFNULL( ( SELECT "Title" FROM "Filter" ), "T" )
AND
  "A" = IFNULL( ( SELECT "Author" FROM "Filter" ), "A" )

Note, however, that the Filter table needs an additional column for each search field with the same data type as its counterpart in Images (e.g., Author of type VARCHAR ). The subform for the search also needs an additional text field where you can modify the author names in Filter .