Build Search and Filter Forms in LibreOffice Base

Creating search and filter forms in LibreOffice Base allows you to locate specific database records without manually scrolling through large tables or editing raw queries. By using a dedicated filter table, SQL parameter queries, and linked form controls, you can build an interactive dashboard to instantly display search results. This guide explains how to design and configure these dynamic filter forms step by step.

Step 1: Create a Filter Table

A temporary filter table stores the search criteria entered into your form.

  1. Open your Base file and navigate to Tables > Create Table in Design View.
  2. Create a primary key field named FilterID (set type to Integer, AutoValue to No).
  3. Add fields matching the data types you want to search (for example, SearchText as VARCHAR or SearchDate as DATE).
  4. Save the table as tbl_Filter.
  5. Open tbl_Filter in data view, enter a single record with FilterID set to 0 or 1, leave the search fields blank, and close it.

Step 2: Create a Parameter Query

A SQL query is needed to compare your main data table against the values stored in tbl_Filter.

  1. Go to Queries > Create Query in SQL View.
  2. Write a query that joins your data table with the filter table and handles empty search fields using SQL functions:
SELECT "tbl_Customers".* 
FROM "tbl_Customers", "tbl_Filter"
WHERE "tbl_Filter"."FilterID" = 1
  AND (
    "tbl_Filter"."SearchText" IS NULL 
    OR "tbl_Customers"."CustomerName" LIKE '%' || "tbl_Filter"."SearchText" || '%'
  )
  1. Save this query as qry_CustomerSearch.

Step 3: Build the Main Search Form

The main form will capture user input and write it to tbl_Filter.

  1. Go to Forms > Create Form in Design View.
  2. Open the Form Navigator (found on the Form Design toolbar).
  3. Right-click the root Forms folder, select New > Form, and rename it to MainForm.
  4. Open Form Properties for MainForm:
    • Set Content Type to Table.
    • Set Content to tbl_Filter.
    • Set Allow additions and Allow deletions to No.
  5. Add a Text Box control to the canvas for entering the search term. In its Control Properties, bind the Data field to SearchText.
  6. Add a Push Button next to the text box:
    • Set the Label to Search.
    • Set the Action property to Refresh form.

Step 4: Add the Results Subform

The subform displays records from the query matching the filter criteria.

  1. In the Form Navigator, right-click MainForm and select New > Form to create a nested subform. Rename it to SubForm_Results.
  2. Open Form Properties for SubForm_Results:
    • Set Content Type to Query.
    • Set Content to qry_CustomerSearch.
  3. Add a Table Control (Grid) onto the form canvas.
  4. The Table Element Wizard will launch. Select the fields from qry_CustomerSearch you wish to display, then complete the wizard.
  5. Add a Push Button labeled Clear Filter:
    • Set its Action to Refresh form.
    • Clear the text field manually or attach a small macro to reset the SearchText column in tbl_Filter back to NULL.

Step 5: Test the Search Form

  1. Switch out of Design Mode (click the Design Mode On/Off icon).
  2. Type a keyword into the text box and click the Search button.
  3. The underlying table will update the filter criterion, and the subform grid will refresh to display only the matching records.