Combobox use to filter output in table

ddragps · July 14, 2023, 6:32pm

Hello
I have a db to manage orders.
Table Customers with Most details
Table Addresses with reference to Customers
Table Orders with reference to Customers and Statuses
Table Statuses referring to the Orders table

I need a form that shows me the all the orders based on the Status I choose, and on the side the Customer’s details.

I have created it with a table of Orders, a few fields from the Customers table and a combobox from Statuses. I would like to have an auto filter of the table based on the selection in the combobox, ideally without a macro.

I have seen many people posting answers to similar issues. They share a db with great examples. However, I have studied the examples and can’t find the appropriate tools to replicate.
If you can give me a step by step, it would be great.

I use Libreoffice 7.5.4.2 64bit in Windows 11.

Thank you
Dragos

Villeroy · July 14, 2023, 7:17pm

You may want to use listboxes for related fields. A combo box is just the same as a text box plus auto-complete.
Talking about a combo box, you can tunr on the form based filter and then pick the filter criterion and apply the form based filter.
Combo vs List Box

ddragps · July 14, 2023, 9:27pm

Thank you Villeroy. I am not even going to pretend that I understood what you said.
I have been teaching myself how to build this database with no previous experience with LibreOffice. The tables part was easy enough (had limited previous experience with mysql), but the forms are extremely un-intuitive in my opinion.
I changed the combobox to a listbox.
When I select an order from the table, the listbox selection changes to the correct status, but the table doesn’t filter based on my selection in the listbox. The 2nd is my need.
I am not sure if I am explaining correctly. Should I include some pics?
Thank you

ddragps · July 14, 2023, 9:36pm

ddragps · July 14, 2023, 9:37pm

Wanderer · July 15, 2023, 12:48pm

For me the hardest part to understand was the filter-table as main-form, then my usual form as sub form. And it is one of the few topics where I recommend video instead of a regular manual.
.
If you ignore the (german) comment you may watch the linked video, where a filter-table is added to a form. My crucial point is around 8:40 and showes how to move parts of form with the “navigator”. It is also shown, how to link the filter to the sub-form as parameter.

If you prefer english, check there is a topic at thefrugalcomputerguy, but actually I have not viewed it

Villeroy · July 15, 2023, 7:14pm

Listbox. Not Combo.
Database_t93580.odb (24.2 KB)

Edit: Filtering by status works best by a form/subform combination unless you have too many status entries. A subform shows all records belonging to the parent form’s selected item.
Selecting a distinct order status into the orders table takes a listbox. A listbox has 2 columns, a visible text and it’s correspoinding ID (hidden). You write the hidden ID value by selecting the text.
In normal edit mode, a listbox represents a value. It does not filter any rows until you switch to form filter mode. In form filter mode, the listbox lets you select a value which is used as filter criterion.