How to change a WHERE clause based on user input (form)?

[ngdias]

Consider a generic SELECT statement, saved as a Query:

SELECT COUNT(*)
FROM Table
WHERE field='condition'

I would like the user to easily change the condition, for example from a drop down menu. Instead of creating a different View for each condition, for example.

Would it be possible for example to create this as a Query, create a Form from the query and bind the condition to a drop down menu where the values are taken from another SELECT statement, for example another Query / View?

[nicholas59]

Hi,

Do apologize for delayed comment. My turn now to return a question to you: i.e. Ok supposed you know about criteria in select statements and supposed you are enough skilled with MainForms and SubForms, why do you absolutely need macros in your research?

[ngdias]

I don’t need macros in my database. I’m actually trying to avoid using macros. I mentioned this in one of the comments.


Not only because of the extra work and the delay in development - learning macros first and then actually coding them - but also because the database will likely be shared with strangers and I don’t want people freaking out with warnings about activating macros if I can help it.

[nicholas59]

So what?

You could then e.g. create an additional Table named Filter with subsequent field named my_condition, add it in the relational scheme, start the form wizard with the Filter Table in the main form,
and link the Query that was suiting your move in the sub form.

However, as long as I remember, Base hasn’t a multi user architecture; i.e. the owner is to distribute .odb file updates to his committee.

[Wanderer]

The quickest way, where you type the condition is a parameter:

SELECT COUNT(*)
FROM Table
WHERE field=:condition

.
The other is to use a second table, wich contains usually only one line to hold the value(s) for your filter.

SELECT COUNT(*)
FROM Table
WHERE field=(SELECT condition FROM Filter WHERE id=0)

The table Filter can be included in your form.
.
You may find descriptions as “power filter” in older forum posts and I think also in the guide for base. Here is a thread to combine with like:
https://forum.openoffice.org/en/forum/viewtopic.php?t=108367

[ngdias]

Hi, thanks for the quick reply. I had a look at the link and also this 'Lookup' fields from another table in a form - #3 by Villeroy.

This is very confusing on a first approach. The guides seem to be for Open Office and the stuff is not exactly the same nor in the same place, although similar. Also it would be great to have a simple tutorial to demonstrate the basic principles.

One thing I don’t understand yet is, does the ‘power filter’ need any sort of macro script?

From reading a bit I think the logic is to invert the ‘priority’ or relation between the main dataset and the list you pick the condition from.

So the list of conditions becomes the main form, while the bulk of the data to be displayed actually goes in a subform?

I still don’t understand how exactly to link the 2 data sets, so the selection in the first form goes to the :condition parameter in the other.

I want to show a list of names to pick from, but what goes into the SELECT :condition is an ID corresponding to that name. Can I just point to the table with this data, or do I need to make a Query with just ID and name first?

(why aren’t there any spaces between the paragraphs??? I wrote the first post the same way and the paragraphs have spaces between them)

[robleyd]

@ngdias

why aren’t there any spaces between the paragraphs?

Newlines/blanklines render in preview, but not in final post

Newlines/blanklines render in the preview, but not in the final post. Periods or other characters have to be used to as a replacement for newline/blankline functionality. Example: Preview renders newline/blankline above this line, but forum does not render it when this is posted.

[ngdias]

Thanks but my initial post that started this thread had normal line breaks with the Enter key and the paragraphs come out separated. In the comment this does not happen. Why?

In any case, this seems very awkward. Surely in 2024 any forum software should be able to render a newline as a newline, no matter if it’s a preview or the forum page, or a comment, no?

[Wanderer]

@ngdias

Surely in 2024 any forum software should be able to render

in any funny way css-stylesheets allow.
.
It is a mixture of Markdown and html used by discourse… You max separate your paragraphs with <br>, if you like…

[Wanderer]

@ngdias

guides seem to be for Open Office

Yes, but for Base not much changed, if you learned how to work with form and sub-form. LibreOffice added another embedded database, but most things are pretty much the same for more than 20 years.
.
I often recommended a video on youtube to see how one can use the Navigator to change hierachic arrangements, but as it is german it is not so useful for users in other languages.

05 OpenOffice Base ein Formular mit Suchfunktion erstellen

[Wanderer]

@ngdias

does the ‘power filter’ need any sort of macro script?

I’m using a macro to refresh the filtered data ‘on change’ of the filter, without pressing any button. If you accept pressing a button, it can work without. Look for the video mentioned above, where to place the button …

[ngdias]

Hi, so I read some more. Here’s what I understood so far:

• There is no system tailored for the specific purpose of variable substitution and replacing a :placeholder parameter in a WHERE clause with the input from a form field. This would be the intuitive way for anyone coming from a programming language that embeds SQL, I guess? So people cheese the Form functionality made primarily to facilitate data inserts, to just display data instead. The solution is sort of a hacky reversal the roles of the forms, making the simple form element to pick the value the Main form and the bulk of the form with the data is then set up as a Subform of this Main form. The Subform is designed to pickup values from the Main form, but not the other way around.
  
  
• One way to do this is to create a single row table that stores the value to be read by the Subform elements. This table is modified in the Main form every time the user makes a selection on an appropriate form element (on the Main form ofc) and then refreshes the Sub form, which then picks up again the modified value. I get the example here Need to create a form to enter a record ID to delete - #2 by Ratslinger. However this example creates a table right next to the main database tables and I was wondering if it is possible to save this somewhere else. I’m trying to apply this to a sqlite database and I would like to keep any support tables out of the .sqlite file. Is it possible to save this in any other way, perhaps on the .odb file for example in the form itself?
  
  
• A Combo Box is a single field form element. In a dual system that displays a list of names from one field and outputs an ID number from another field, one would need to use a List Box instead. I still don’t know how to do this → show values from one field and use another as the output? I’ve been playing around with List boxes and I’m having a hard time displaying the field I want unless I set the Bound field to -1. Any other value I get a non-functional element.
  
  
• To refresh the data displayed in the Subform, one simply set ups a clickable button in the Subform, set to refresh. This part is simple.
  
  
  I think I’m almost there, I would really appreciate some guidance on the two questions above. Thanks!

[Wanderer]

@ngdias

Is it possible to save this in any other way, perhaps on the .odb file (as a Query) or even better in the form itself?

Before answering I should say I use an precise copy of my data-table (all columns, but only one row) as a filter table with my sqlite-db. Advantage: The filter-table can be accessed by standard sql, so it works also with other databases like MariaDB. No macro needed.
.
As a coder you can link nearly everything. The problem is, YOU have to do it or somebody else need to add some kind of extension. There is the :parameter wich binds to parameter-subsitution, but I know of no way to access some @form@field. Sure it could be invented, but don’t expect anything soon, as Base didn’t get much support.
.
So what you can do is developing macros. The macro runs “inside” of your .odb and can access the form. You can then create and execute SQL-commands from your code. Several examples in the guide for Base available.
.
Sqlite may have a combination for you: You could attach a “service-db”, wich has all your filter-tables, but I didn’t try this from LibreOffice. I only used ATTACH in command-line scripts for sqlite. But I guess a temporary table may be more what you need. In both cases, you would need some setup-script to create or attach the table, before forms/other scripts can use them.

[Wanderer]

@ngdias

playing around with List boxes and I’m having a hard time displaying the field I want unless I set the Bound field to -1.

The numbers you use must match the sql-query you entered before. Can you show your query?
.
Maybe try some of this videos, but I didn’t watch them myself. (It is no content of TDF or LibreOffice but of thefrugalcomputerguy)

LibreOffice: Base - Homepage

[Villeroy]

@ngdias

There is no system tailored for the specific purpose of variable substitution and replacing a :placeholder parameter in a WHERE clause with the input from a form field.

Subforms get their parameter substitutions from their parent form.

@ngdias

However this example creates a table right next to the main database tables and I was wondering if it is possible to save this somewhere else.

This is a problem, indeed. I’m aware of only one way to handle this without macro programming.

@ngdias

A Combo Box is a single field form element. In a dual system that displays a list of names from one field and outputs an ID number from another field, one would need to use a List Box instead. I

A combo box is just a text box with auto-complete. What you describe is a listbox. Recipe for a working listbox:

• Create a query like SELECT "Surname" || ', ' || "Forename" AS "Visible", "ID" FROM "Persons" ORDER BY "Visible" which selects 2 columns, a visible one made of text and its corresponding primary key.
• Create a listbox or right-click-replace some numeric foreign key field created by the form wizard.
• In the listbox properties, you choose the foreign key as “Data field”, content type “Query”, content is the query you created and the “bound field” index remains 1, which refers to the 2nd field.

This way you can build working listboxes from reusable queries within seconds.

See https://ask.libreoffice.org/uploads/short-url/8nC9OrU7HOQpQmQHg1eZLpkq415.odb (“power filter”)
or https://ask.libreoffice.org/uploads/short-url/bAFbkHry1oo22OFK8AipFHBws2j.odb (relations in subforms and listboxes).

[ngdias]

Thanks for the input. I had yet another read at Apache OpenOffice Community Forum - [Example #1] Filter/Search with Forms (leveraging SubForms) - (View topic) and I think I understand it better. The example with parameter substitution still uses a Filter table to store the variables to be read by the Subform. I was wondering if somehow I can create a temporary table to perform the function of a filter table? I tried something like

CREATE TEMPORARY TABLE Filter2 (
    ID INTEGER PRIMARY KEY,
    BikeID2 INTEGER)

In the List Box > Form Properties… > Data and then use SQL command, but I get an error.
Then I had a look at some way to always create a temp table when the form is opened. I had a look at the options in the Label fields, they also allow for SQL statements… And there is a control that establishes the order in which the form elements are run at the start. There is a control ‘When loading…’, but this requires setting up a macro. Would this work? If I start creating macros, will there be warnings when Base opens? I may want to send the database to other people, but macros are a well known source of hacks or exploits and 3rd parties are usually wary of opening files with macros. So I would prefer to avoid using macros if they are going to pop up warnings.


Regarding the List field, it appears a SELECT statement with the first field as ‘name’ and the second as ‘id’ with Bound field set to 1 works. Apparently what populates the List box must be the first field in the SELECT?

[ngdias]

@Villeroy

Subforms get their parameter substitutions from their parent form.

Yes I understand this now, but Subforms can only read data from a parent form if the data is stored in a table, correct?. Subforms cannot read the value directly from a form field, like what happens for example in HTML. This would make things a lot simpler…

[Villeroy]

I can’t get temporary tables to work with Base. I can create a filter table in memory, I can insert a record, but Base seems to be ignorant of that table.

Two attachments:

1. A database doc FLT_HSQL.odb to be registered as “FilterTable” (via Tools>Options>Base>Databases) with a record in one filter table only.
2. A stand-alone form document with a filter form linked to the filter record and a subform linked to the “Bibliography” database that comes with LibreOffice. This is a dBase database.

You see, that it is possible to link logical forms to different databases, because logical forms attached to stand-alone documents let you choose between different databases whereas embedded forms always refer to their embedding database.
Biblio_Filter.odt (55.7 KB) (form/subform linked to different databases)
FLT_HSQL.odb (3.5 KB) (register as “FilterTable”)
P.S. if you append
OR :paramTXT1 IS NULL
to the subform’s SQL statement, you can clear the filter form in order to see all records.

[Wanderer]

@ngdias

So I would prefer to avoid using macros if they are going to pop up warnings.

There are warnings. They can be switched off, if you place the macro in a “safe” folder or for signed code.

[ngdias]

When you say ‘you’ you mean I would need to prepare a separate file with the macro code and tell whoever I share the database with to trust the macro code, and additionally perform an additional step of copying the macro file into a specific folder in their computer?