Combo Box filled with 2 fields from 2 different tables?
Combo Box filled with 2 fields from 2 different tables?
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
Combo Box filled with 2 fields from 2 different tables?
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 |
LibreOffice 7.0 on Windows 10
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Combo Box filled with 2 fields from 2 different tables?
While it is possible to write a query to populate a combo box from 2 or more tables, something like
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
Code: Select all
SELECT "StudentTable"."Name" || " - " || "TeacherTable"."Name" FROM "StudentTable", "TeacherTable"
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Combo Box filled with 2 fields from 2 different tables?
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.
LibreOffice 7.0 on Windows 10
Re: Combo Box filled with 2 fields from 2 different tables?
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:
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.
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"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Combo Box filled with 2 fields from 2 different tables?
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.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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Combo Box filled with 2 fields from 2 different tables?
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:
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.
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"
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Combo Box filled with 2 fields from 2 different tables?
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