[Solved] Relationship

Discuss the database features
Post Reply
IreneEng
Posts: 19
Joined: Thu Jul 27, 2017 10:00 pm

[Solved] Relationship

Post by IreneEng »

I've created 3 tables for a library:
table a: books
table b: authors
table c: world events

But I don't know how to link them:
1. in Authors table, I've 'first name' and 'last name', how can I combine them, and to use the combined in the pull down - see 2 below
2. when I enter a book, I'd like to have 2 pull-down fields, one from 'writer's name' field in Authors table, and 'name of the event' in World Events table

Thank you in advance
Irene
Last edited by Hagar Delest on Sun Nov 05, 2017 11:31 pm, edited 1 time in total.
Reason: tagged [Solved].
Apache OpenOffice 4.1.2
Windows 10 ... I think
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: relationship

Post by FJCC »

The usual way to handle this, if each book has one author, is to have the primary key of your Author table be an auto incrementing integer that serves as an id number for the author and have a column in the Book table to store the id number, not the name, of the author. In your form, you would then use a list box that displays an authors name but stores the id value in the Book table. The events can be handled similarly. However, books can have multiple authors and authors have multiple books. To handle this case, a so-called many-to-many relationship, you need to make an intermediate table. Each author and each book would have an id number and the intermediate table would have a column for each id. If a book has three authors, it would have three entries in the intermediate table, one for each author. A web search for database many to many relationships should lead you to better explanations than I can give.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: relationship

Post by UnklDonald418 »

As FJCC pointed out you will likely need to use an intermediate table to handle the potential many to many relationship between books and authors. The same is probably true of world events.
I uploaded a little demonstration of how that might work. Besides the 3 tables you described there are 2 intermediate tables where all the relationships are stored.
There is one form that demonstrates how you could use ListBox controls to display/select authors and events for a given book.
Demo13Books.odb
(14.46 KiB) Downloaded 145 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
IreneEng
Posts: 19
Joined: Thu Jul 27, 2017 10:00 pm

Re: relationship

Post by IreneEng »

Thank you FJCC and Donald!!

One more dumb question: booksTOauthors & booksTOevents are entered by hands? I'm still in my own dreamland of a 'pull down' so the Base knows which row matches with which ... But .... in reality, i.e. IF I've 1,000 authors, when I buy a new book written by an existing author, I need to search him/her in the authors table and get the corresponding number and enter it in the intermediate table with the new book's number? :shock:

Where and when, have I gotten the notion of 'pull down' ?? :crazy:

Thank you so much
Apache OpenOffice 4.1.2
Windows 10 ... I think
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: relationship

Post by UnklDonald418 »

When using Form01 in the demo I uploaded there is no need for the user to ever know the number of the author.
Open Form01
Use the main Navigation bar to select an existing book or click on New Record to add a new book and type in a a new Title.
In the Author table click on the first empty cell in the Author column and a down arrow should appear at the right of the cell. Click on the down arrow to see a selection list of all the names in the authors table, sorted by the last name and then the first name.
If you type a letter, for instance M, the selection cursor will jump to the first author whose last name begins with M.
Select a name from the list and press Enter on the keyboard and a new record will be added to “booksTOauthors” including the foreign key values for the current book and the selected author.
I did leave the ID field visible in the Authors table during the testing phase. That is the record number in “booksTOauthors”. In a working table that column would be hidden.
One drawback of this strategy is that the author must already be in the ”authors” table. To overcome that another form would be needed.
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
IreneEng
Posts: 19
Joined: Thu Jul 27, 2017 10:00 pm

Re: relationship

Post by IreneEng »

Thank you so so much Donald :super: You're my hero! :super: It works wonder and I see the 'pull down' :bravo:

I added more fields in the tables but unable to modify (add these fields into) the Form, after reading the following links,
https://wiki.openoffice.org/wiki/Docume ... abase_form
viewtopic.php?f=83&t=28235

Is it even possible?

Thank you so much
Apache OpenOffice 4.1.2
Windows 10 ... I think
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: relationship

Post by UnklDonald418 »

Yes, adding Controls and Forms to a Form document and connecting them to a data source isn't terribly difficult.
Right click on the form document Form01 and select Edit to enter the Form Design mode.
Along the bottom of the screen are a row of icons, 4th from the left is Form Navigator, click on that.
The Form Navigator window shows the structure of the form document. On Form01 there are 3 forms, MainForm, AuthorSubForm, and WorldEv_SubForm. Click on MainForm and on the form document you will see a selection frame with 6 green handles surrounding the controls associated with MainForm. Likewise click on AuthorSubForm and you will see a selection window around the Author table.
In the Form Navigator right click on MainForm and select Properties to open the Form Properties dialog. Select the Data tab to see the data source for MainForm.
Use the Form Control toolbar along the left margin to select the type of control you want to add to the form document. For instance select Text Box. The mouse pointer should change to cross hairs that you can use click and drag to add the new control. The Form Properties dialog should change to display the Data tab for the new control. On the Data field row click on the down arrow at the right of the row to see a list of available columns. If you added a column to the "books" table it should appear on that list. Select a column name and it will be connected to your new control. Click on the icon for Design Mode On/Off and you should see the new control populated with a value from the “books” table.
Click on the Design Mode On/Off icon again. In the Form Navigator window select AuthorSubForm. To add controls to a table right click an empty space on the header row of the table (you may need to expand the width of the table using the green handle on the right center), and select Insert Column. Again select Text Box and a column named Text Box 1 will be added to your table. If the Properties Test Box dialog is still open select the Data tab and select a column from the list shown in Data field (if the dialog is not still open right click on Text Box 1 and select Column). If you added a new column to the “authors” table it should appear on the list. You can change the name of the column heading (and a list of other properties) on the General tab of the Properties Text Box dialog.

See the User Guides for more complete coverage of table design.
The AOo guides can be found at
https://wiki.openoffice.org/wiki/Docume ... e_Chapters
There is some coverage of forms in Ch8 - Getting Started with Base
I found the LO Base Handbook to be more helpful.
http://www.libreoffice.org/get-help/documentation/
It has a chapter devoted to forms.
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
IreneEng
Posts: 19
Joined: Thu Jul 27, 2017 10:00 pm

Re: relationship

Post by IreneEng »

Use the Form Control toolbar along the left margin to select the type of control you want ...
Thanks Don but what is 'Form Control toolbar' Am I at the right place - pls see the red circle

Image
Apache OpenOffice 4.1.2
Windows 10 ... I think
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: relationship

Post by UnklDonald418 »

The Text Box control is the second icon below the one you circled.
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
IreneEng
Posts: 19
Joined: Thu Jul 27, 2017 10:00 pm

Re: relationship

Post by IreneEng »

what is the key to take/ok the action? There is nothing under any of the 3 tabs on top (General/Data/Events)
Apache OpenOffice 4.1.2
Windows 10 ... I think
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: relationship

Post by UnklDonald418 »

Any changes you make on General or Data tabs of the Properties dialog take effect immediately, no need for confirmation.
For example, select a Text Box control and on the General tab scroll down and change the Background color. The Text Box will immediately change to the new color.
Once you have completed your changes to the form document and try to close it, then you will get a confirmation dialog asking if you want to Save the Changes to the Form.
The Events tab is for advanced users writing Macro code.
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
Post Reply