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.
[Solved] Newbie problem with listbox in a Form
[Solved] Newbie problem with listbox in a Form
Last edited by Hagar Delest on Fri Feb 14, 2020 9:01 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
Openoffice 4.1.7 on linux / Openoffice 4.1.7 on Win 10 / Libreoffice 6.3.4.2.0+ on linux
Re: Newbie problem with listbox in a Form
Source type: SQL
Source:
"Firstname", "Surname" = field names
"Visible" = alias name for the concatenation with comma
"ID" = primary key field
"Table" = name of table
Source:
Code: Select all
SELECT "Firstname" || ', ' || "Surname" AS "Visible", "ID" FROM "Table" ORDER BY "Visible"
"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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Newbie problem with listbox in a Form
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.
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
Re: Newbie problem with listbox in a Form
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.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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Newbie problem with listbox in a Form
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
Re: Newbie problem with listbox in a Form
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Newbie problem with listbox in a Form
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
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Newbie problem with listbox in a Form
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
Re: Newbie problem with listbox in a Form
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
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