Forms with sub-sub-forms

Creating and using forms

Forms with sub-sub-forms

Postby Didcut » Fri Nov 08, 2019 8:50 pm

I've read through several tutorials and read through documentation but it's still not clicking for me. I feel like what I'm trying to do should be relatively simple I just can't figure out how to do it.

I'm trying to create a database for book titles and because people can sometimes be authors or editors I want a separate table for People, one for people who are authors and the titles they have authored. I think this means I need three tables to hold the relevant data. My problem has been creating a form that allows me to actually input this data. I ultimately want to expand this to cover things like Editor and Illustrator but I felt like this was a good starting point.

So I've got a "Person Table" with FirstName Last Name PersonID
A "Author Person Table" AuthorID and PersonID
and a "Title Author Table" TitleID and AuthorID

Sometimes books have multiple authors and I believe this will account for that.

I've set up all the relationships but when I try to make a form to create a new Book Title it doesn't seem to connect at all with the other two tables.
LibreOffice 6.3 on Windows 10
Didcut
 
Posts: 3
Joined: Sat Nov 02, 2019 7:39 pm

Re: Forms with sub-sub-forms

Postby UnklDonald418 » Sat Nov 09, 2019 2:58 am

Welcome to the forum.

The form at the top level (probably MainForm) will use your Book Title table as a data source.
The SubForm will use the intersection table named "Title Author Table" as it's data source. The SubForm will be linked to the Master Form using TitleID.
To accommodate multiple authors the SubForm should have a table control with columns for both TitleID and AuthorID. If the link is properly defined, the TitleID column will automatically have a value inserted, so you could hide that column (don't delete it, just hide it).
The AuthorID column should have a ListBox Control. The query for the listbox control would use the "Person Table" as it's data source.
You enter information for a title on the top form then on the listbox on the SubForm you select Authors(s).

There is no need for the "Author Person Table"

If you need further assistance please upload a sample of your database
How to attach a document here
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1238
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Forms with sub-sub-forms

Postby Didcut » Sat Nov 09, 2019 5:30 am

OMG thank you that worked like a charm and I feel like I'm finally starting to understand how this works. My only question now is how do I display the title of the book and the name of the author in the listbox/field form so I know what I'm selecting? Here's a copy of my little database.

Thank you for your help.
Attachments
People Database.odb
(14.34 KiB) Downloaded 4 times
LibreOffice 6.3 on Windows 10
Didcut
 
Posts: 3
Joined: Sat Nov 02, 2019 7:39 pm

Re: Forms with sub-sub-forms

Postby UnklDonald418 » Sat Nov 09, 2019 7:37 am

I made some modifications to your form document and saved it as Title Table01
People Database01.odb
(24.15 KiB) Downloaded 7 times

Let me know if you have questions.
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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1238
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Forms with sub-sub-forms

Postby F3K Total » Sat Nov 09, 2019 10:17 am

One additional hint:
TitleAuthorID is not necessary. If you replace in table "Title Author Table" current primary key "TitleAuthorID" by using both "TitleID" and "AuthorID" as a combined primary key, see attached picture, you prevent the table from having an author-title combination twice.
PD.png
PD.png (23.55 KiB) Viewed 168 times
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 934
Joined: Fri Dec 16, 2011 8:20 pm

Re: Forms with sub-sub-forms

Postby Didcut » Sun Nov 10, 2019 9:46 pm

I tried to expand my form so I could get other inputs like "editor" but when i added the table control for the editor table now I can't input a new person in the person table. Is this possible or do i need a whole separate form for inputting each new person so i can assign them as an "author" or "editor"?

I was hoping to be able to input the title information, author, editor, publisher, illustrator, fiction/non-fiction, a source URL of a copy of the book, and some topic information like "historical" "wood craft" "children's book" etc. Just so you understand where I am trying to get with this.

thanks for your help thus far.
LibreOffice 6.3 on Windows 10
Didcut
 
Posts: 3
Joined: Sat Nov 02, 2019 7:39 pm

Re: Forms with sub-sub-forms

Postby Villeroy » Sun Nov 10, 2019 10:27 pm

I downloaded Uncle Dons example, applied the changes suggested by F3K Total, deleted one form, tweaked the other one a little bit and added one with reversed roles.
The 3 tables represent a many-to-many relation between Authors and Titles. Each author can have zero, one or many titles and vice versa, so every book can have zero, one or many authors. This is called a "many-to-many" (m-n) relation.
You have a list of authors, a list of titles and a table which maps unique combibations of authors and books.
A form reflecting a many-to-many relation has one list represented by a main form and the mapping table is always the subform.
The subform is linked through the common item ID shared with the parent form.
The subform has a table control in order to show many related items at once.
In this case the subform has only one column which represents the IDs of the related items.
These related IDs are represented by a listbox showing the related item's name in front of the hidden ID, so that you actually enter an ID by selecting a name. This is what list boxes do.
The sub-subform is linked to the subforms item through the common IDs. It simply shows the additional details of the selected subform item that are stored in the other list. The sub-subform is linked to its parent by the common ID.

List A --> Mapping Table --> List B
[A.ID] --> [AID ; BID] --> [BID]
Details --> linked items --> Details
Main Form --> Subform --> Sub-Subform
Autors --> Authors&Titles --> Titles
Titles --> Authors&Titles --> Authors
Attachments
People Database02.odb
(25.87 KiB) Downloaded 2 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27299
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Forms with sub-sub-forms

Postby UnklDonald418 » Mon Nov 11, 2019 2:30 am

In answer to your question about adding Editors
Building on the version Villeroy uploaded, I added two Boolean fields (Author, Editor) to the Person table and set the default value for both to No.
They allow you to choose if a person is an Author or an Editor or both.
That allows the listbox queries to show only Authors or only Editors.
Since there are slightly different queries for each listbox, the queries are now stored in the listbox controls. To see those you need to look at the Properties List box dialog for each listbox
I added a Title-Editor intersection table and a form document Titles - Authors - Editors
That form document includes a separate Form for Person entry/edit.
Attachments
People Database03.odb
(38.03 KiB) Downloaded 2 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.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1238
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 4 guests