Problem: Base has 3 methods of filtering records but no method for filtering by form controls. All Form controls write some value into some column of the current record. Subforms filter existing records by master/slave fields. Let’s utilize this limitation. We create a table with one record where our form controls can save entered filter criteria. The values entered into the single record can filter a subform.
Create a table to store sets of filter criteria with max. 2 integers, 2 dates, 2 case insensitive strings and a primary key (NOT automatic).
CREATE TABLE "Filter"(
INT1 INT, INT2 INT,
D1 DATE, D2, DATE,
TXT1 VARCHAR_IGNORECASE(10),
TXT2 VARCHAR_IGNORECASE(10),
ID INT PRIMARY KEY
)
Add a first row with ID 1
INSERT INTO "Filter" VALUES NULL, NULL, NULL, NULL, NULL, NULL, 1
Create a main form with the following properties:
Source type: SQL
Source: SELECT * FROM “Filter” WHERE “ID”=1
Turn off all abilities except “Modify data”. In this form we must not tab beyond this single record. We must not delete this record and we don’t need any navigation bar for a single record.
Simple solution for a subform filtering one table:
Source type: Table
Source: Table name
Master fields: INT1, D1, TXT1
Slave fields: <corresponding integer, date and text of our table>
This solution is too simple because it does not show any records unless every criterion has a value. You have to fill out INT1, D1 and TXT1 with reasonable criteria in order to get any record set.
Create a parameter query (query with place holders):
SELECT * FROM "Table name"
WHERE (:pTXT1 = "text column" OR :pTXT1 IS NULL)
AND (:pINT1 = "integer column" OR :pINT1 IS NULL)
AND (:pD1 = "date column" OR :pD1 IS NULL)
Now for the form:
Source type: Query
Source: Name of above query
Master fields: TXT1, INT1, D1
Slave fields: pTXT1, pINT1, pD1 (corresponding parameter names without leading colon)
The expression (:pTXT1 = “text column” OR :pTXT1 IS NULL) returns TRUE when parameter :pTXT1 is missing.
If all 3 parameters are NULL (missing), the WHERE clause evaluates to WHERE TRUE AND TRUE AND TRUE, so all records are shown indiscriminately.
If INT is entered in the master form as 42 the WHERE clause evaluates to: WHERE TRUE AND (“int column” = 42) AND TRUE which shows all records where “int column” equals 42, ignoring the other 2 parameters which resolve to TRUE because they are NULL.
Searching for substrings:
WHERE (’%’ || :pTXT1 || ‘%’ LIKE “text column” OR :pTXT1 IS NULL)
The literal % is the same as * when searching for file name patterns.
The || concatenates 2 strings (same as & in spreadsheets).
The LIKE operator is used for pattern matching. An equal sign would match literal % signs.
Searching for date ranges:
WHERE (:pD1 >= “date column” OR :pD1 IS NULL)
AND (:pD2 <= “date column” OR :pD2 IS NULL)
Need another criteria set for another form? Add a filter record with ID=2 and use that filter row for the filtering main form: SELECT * FROM “Filter” WHERE “ID”=2
