List boxes not displaying in form and not inserting content into table

Version: 24.2.6.2 (X86_64) / LibreOffice Community
Build ID: ef66aa7e36a1bb8e65bfbc63aba53045a14d0871
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Hell again everyone. I have run into my next issue and though I’ve spent several hours trying to figure it out, I have to ask for some assistance to get through my next hurdle.

I have watched this video here on how to setup your database. He does a good job but I am not having some luck with my list box when I try to create my database. I am not getting an error and do not know what to do or search for to get this to work. In a previous configuration, I could get the list to display but not input into the table when I save the form. Now I can’t even get the list to display. I don’t know what to do. Another thread mentioned that the Wizards are pointless. But to someone who is just learning, the wizards are essential.

Maybe I shouldn’t be learning Base without getting some other essentials under my belt, but I don’t know what those essentials are.

If anyone can please be patient with a newbie, I would greatly appreciate it. Thanks again to the community for the help you provide.
New Database 3.odb (13.1 KB)

It seems to me, that your aim is to insert to which Company the product belongs.

Keeping it simple, by using the wizard, i obtained the list you were looking for. Hope it is ok to you…
New Database 3-001.odb (22.2 KB)

It works! What did you do differently?

I had to listen with care to what the wizard is saying… so nothing special.

Do not worry about that, try with combo-box too, whose procedure is simplier.

Have a nice day

First I would recommend changing the structure of your Tables (see RobertG posts): However, here are some choices for the direct fix:

  1. The ‘bound field’ of the list box data properties should be “0”, not “1” if it is from the table ‘Companies’. There is only one column in table ‘Companies’ and column indexes start at “0”, not “1”.
  2. The Company “list box” can also be a “combo box”. Simply right click the list box… then ‘replace with’ - ‘combo box’. In the ‘data’ tab of the combo box ‘control properties’, the ‘Type of list contents’ should be ‘SQL’ and the ‘list content’ being: SELECT “Companies” FROM “Companies”.
    Both methods only make the company names available for selection, but upon selecting, nothing is going to happen. If upon selecting a “Company” from the list you wish to show its corresponding data, you will need to use an event macro which will filter the form based on the selection, or by using a push button control which is simpler (no macro), but not as streamlined! There are tons of demos on both ways to filter a form.

I would never create a table, which only contains one field. “Companies” will show the field “Companies” as primary key. This field is for text content. So you save the same text content with a listbox in “Consoles”. So you could delete “Companies” and insert content directly by a combo box. You could set content of the combo box by query to show only different values of the companies in “Consoles”.

Better: You create a table “Companies” with a field ID (Integer Autovalue) and “Companies” as text content and a field “Companies_ID” as INTEGER in “Consoles”. So “Consoles” will only save the values as INTEGER while the table “Companies” will save the text content.

1 Like

@NEW_LIBRE_USER

In full respect of RDBMS Normalisation, find hereafter an update.
The query will show a readable list.

Regards
New Database 3-002.odb (14.1 KB)

It looks like you change the selection from Table to SQL for Type of list contents. This change worked for me as well when I recreated a database. But would you know why selecting Table works in the video at the 14m55s mark but it does not work for me? I don’t see what I’m doing wrong to not get it to work if I select Table instead. It’s not a wrong Bound Field value because I’ve tried multiple values, including 0 and 1. Thanks again for your help.

@NEW_LIBRE_USER

All depends on the goals you want to achieve, whereas, if your intention was to keep it unofficial, then you may use -001. On the contrary, should you use it seriously, we have to do it according to RDBMS statements for Relational Data Base Management System.

Could you therefore post your file with the bug? So that I could see if I was wrong?

Using your posted odb I simply set the list box control data properties: Data field = nothing, ‘type of list contents’ = Table, ‘list contents’ = Companies… NOT Consoles!.. and the ‘Bound field’ = 0… Company list shows just fine! However, when you select a company from the list box, nothing is going to happen because no event macro or action has been assigned to the list box!

@skyandrews

The event assigned to the list box is a Query. As shown hereafter.
Please avoid to criticize a job properly done. Thanks!
New Database 3-002.odb (14.3 KB)

List box properties

Combo vs List Box

I think I have an idea of what is going on. First off, yes, I understand that having a table with a single column is not good databasing @RobertG. But I am only trying to learn at the moment and to get past this point in the tutorial that I am using linked in my first post. @nn45 provided a wonderful working example using queries and I very much appreciate it. But for some reason I had an issue with the tutorial that I was using and the list box not displaying the items when “Type of list contents” selected was Table.

I think I figured it out. When I re-downloaded the file that I originally attached to my first post, New Database 3.odb and I open up the form, the dropdown list does not work. When I change the Bound field from 1 to 0 as @skyandrews instruction, it still does not work when I leave Design Mode. If I save the file as it is, reopen it and access the drop down list, it now works! Does this happen to anyone else? Meaning if you change the Bound field from 0 to 1 and then 1 to 0, does it not display the list contents until you save and reopen? If this happens to you as well, does this mean it is a bug? I’ve spent way too many hours to try and figure out what I am doing wrong, but if this is a bug, someone please confirm for me so I can know that my time has not been wasted. Thanks again for all your help and the great community here.

As with any other design changes to forms and controls in libreoffice, the changes must be saved and the form reopened before taking effect. This is normal operating procedure, no bug.

Design changes to a form are more directly made by first right clicking the closed form in the LO forms list and selecting “Edit”… The “Design Mode” of an open form, as you have mentioned, requires a few more clicks of the mouse in order to secure any changes.

I can drive a screw with a hammer but it is not the fault of the screw if it doesn’t hold the boards together the same as if I used a screwdriver.

List Boxes were designed to work with a 2 element array. Element 0 being the Display field which is normally Text and element 1 the Bound field is normally an Integer value. When you try to use a ListBox control outside those design parameters, like the screw you can’t expect it to work properly.

As has already been suggested a ComboBox control is the correct tool to use when you only have a single value in your table.

For a tutorial see Combo vs List Box

Thank you for the tutorial @UnklDonald. Much appreciated and I will check it out as I am looking forward to increasing my knowledge on databases. I understand that I am using the wrong tool. But What I don’t understand is why Libre Base doesn’t perform as I think it should when going from Bound Field 0 → 1 → 0. That seems like a bug to me and at this point I’m asking for someone else to try this out to confirm whether I am crazy or not so we can report it to the Dev team.

There is no bug. When you mess with the Boundfield in a ListBox Control you should not expect it to work properly.