All-purpose filter table with one row. Add more rows for more filter forms.
CREATE TABLE "FLT"("D1" DATE,"D2" DATE,"INT1" INTEGER,"INT2" INTEGER, "INT3" INTEGER, "DEC1" DECIMAL(7,2),"DEC2" DECIMAL(7,2), "TXT1" VARCHAR(50), "TXT2" VARCHAR(50), "TXT3" VARCHAR(50),"BOOL1" BOOLEAN, "BOOL2" BOOLEAN, "DESCR" VARCHAR(200),
"FID" SMALLINT NOT NULL PRIMARY KEY);
INSERT INTO "FLT" ("FID") VALUES (0);
Create a form with source type “SQL” and source SELECT * FROM "FLT" WHERE "FID"=0
Form properties:
- Modify =Yes
- Insert = No
- Delete = No
- Navigation bar = No
This setup ensures that you can’t tab into the next record or into the new record of that table.
Subform:
Some table or query with pairs of master/slave fields. This simple setup is limited to pairs like Master.A = Slave.X AND Master.B = Slave.Y, and when any of the master fields is empty, the subform remains blank.
Any slightly more advance pairing (Master >= Slave, Master LIKE %Slave%, function(Master)<Slave OR Master IS NULL) requires parameter queries in parsed mode with named parameters. Then map the master fields to the subform’s parameter names, without leading colon.
2 Buttons:
- A cancel button (Action: Undo data entry) belonging to the filtering main form.
- An OK button (Action: Refresh form) belonging to the filtered subform.
https://ask.libreoffice.org/uploads/short-url/6lJYIqihU01BD1nOGoeDVi5ofmB.odb
Uses a paramter query
SELECT "PID", "CID", "D", "V", "ID"
FROM "Data"
WHERE ( "PID" = :paramP OR :paramP IS NULL )
AND ( "CID" = :paramC OR :paramC IS NULL )
AND ( "D" >= :paramD1 OR :paramD1 IS NULL )
AND ( "D" <= :paramD2 OR :paramD2 IS NULL )
- Missing arguments are ignored because
:param=Something OR :param IS NULL returns True if either the criterion matches or if the criterion is missing.
- The date is compared with 2 parameters using operators <= and >=.
- We can easily filter by list box values comparing integers with integers.
- The filtered record set is editable because we simply select all columns of a single table.
