Switch to: V9V8V7V6V5

Lesson 6 - Complex Filter

In this lesson, you will learn how to filter records shown in the form using custom conditions. This approach requires more coding than the control filtering but it also more powerful and customizable.

The form will allow the user to select the field for filtering, select its criterion (=, <, >, LIKE) and input the value to compare to the field values.

These example lessons make use of a version of the well known Sakila database, the successor to Microsoft's Northwind database. While in this lesson we use a version of Sakila adapted to Valentina DB, the skills are transferable to any other database.

Archives

  • Sakila - Here you can find archives of 'sakila' databases for Valentina DB.

Note, that form uses 'sakila' database with Foreign Key links, download and uncompress it.

  • Project from the previous lessons, download and uncompress it.

Step 1: Open Project

Open an existing local project.

  • On the Start Page click Open Project → Local… and select the project on disk.

Step 2: Duplicate Form

  • Duplicate the form 'simple_form' created in the first lesson using the context menu.
  • Rename a new form as 'complex_filter' and open it for editing.

Step 3: Add Filter Controls

  • Add a new horizontal layout to hold filter widgets and a horizontal line to separate them from the other widgets of the form.

  • Drag the ComboBox control to the form which will show the list of fields to filter. Rename it as cbField.

  • Double-click it to open the dialog, switch to the Schema mode and select the Fields group of the customer table:

  • Drag the ComboBox control to the form which will show the list of criteria for the filter, rename it as cbCriteria:

  • Double-click it to open the Items dialog and add the following criteria:

  • Drag the LineEdit control to the form which is intended to enter the value for filtering and rename it as leSearch.

  • Drag two ToolButton widgets: one to apply the filter and one to discard it.
  • Rename them as tbApplyFilter and tbDiscardFilter respectively.
  • Set the appropriate icons for this buttons.
  • Uncheck the property Enabled of the tbDiscardFilter button, it will be inactive at the start since no filter is applied yet.

Step 4: Apply Custom Filter

  • Set the following script in the clicked slot of the tbApplyFilter button:
// Field names may have flags, so remove them
var fldName = this.cbField.currentText;
if( fldName.slice( -1 ) == ']' )
{
  var brIndex = fldName.lastIndexOf( ' [' );
  if( brIndex > 0 )
    fldName = fldName.slice( 0, brIndex );
}
 
// Prepare values
fldName.replace( '"', '""' );
 
var search = leSearch.text;
search.replace( "'", "''" );
 
// Prepare condition
var condition = '"' + fldName + '"' + this.cbCriteria.currentText + "'" + search + "'";
 
// Apply filter condition
records = this.table.getTableCursor( this.uuid );
records.clearFilters();
records.addFilter( condition );
records.applyFilters();
 
// Now we can enable the discard button
this.tbDiscardFilter.enabled = true;

  • Set the following script in the clicked slot of the tbDiscardFilter button:
records = this.table.getTableCursor( this.name );
records.clearFilters();
records.applyFilters();
 
this.tbDiscardFilter.enabled = false;

Step 5: Execute Form

  • Click Execute.
  • Move through the unfiltered records.
  • Select the customer_id field, the criterion is set to '=', input 6 as a filter value
  • Apply the filter using the button. The record with customer_id equal to 6 is found:

  • Select the first_name field, select the LIKE criterion and input %JO% as a filter value.
  • Apply the filter using the button. The set of matching records is found, navigate through it.

  • Discard the filter using the button, now the form shows unfiltered records again.

The resulting project you can find here.