Combo Box filled with 2 fields from 2 different tables?

Discuss the database features
Post Reply
juhostyn
Posts: 4
Joined: Thu Jun 17, 2021 8:19 pm

Combo Box filled with 2 fields from 2 different tables?

Post by juhostyn »

How to create a Combo Box filled with 2 fields from 2 different tables. Exemple "book loans" create Combo Box field in form with 2 costumers(2 tables differents): table 1: teachers and table 2: students(I need only columns Name), other situation 2 type books(2 tables differents): table 1: books didactic and table 2: books literary(I need only columns ID)? You can help me?
LibreOffice 7.0 on Windows 10
juhostyn
Posts: 4
Joined: Thu Jun 17, 2021 8:19 pm

Combo Box filled with 2 fields from 2 different tables?

Post by juhostyn »

 Edit: Duplicate post from Beginners forum
Posting in multiple places is a waste of your time; we just delete or merge them -- MrProgrammer, forum moderatorr 
How to create a Combo Box filled with 2 fields from 2 different tables. Exemple "book loans" create Combo Box field in form with 2 costumers(2 tables differents): table 1: teachers and table 2: students(I need only columns Name), other situation 2 type books(2 tables differents): table 1: books didactic and table 2: books literary(I need only columns ID)? You can help me?
LibreOffice 7.0 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Combo Box filled with 2 fields from 2 different tables?

Post by UnklDonald418 »

While it is possible to write a query to populate a combo box from 2 or more tables, something like

Code: Select all

SELECT "StudentTable"."Name" || " - " || "TeacherTable"."Name" FROM "StudentTable", "TeacherTable"
the resulting list would likely be very long. For instance if you have 50 students and 10 teachers. the combo list would have 500 entries, unless there is some relationship between students and teachers that you have not mentioned.
A better approach would be to include in your table an integer field for a StudentID and another integer field for TeacherID. Then you could use Listboxes to select a Student and a Teacher.
See the tutorial Combo vs List Box
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
juhostyn
Posts: 4
Joined: Thu Jun 17, 2021 8:19 pm

Re: Combo Box filled with 2 fields from 2 different tables?

Post by juhostyn »

Sorry, I don't want to concatenate but to transform the two lists, one with the names of the students and the other with the names of the teachers, into one where I can choose whether it's the teacher or the student who will pick up the library book.
Attachments
Sem título.png
LibreOffice 7.0 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Combo Box filled with 2 fields from 2 different tables?

Post by Villeroy »

A combo box is exactly the same as an ordinary text box plus auto-complete suggestions. Whatever you type into it, will be stored in the linked database column.
A list box has one or two columns. A two column list box has the following source:

Code: Select all

SELECT "Visible Column", "Bound Column" FROM "somewhere" ORDER BY "Visible Column"
Bound column = 1 (which is the second column, 0 would be the first)
You select some text entry from the visible column (may be a concatenation) and the bound column's value is written into the linked column.
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: Combo Box filled with 2 fields from 2 different tables?

Post by UnklDonald418 »

to transform the two lists, one with the names of the students and the other with the names of the teachers, into one where I can choose whether it's the teacher or the student who will pick up the library book.
Perhaps it would be better to combine both Students and Teachers into a Persons table, with a boolean field to indicate if that person is a teacher or a student.
Likewise, a Book type field would allow combining both book tables.
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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Combo Box filled with 2 fields from 2 different tables?

Post by UnklDonald418 »

Combo Boxes are subject to data corruption that can be difficult to correct because typographical errors can be easily perpetuated, so it is best to use List Boxes where errors are much easier to locate and repair.
Because there is no relationship between "tb_book_diadactic" and "tb_book_literary" any attempt to JOIN the two tables will result in Catesian Product list.
However, that problem can be overcome by using a query something like:

Code: Select all

SELECT "book_diadactic_id",  "matter"  FROM "tb_book_diadactic" 
UNION ALL
SELECT "book_literary_id",  "title"  FROM "tb_book_literary" 
This appends the list from "tb_book_literary" to the list from "tb_book_diadactic"
It might be best to use a query similar to the one shown to create a View.
Then a ListBox query can get it's list from the View.
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
juhostyn
Posts: 4
Joined: Thu Jun 17, 2021 8:19 pm

Re: Combo Box filled with 2 fields from 2 different tables?

Post by juhostyn »

Thank to you UnklDonald418 and Villeroy, your clarifications helped me a lot, but I think about redoing my project.
LibreOffice 7.0 on Windows 10
Post Reply