[Solved] Newbie problem with listbox in a Form

Discuss the database features
Post Reply
jamesq
Posts: 10
Joined: Thu Jan 23, 2020 11:19 am

[Solved] Newbie problem with listbox in a Form

Post by jamesq »

Hi
I have been at this for some time and tried lots of examples but none work and I got quite a bit of integrity errors etc. I have 2 tables 1 Children and Staff. I want to create a list box or whatever in Children table that shows both fields Firstname and Surname from Staff table and the choice will be added in the Children table in a Teacher field.
No doubt it is probably simple but help would be appreciated.
I am running OpenOffice 4.1.7 and I have created the split database using the Create new split database wizard by DACM on this forum.

thanks.
Last edited by Hagar Delest on Fri Feb 14, 2020 9:01 am, edited 1 time in total.
Reason: tagged solved
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie problem with listbox in a Form

Post by Villeroy »

Source type: SQL
Source:

Code: Select all

SELECT "Firstname" || ', ' || "Surname" AS "Visible", "ID" FROM "Table" ORDER BY "Visible"
"Firstname", "Surname" = field names
"Visible" = alias name for the concatenation with comma
"ID" = primary key field
"Table" = name of table
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
jamesq
Posts: 10
Joined: Thu Jan 23, 2020 11:19 am

Re: Newbie problem with listbox in a Form

Post by jamesq »

Thanks Villeroy for quick replay that worked fine, the names come up in list box but do not transfer to the table after choosing.
The form I use to input date into my main table and I want to allow choice of selecting a teacher or doctor from respective tables.
Last edited by jamesq on Thu Jan 30, 2020 1:23 am, edited 1 time in total.
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie problem with listbox in a Form

Post by Villeroy »

jamesq wrote:Thanks Villeroy for quick replay that worked fine, the names come up in list box but do not transfer to the table after choosing.
The list box should not transfer the chosen name. In 99% of all use cases it transfers the referenced table's primary key (the row ID) into the foreign key field of a form.
If you really want to write strings, set the bound field to 0 (which is the first column, the visible one). This would be somewhat faulty database design.

[Example] Relations reflected by list boxes in forms
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
jamesq
Posts: 10
Joined: Thu Jan 23, 2020 11:19 am

Re: Newbie problem with listbox in a Form

Post by jamesq »

Thanks Villeroy. I can live with entering data manually but for handiness I wanted to scroll the list box and choose the name to populate the table the form is referenced to. The form's data source is the Children table and the listbox source table is Teachers and shown in the list box is First Name and Surname from the Teacher table. When the user clicks the appropriate name then that would populate the Staff field of the Children's table. Is this possible and if so what would secure way of doing it. Sorry for such basic question.
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Newbie problem with listbox in a Form

Post by Villeroy »

You copy the names, so they are stored a hundreds or thousands times. If you store the names in the teachers table and reference them by relations (numeric foreign keys) each name is stored only once in the table. If a name changes (by marriage or anyhow), you change the name in the single place.
Relational database design makes it possible that every detail about something is stored only once. In my example database you can rename any person, animal or thing and the change takes effect in all instances because all items are referenced by their unique ID number.
A list box shows some memorable string in the visible first column and keeps the corresponding item ID in a hidden second column. The item ID for the displayed item is written into the underlying table.

By the way: you don't have to scroll a listbox. When it is focussed, you can type the searched name with the keyboard.
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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Newbie problem with listbox in a Form

Post by UnklDonald418 »

I uploaded a simple listbox demonstration.
The form document has two table controls.
It doesn't matter which one you use, type in a Student Name then select an instructor name from the list box.
The table on the left shows FirstName SurName
The table on the right show SurName, FirstName
The difference is in the Listbox query
Attachments
Demo47_ListBoxes.odb
(12.43 KiB) Downloaded 196 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
jamesq
Posts: 10
Joined: Thu Jan 23, 2020 11:19 am

Re: Newbie problem with listbox in a Form

Post by jamesq »

Thanks UnklDonald418 - Looks interesting. Under pressure at moment but will study later. Looks pretty good though.
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
jamesq
Posts: 10
Joined: Thu Jan 23, 2020 11:19 am

Re: Newbie problem with listbox in a Form

Post by jamesq »

Thanks for help. I ended up putting 2 listboxes in the form based on the table field and using the following SQL code to give me the options and fill in the field. Two fields in the table both related to primary key in 2 other tables tables Staff and Agencies. Here's the code I used
1
SELECT "name", "ID" FROM "docagent" WHERE ("title" = 'Doctor') ORDER BY "name" ASC
2
SELECT CONCAT ("firstname", ',',"surname"), "ID" FROM "staff" WHERE "driver"='1' ORDER BY "firstname" ASC

This seems to work fine to me. If I am on the wrong track I appreciate you letting me know.

Thanks
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
Post Reply