Libreoffice combobox filtering

English
, , ,
#1

I have some tables Club and Team. Teams are linked to Club through a ClubID on the Team table. A Club can have many Teams.

I have another table, Fixture, which has a key to a TeamID.

However when completing a form I don’t want to pick from a large list of Teams, I want to be able to first select a Club in one comboxbox/listbox and then have a second box populate with the Teams belonging to that club. This second box would then populate the TeamID of the Fixture table.

This feels like it should be something simple, but I can’t figure it out.

Many thanks.

#2

There is a form based filter accessible from the form navigation toolbar:

And there is a somewhat advanced method of leveraging subforms. I call it “power filtering”. https://ask.libreoffice.org/uploads/short-url/nW2PUGMTAXtJcBy67iiy8RvNj8m.odb

#3

Thank you for the reply, but this isn’t quite what I was looking for.

#4

Forms reflecting one-to-many and many-to-many relations:
[Example] Relations reflected by list boxes in forms

#5

Thank you again, but no.

I don’t think I actually need a subform. I need a field on the form to be populated from data within a combobox, but the data in that combobox should be dynamic based on a selection made in an earlier combobox.

#6

A combo box is just a text box with auto-complete. It writes into the undelying text field whatever you type into it regardless of the list content. If it is about primary keys and foreign keys, you always want a listbox. A list box shows a column of text values and writes the corresponding key into a foreign key field (typically an integer).
Cascading listboxes are a little bit complicated. The attached database lets you find a city by its continent and country. The selected city can be passed to a voyage entry then.
It has 4 forms:

  1. Voyages_simple which is more like a work-around to avoid cascading listboxes, nevertheless allowing to find a city by its country
  2. Voyages_simple cascade is a cascade of forms and subforms where you select the continent from a table grid, then the country, finally the city and this is transfered to a new voyage.
  3. Voyages_cascading_listbox implements cascading listboxes by means of the above linked “power filtering” method. This is a bit clumsy to use because it requires a lot of mouse-clicks.
  4. Voyages_cascading_listbox_AutoRefresh is rather similar to 3) but this time a macro saves most of the mouse-clicks
    countries_cities.odb (65.7 KB)
How to associate two comboboxes
#7

Thank you. I like option 3, but the additional clicks isn’t very user friendly. I think I can make option 2 do what I need. Thanks very much.

#8

In the macro free version (3) the buttons belong to their respective successors. They take the focus. They refresh their containing form. This way you finish listbox entry with a mouse-click on a button which takes the focus away from the previous form to the next form. Losing the focus saves the predecessor form to the filter table. The button refreshes its current form including the listbox based on the saved content of the predecessor.

The embedded macro code for option 4) requires that you save the document in a “trusted directory” according to Tools>Options>Security>[Macro security]
The code is generic. It can be used with all forms and subforms without any adjustments to the code. A hidden control named “AutoRefresh” attached to the logical form configures the elements to be refreshed.
[Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes.
The macro version of this solution has a different structure. The push buttons between the listboxes belong to their respective predecessor. The push buttons do not take the focus. They save the stored listbox selection in their respective form to the filter table. They are default buttons (triggered by Enter). This way you finish the listbox entry with Enter. The record is stored, the refresh macro is triggered, refreshes the next listbox and the focus moves to the next listbox.

#9

OK so I think I have this sussed now. One more question though. Why does this not appear to work very well without a cascade. So I have one subform and I need to select a value to go into the main form?

#10

A subform shows a record set filtered by the associated values from its parent. New subform records inherit the associated values from the parent form.

A listbox writes one value to a field. Its list content is not affected by any subform parameters.

Thats all the functionality. There is nothing more. We have to combine these two chess pieces in creative ways. You may find some other way.

#11

So how do you select a value from another table to create a link to the parent? This is something that is ridiculously easy in Access using the Lookup Wizard. Am I missing something?

#12

This is what the listboxes do. All my listboxes write integer primary keys of some other table. ID of continent into filter table, ID of country into another record of the filter table, ID of city into “Voyages”.

#13

Thank you, got it now. I was overthinking!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.