How can I use a dropdown list to display only certain datasets of a query?
I’m using a query to display an overview of data sets with certain fields and aggregate functions. I want to use a list field to set a filter and it works fine filtering table entries. But as soon as I set the form data to the query instead of the table the list field is greyed out and cannot be used anymore. This happens when I copy the query into the SQL field of the form as well as using a named query from the menu.
I’m just using the ID returned by the list selection. I don’t want to change any data. This query is just for displaying results.
If this does not work I think I will make the dropdown list “stupid”, meaning I will try to disconnect it from the data. Then I will try to populate it onLoad using a SQL query in a macro. At least that is one way I can think of. I would like to avoid using filter tables.
I made an example file with a list of Dogs showcasing the problem. Hope you can see from it what I’m trying to achieve. I use the macro for setting the form filter.
SELECT Dogs.dog_name, Dogs.dog_id, Breeds.breed_name, Owners.owner_name, Owners.owner_id FROM Dogs LEFT OUTER JOIN Owners ON Dogs.owner_id = Owners.owner_id LEFT OUTER JOIN Breeds ON Dogs.breed_id = Breeds.breed_id
REM ***** BASIC ***** Sub FilterByOwner(event As Object) Dim lstOwner As Object Dim form As Object Dim selectedId As String lstOwner = event.Source.Model form = lstOwner.Parent selectedId = lstOwner.getCurrentValue() If selectedId = "" Then form.Filter = "" Else form.Filter = "owner_id = " & selectedId End If form.reload() End Sub Sub ResetFilter(event As Object) Dim form As Object form = event.Source.Model.Parent form.Filter = "" form.reload() End Sub
Version: 184.108.40.206 (x64) / LibreOffice
Thanks to @Ratslinger I was able to get it working. Here is the changed example file in case someone has that problem in the future. I had to move the
Reset Filter button to the subform and add a reset action + macro to it because it kept trying to submit the list.
Regarding my dislike of filter tables:
Maybe I do not understand the filter table concept completely. But from what I got so far I do not like them. I did not want to use filter tables because they are LibreOffice specific artifacts that help the GUI solve things it should be able to do on its own. I cannot repurpose them for caching or optimisation either, the DBMS does that already and it does it well. Imagine if you used multiple GUIs for a DB and every GUI stored its own metadata garbage tables in the database. It gets even worse once you have multiple DB users. I would only use filter tables if I did not have a choice, like if I needed a document without macros and for that they are OK.