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?
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…
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.
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 …
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!
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.
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)
Subforms get their parameter substitutions from their parent form.
This is a problem, indeed. I’m aware of only one way to handle this without macro programming.
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).
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?
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…
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:
- A database doc FLT_HSQL.odb to be registered as “FilterTable” (via Tools>Options>Base>Databases) with a record in one filter table only.
- 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.
There are warnings. They can be switched off, if you place the macro in a “safe” folder or for signed code.
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?
Not necessary. Macros can be in the .odb and the file is placed in a folder wich is labeled as a safe place.
.
I wrote “you”, because the same happens on your system. Assume my photo-database is in folder EXIF, then I can register this folder as trusted place and will not be asked before starting macros from there (but I have also to set the security level accordingly). If I wish to distribute my database, a similiar setting needs to be done once on the system of every user.
.
Maybe check the settings yourself (start at Tools>Options and go to security, then macro-security)
https://help.libreoffice.org/latest/en-US/text/shared/optionen/macrosecurity.html
I could not resist, to add a lengthy comment. Following this video, you can’t get a satisfying filter form.
Ok, got it.
I’m not interested in telling other people to deal with security warnings, so I’m going to stay away from macros for now. And I think I can do what I need without them. But I appreciate your clarification in any case. Thanks.
IMHO, creating usable database solutions for “other people” requires a sane use of macro code.
I managed to get the form to work with a refresh button, following the all the help here and the Bike demo db. I’m not using any macros, only a refresh button.
The only problem now is that I need to press the refresh button twice to actually see updated data in the Subform. The form still takes time to get the data, but it is still assuming the previous selected value in the List box, not the new / current one.
This doesn’t happen in the Bike demo even after messing around with it for a bit.
I checked after making a selection in the List box that the filter table field is indeed updated.
If I for example make a selection in the List box, press the refresh button, make another selection and press the refresh button again, I get the data corresponding to the first selection. If I continue like this the refresh button always shows info from the previous selection, not the current one.
What could be the reason for this and how do I fix it?
Hum… what video and what lengthy comment? I don’t see any… in any case your help is very much appreciated and I thank you for your patience, as well as the other people offering help as well.