Filter Child Records with Subforms in LibreOffice Base

Designing subforms in LibreOffice Base allows you to establish master-detail relationships, enabling the interface to dynamically display and filter child records whenever a corresponding parent record is selected. This tutorial outlines the exact steps required to set up parent-child subforms, configure the Form Navigator, and link primary keys to foreign keys for seamless data filtering.

1. Establish the Table Relationship

Before creating forms, ensure your database has a proper relationship defined between the parent (master) table and child (detail) table. The parent table must contain a Primary Key (e.g., CustomerID), and the child table must contain a corresponding Foreign Key field (e.g., CustomerID) that references the parent.

2. Create the Main Form (Parent)

  1. Open your LibreOffice Base database file.
  2. In the Database pane, click on Forms, then select Create Form in Design View.
  3. Open the Form Navigator via the Form Design toolbar to view the hierarchical structure of your form.
  4. Right-click the root Forms folder in the Form Navigator, select New > Form, and rename it to MainForm.
  5. Right-click MainForm, select Properties, and navigate to the Data tab:
    • Set Content type to Table or Query.
    • Set Content to your parent table (e.g., Customers).
  6. Add controls (such as text boxes, labels, or a list box) to your form canvas to display the parent record fields.

3. Add and Configure the Subform (Child)

  1. In the Form Navigator, right-click MainForm and select New > Form. Rename this new form to SubForm. Placing it inside MainForm establishes it as a child.
  2. Right-click SubForm and select Properties.
  3. Go to the Data tab:
    • Set Content type to Table or Query.
    • Set Content to your child table (e.g., Orders).

To filter child records automatically based on the parent record: 1. In the Properties: Form window for SubForm, locate the Link master fields and Link slave fields options on the Data tab. 2. Click the ellipsis (…) button next to either property to open the Field Linker dialog. 3. Under Master field, select the parent table’s primary key field (e.g., CustomerID). 4. Under Slave field, select the child table’s foreign key field (e.g., CustomerID). 5. Click OK to apply the link.

5. Add Child Controls to the Subform

  1. Ensure SubForm is selected in the Form Navigator.
  2. Add a Table Control (Grid) to the form canvas to display multiple child records simultaneously.
  3. The Table Element Wizard will launch automatically:
    • Select the fields from the child table you want to display (e.g., OrderID, OrderDate, TotalAmount).
    • Complete the wizard.

6. Test the Filtered Subform

  1. Toggle off Design Mode by clicking the Design Mode On/Off button on the Form Design toolbar.
  2. Navigate through the parent records using the record navigation bar for the main form.
  3. The child table control will automatically update and filter to display only the records associated with the active parent record.