Combo vs List Box

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Combo vs List Box

Post by Villeroy »

The attached example document demonstrates two ways how to collect repeating items in a list.

###################################################################################################

The first method makes use of a combo box. It stores data from the input form "Collection_Combo" to the equaly named table. Query "qDistinct_Combo" feeds the combo box with distinct item names that have been used so far in the "Collection_Combo" list. This is an informal appropach that does not require any database relation.

###################################################################################################

The second method makes use of a list box. It stores data from the input form "Collection_Listbox" to the equaly named table. Query "qListboxItems" feeds the list box with names and item IDs from the related table "ListboxItems". The list box itself is fed by query "qListboxItems" (names and IDs of items). The list box shows item names but enters the corresponding Item-IDs into the collection table. You can only enter items that stored in the related table "ListboxItems". The same form document includes a second form to enter new list box items into the white text box.
This is the way how you handle one-to-many relations by means of list boxes. Unlike the combo box approach you save a pointer (a reference ID) to another table which may store a lot more information about each item than just the name. In this form you enter availlable item-IDs to your collection list.

A 3rd form document is named after table "ListboxItems". It shows the 1-n relation from the other side. You enter collection entries related to the item selected in the parent form which shows the availlable items of table "ListboxItems". The parent form has a dependent child form showing the related collection entries of the item selected in the parent form.
A second child form is based on query "qAggregation" and shows the sum, average and row count for the item selected in the parent form.

The picture shows the relation between the availlable items and their related collection entries. Call menu:Tools>Relations from the database main window. The fields marked by a yellow key are primary keys (unique "row numbers"). The "Item_ID" of the left table is called "foreign key" because it points to the other tables primary key.
One-to-many (1-n) relation between a collection list and a list of availlable items. menu:Tools>Relations
One-to-many (1-n) relation between a collection list and a list of availlable items. menu:Tools>Relations
Listbox_Relation.png (16.72 KiB) Viewed 16309 times
The one-to-many (1-n) relation makes sure that you can not enter any Item_ID on the n-side that does not exist on the 1-side of the relation. This is called "referential integrity". By default you can not edit or delete any item-ID from the n-side that is used on the n-side. The latter restriction can be modified. Double-click the relation line between the tables.

The primary keys (item-IDs, indicated by yellow key icons) are unique "row numbers". The database adds them automatically as you enter new records to the tables. They also improve speed when looking up related stuff in multiple tables.
There is also an additional unique index on table "ListboxItems" to guarantee that you can not enter the same item name twice into list of availlable items. The index was added via right-click table "ListboxItems" > Edit... and then menu:Tools>Index Design... These additional indices help to improve speed while they my enforce uniqueness beyond the uniqueness of primary keys. Excercise: Add a non-unique index on the item names "N" to the combo-collection table. It will speed up filtering when the collection grows.
Attachments
List_vs_ComboBox.odb
Example with flat collection of text items represented in a combo box and a collection of related items represented by a list box.
(72.14 KiB) Downloaded 922 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply