Use a cell value as query parameter in database query

matth · March 26, 2025, 1:55pm

I managed to create a database query and import that into a Calc sheet.

The query uses a variable parameter, so when I refresh the range, a popup ask for the value of the parameter, and then returns the matching values.

How can I link a cell value to be used for that parameter, so I don’t have to manually enter it every time I refresh the query?

Villeroy · March 26, 2025, 6:10pm

Store filter criteria in the database. Store everything in the database.

https://ask.libreoffice.org/uploads/short-url/gK9hcyzEnMVotx6Mtmr6CqCDsPR.odb

A variant of the same with forms on sheets: Apache OpenOffice Community Forum - [Solved] Using Filter in Calc V3.1 with dates - (View topic)

matth · March 28, 2025, 3:20pm

Thank you very much.
I tried to create a form however I’m still struggling with one point.

I created a List Box field where I can pull the possible parameter values. With a push button I can trigger to refresh the query. However, when I push the Ok button, the popup still ask to enter the parameter value manually. How do I “link” the value from the List Box to the Parameter in the query?

The parameter in the query is called :BatchNo and that’s what the field value in the List Box is also called. I can’t seem to find to link the query parameter to the form.

EDIT: So I think I understood the concept now. You store the form data into a filter table in the database, and then the main query includes the values from the filter table.
However, my database is an ERP system, where I can’t just add tables at my pleasure. Is there another solution than to add tables to the database?

Villeroy · March 28, 2025, 10:31pm

Let me suggest a somewhat esoteric solution, still free of macro code. If you register the ERP database, register a second database with the filter table and use stand-alone forms (Writer documents or Calc documents), it is possible to bind a main form to a filter table of one database and bind the filtered subform to your ERP database.

Create a new, registered database document with an embedded Firebird or HSQLDB.
Create a filter table. The following statements create a table with one row and several columns of various simple data types:

CREATE TABLE "FLT"("D1" DATE,"D2" DATE,"INT1" INTEGER,"INT2" INTEGER, "INT3" INTEGER, "DEC1" DECIMAL(7,2),"DEC2" DECIMAL(7,2), "C1" VARCHAR(50), "C2" VARCHAR(50), "C3" VARCHAR(50),"BOOL1" BOOLEAN, "BOOL2" BOOLEAN, "DESCR" VARCHAR(200),
"FID" SMALLINT NOT NULL PRIMARY KEY);
INSERT INTO "FLT" ("FID") VALUES (0);

Create a Writer or Calc document with a main form pointing to
SELECT * FROM "FLT WHERE "FID"=0 in the registered filter database.
Disallow everything except modification. No insertion, no deletion, no navigation bar.
Create a subform linked to your param query of registered ERP database and link the parameter names - without leading colon - to fields of the filter record with matching data types. The filtered records will be editable if the param query refers to a record set from one table including the primary key.
Add a [Cancel] button to the filtering main form and an [OK] button with Action=Refresh to the filtered subform. The OK button moves the focus from the main form to the subform, which stores the main form before refreshing the subform.

This is not possible with a form that is embedded in the database because embedded forms always refer to the same embedding database.

Villeroy · March 29, 2025, 8:29pm

param_substitution.ods (21.1 KB)

OK, this took some time. Please register my sample database
as “FilterData” and store the attached spreadsheet in a trusted directory where documents are allowed to run macros.

A macro refreshes the linked database ranges “Import2”,“Import3” and “Import4” on document open. These database ranges reside on Sheet2.

The first sheet contains a form and a 4 cells B2:B5 where you can enter optional filter criteria.
The 4 cells have some validation for the criteria input based on the linked datbase ranges.
The form is linked to the database’s parameter query “ParamFilter” which filters by a from-date, until-date, a category and a person. Each parameter is optional. Missing parameters are ignored.

SELECT * 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 )

The form’s push button simply reloads the form.
The form has a hidden control named “ParamsRange” which contains the name of a range named “Parameters” (Sheet2.J2:M3).

The named range specified by the hidden control contains the 4 names of the query parameters in row #1 (paramD1, paramD2, paramP and paramC) and the corresponding criteria values from the first sheet in row #2. Any missing criterion is represented by error value #N/A.

Whenever the form is loaded or reloaded by any means, the form’s event “Fill Parameters” calls a macro.
The macro reads the named range specified by the hidden control “ParamsRange” and fills the query parameters with the values from row #2 according to the corresponding parameter names in row #1.

The hidden control makes it possible to use the same trick with different forms and different parameter ranges within the same document without writing a modified macro. Just add a hidden control “ParamsRange” where you specify the named cell range, filling up the parameters.