Creating form for many-to-many relationship

[japhyr]

Hello everyone,

I am trying to create a database to record notes about what I read. I have set up three tables that I want to deal with right now: sources, authors, and source_authors. The table sources contains the title of the source (a book or an article etc.), and a sourceid. The authors table contains first name, last name, and authorid. Since a source can have multiple authors and an author can have multiple sources (books), I use a third table to store the connections between these. This is the source_authors table, with only three columns: sources_authorsid, sourceid, and authorid. A screenshot of the relationships is attached:

Relationships

I would like to make a form for entering a new source. To see how all the parts work, I used the wizard to make a main form that includes all the fields from sources, and a subform that includes all the fields from source_authors. These are linked on the sourceid field. With the subform selected in Form Navigator, I created another subform that includes all the fields from the authors table. This subform is linked to the first subform on the authorid field. A screenshot of this form is attached:

Form

Problems:

• The first source shows up correctly. It displays the correct source_authors indices, and displays the correct author. The second source does not show up correctly. When the second source is displayed, the correct source_authors indices are displayed, but the author does not show up.
• Entry acts strangely. I can enter a new source, but when I tab from the source title, something flashes in the source_authors boxes, and it goes to a new record. If I visit the sources table, the new source is listed. If I open the form again and go to this new source, I can't enter an author's name unless I make up a new authorid in the source_authors fields.

I have looked around, but can't find a simple explanation of how to make this work. Any suggestions, or links for how to do this?

[Villeroy]

May be it helps to have a simple reference implementation of such a relation. Have a look at form "Movies" in Drew Jensen's movies_2.odb.
Open the form and test it. Each movie can be associated with one or more genres.
Open the form in edit mode.
Get toolbar "Form Design"
Push it's 5th button to call the "Form Navigator"
Call the properties from the context-menu of entry "MainForm" in the navigator.
The abstract form is bound directly to table "Movie", thus including it's PK "MovieID" and being editable.[your "sources".sourceID"]
The form includes some pairs of a label is associated with a box. The box bound to a field of the underlying table. [I would disable the MovieID control, since it is auto-generated. You could even remove that control as long as the PK remains part of the form's source.] [your first row of form controls]

Additionally it contains a subform. Click "SubForm" in the navigator.
The abstract form is bound directly to table "MovieGenre", thus including it's PK and being editable.[your "source_author"."sourceAuthorID"]
Both forms are linked to each other through their respective fields "MovieID", so each selection of a movie in "MainForm" picks all entries in the subform with the same "MovieID" in table "MovieGenre". [your "sourceID" in both tables]
The subform contains a single list box, bound to field "GenreID" of the form's source table "MovieGenre". The list box is wrapped into a grid control, which shows one instance of the list box for each matching GenreID. Less important detail: The surrounding grid control is tweaked a little bit for appearance. It shows no record selector nor navigation bar and the GenreID field is removed. So the grid appears more like a list box containing other list boxes.

Your grid control represents the linking table directly!

The most difficult part (for me at least) is the way how list boxes are bound to fields. We can not select the box in the navigator. Click the grid's column header to show the list box properties and choose the "Data" tab.
It's source is a simple query: SELECT "Genre Name", "GenreID" FROM "Genre" [one could append ORDER BY "Genre Name"]
It's bound to the numeric field "GenreID" of it's form. That is field index 1 of the underlying query. Field index 0 is the name-string to be displayed in the list box. Always take care that the displayed values are the first field of a list box's data source. The bound field can be accessed by an index where 1 is the field index of the first field behind the displayed field. In other cases you may use index 0 as bound field in order to write the displayed values into the bound field.

Edit: Forgot to mention how you can change a text box in a grid control to list box: Call "Replace with..." from the column header's context-menu. Edit,Edit: Don't do that with version 3.0. It will crash. This has been fixed in 3.0.1

[japhyr]

Thank you for the detailed response. I had seen this example database in another thread, but when I looked at it I could not figure out how to recreate it. Your last paragraph was most helpful in understanding how to set up the list box within the table control. I have been able to clean up my form so that it looks like this now:

Form

I don't fully understand the "bound field", but I'll pay more attention to that if my forms get more complicated. Is it enough to understand that the first field in the select statement must be the field I want displayed in the list box?

The only problem I have left with the form for now involves entering a new source. If I click in the "Title" box and enter a title, and then try to tab to the list box, it goes to a new source record entirely. In form navigator, I right click on the main form and choose tab order. Only the fields on the main form are listed, and none on the subform. If I right click on the subform and choose tab order, only the table control is listed. Is there a way to allow tabbing through the entire form?

[Villeroy]

All I can tell about tabbing in forms: http://user.services.openoffice.org/en/ ... =39&t=9484

Regarding property "bound field", you write an integer ID number into "source_author"."authorID". The number corresponds with the displayed string of field zero. In list box source ...

Code: Select all

SELECT "firstName", "authorID" form "authors"

... the name is the displayed field #0 and "authorID" is the bound field #1 ... Ah, this is really confusing: The field is double-bound. It reads value(s) of "authorID" from "authors" and when you submit the form the value(s) get written into "authorID" (list box property "Data Field") of the form's record set.

Form
|__Data Field <--[Name, ID] <--other table

[japhyr]

The bound fields are starting to make a little sense, and your further explanation clarifies it a little more. I'll study it more when it affects the form I'm trying to make.

Thanks again, you got me back on the road to making a useful database.

[japhyr]

Not quite solved yet! Here is what the form looks like now:

Source Entry Form

The list box works well for the source type, because it can only be one of a few choices. But I have a number of questions about the authors part of the form.

The authors list box displays authors correctly. If I add authors directly into the table, with the correct sourceID, they show up correctly in the form. But the main purpose of the form is to enter new sources. When I enter a new source, I can choose any author first name and any author last name. Is there a way to bind these fields somehow, so that I can only choose whole authors?

Also, I would really like to be able to enter new authors directly on this form. It makes sense to have to enter new source types separately in the list table, because new source types will be added rarely. But most new sources will have an author that is new to the database. Is there a way to add new authors directly on this form? Would a table subform be better suited to this than a listbox? If it makes any difference, there will be many articles in the database, each with multiple authors.

[Villeroy]

Yes, I've seen that but kept quiet because I was not so shure about the implications. I guess, the latest chosen part of a name puts the value for the "authorID"
Try this list source for a single list box in the grid and drop the second one:

Code: Select all

SELECT Concat(Concat("firstName" , ' ') , "lastName") AS "Name" , "authorID" form "authors"

add ORDER BY "lastName" if you like.
In direct SQL you can use HSQL's concatenation operator || instead of the clumsy concat:

Code: Select all

SELECT "firstName" || ' ' || "lastName" AS "Name" , "authorID" form "authors"

Edit: OOo 3 allows for concatenation by || in direct mode

[japhyr]

Thank you, that works very well for choosing existing authors from the database. That part of the form now looks like this:

Author Listbox

Author Listbox.png (6.2 KiB) Viewed 47031 times

To facilitate adding new authors from this form, I am thinking of adding a second subform with just firstname and lastname text boxes, or linking to a separate author form if linking is possible. I'll check those out and post back. If anyone has a suggestion for how to allow adding new authors, I'd love to hear it.

[Villeroy]

What you see in a form is a snapshot of the records at the time when the form requested those data from the database storage. After editing the form's values you have a "dirty record" in the respective form. The form's record is not in sync with the storage.
A button on toolbar "Form Navigation" allows for resetting the current record to the original clean state, another button lets you store the form to disk. Base stores automatically when you switch from a dirty record to another one. (always assuming complete and valid data here).

Adding a record to another record set than the form's current record set requires some steps before you can see the changes in your form's current record set:
- Keep your current form loaded with a clean record.
- Open your "authors" table.
- Insert a new record into the table (edit the very last row and store the new record)
- Switch back to your form, set the focus to your subform and refresh by using the button before the sort buttons on Toolbar "Form Navigation".
Now you can access the new author entry in your form.

How can we enhance your form to facilitate the process?
A subform links the record sets of two forms by means of common fields. You can not bind an existing record of one record set to a not yet existing new record of another one. Thus you will not succeed with another subform.
Loading another form document in parallel and switch between two form-windows is not as easy as could be.
- Close your form and reload it in design mode.
- Get toolbar "Form Design" and the form navigator from it's 5th button.
- Select the root (abstract container "Forms") and get a new form from it's context-menu.
- Get the properties of the new form, set it to "new records only" and bind it to tables "authors".
- You can use the 6th button of "Form Design" to add the required field controls with labels.
- Add a push button to your form and set it's property "Action" to "Save Record". The button provides the same functionality as the respective button on the navigatio bar.
Property "Default Button" lets the button listen to the Enter key while the form is in use, so you may fill out the form tabbing through the fields and save the record with Enter.
- You may add a reload button to the subform of your existing form.

When you are missing an author now, you set the focus to the new independent form on the same form-sheet, add a new author, reload the other form and pick the new author.

[japhyr]

Your reply makes very good sense. I played around with designing a browser interface for a database once, and in a sense it was easier. You could make a web form that would display current data, and collect new data. When the user submitted data, you could choose which data to insert into which table first, making sure the database was filled properly. With a form, we still have to control the order that information is entered into the tables.

I tried your first suggestion, just to see it work. It doesn't seem to work; the subform does not seem to refresh without closing the entire form. If I open the form, add a new source through the sources table, and then refresh the form, I see the new source in the form. But if I open the form, add a new author through the authors table, and then refresh the subform, I do not see the new author. I've tried refreshing the subform, refreshing the form, doing both of those with and without their respective tables still open. The subform never seems to refresh until the whole form is closed, and then reopened. Am I missing something?

[Villeroy]

I played around with designing a browser interface for a database once, and in a sense it was easier.

In any case you are using development tools where ease of use is a criterion for the end product rather than the production process. Would you allow internet users to add new authors to your database? If so, the fundamental problem would be the same (set session-cookie, direct user to other form, if new author has been submitted successfully take him back to the previous refreshed form, ...).

I tried your first suggestion, just to see it work. It doesn't seem to work; the subform

No, no! Don't use a subform! Back to our common Base document movies_2.odb by Drew Jensen. I loaded the "Movies" form in edit mode and followed my own instructions.

Hierarchy of MainForm>Subform>Grid&RefreshButton plus Genres>Input Box&SaveButton

Adding an independent form (blue) to add new genre which appears after reloading the subform (red).

Both buttons could be used from the navigation toolbar, given that the right form has the input focus.In any case you have to add (and store) a new record and then reload the respective form(s) and subform(s) where the new entry needs to be used..

Edit: You only need to refresh the list box using the second refresh button on the navigation toolbar.

Hint: You can drag&drop elements in the navigator in order to associate them with other forms.

[japhyr]

I am getting lost somewhere in this.

Using the movies database, I tried your first suggestion to see if it works: I open the movies form. I open the genre table. I add a genre, "Romance". I save the record. I close the genre table. I go back to the movies form, which I never closed. I look at the genres list, and Romance is not there, as expected. I hit the refresh button, and Romance still does not appear. (Am I missing a step about "setting the focus"? I thought simply clicking on the drop down list or its table sets the focus on the subform.) I close the form, open it again, and Romance is in the drop down list.

I also tried modifying the movies form, as you described. Here is my modified form:

Movies Form

I find the same behavior with this modified form. If I add a genre, say "Documentary", and hit the "Add New Genre" button, then hit the "Refresh Genres" buttons, the new genre does not appear in the list. If I close the form and reopen it, the new genre appears in the list.

Can you see what I am doing wrong?

PS I did set the action of "Add New Genre" to Save Record, and "Refresh Genres" to Refresh Form. I did not set the "Default Button" property for either button. I also saved the database, closed and reopened the database, and the behavior is still the same.

[Villeroy]

This could be a bug in v2.4. At least I can not see what's wrong with your version. The "Default button" property is not so important. It's just a trick to finish input and save with one stroke of the Enter key.
I attach my version as movies_3.odb.

I can load the form "Movies", see "Saw" by James Wan, enter a new genre "Sadism", refresh the subform, pick "Sadism" as second genre below existing "Horror" genre, hit Enter and focus the main form to edit the next movie.

Edit: Meanwhile I tested the attached file with 2.4.2. Indeed, the entire form document has to be reloaded in order to get new entries into the subform.

[japhyr]

Thank you for clarifying that this is a difference between 2.4 and 3. I looked at upgrading, so I'll make a decision how to coordinate the openoffice upgrade with the ubuntu upgrade. For now I finished the form and hope to start using the database shortly. I'll close the form and reopen it now when I need to, knowing that the form will work better when I upgrade.

In any case you are using development tools where ease of use is a criterion for the end product rather than the production process. Would you allow internet users to add new authors to your database? If so, the fundamental problem would be the same (set session-cookie, direct user to other form, if new author has been submitted successfully take him back to the previous refreshed form, ...).

All I meant is that with a web-based form, you can separate data collection from data insertion more clearly. To display a form, you collect some existing data to display in the form, say the current list of authors, and display the form. The user can enter data, say a new source title, and list a new author. The user submits the whole form, and your processing code checks for a new author, inserts the new author into authors, then checks for a new source, then inserts that source into sources with the correct author correlation. There is no need to get a new author from the user, insert it, then present a new form to get the title. I don't want to make a web-based interface for this database, but I do want to understand how databases are built. This was an interesting clarification for me about the difference between setting up a web-based database and an application-based database.

[Villeroy]

The user submits the whole form, and your processing code checks for a new author, inserts the new author into authors, then checks for a new source, then inserts that source into sources with the correct author correlation.

OOo comes with it's own "Basic" (by any means) scripting language and it is accessible through several other languages. In this forum I avoid scripting and try to explain the software as is, since things become extremely difficult as soon as you try to outline a scripting solution for someone else, particularly a non-programmer. Furthermore, I try to avoid the impression that scripting is the "normal operation mode" of this application when you did not understand the office application or when in need to fix a bad document design (the typical "VBA application" I've seen so often).

[Shimrod]

Villeroy -
I've just come across this thread while searching for a many-many form solution.
Your answer is excellent, thanks very much for taking the time to explain this, particularly the extra about adding in new records by using another form.
There are a few people here who I come across time and time again who I admire greatly for their efforts in helping others out.
Many thanks in particular to

Villeroy
Drew Jensen
RPG

I'm new to OO, and am using Base as a front end to MySQL.
I've a bit of programming experience, but in common with others I've been surprised by the really tough learning curve in Office Basic. Trying to navigate through objects (even with the excellent XRay tool) is surprisingly tough. The OO developer documentation is extremely patchy, and there is virtually no context help (e.g. many of the object proprerties seem to be undocumented at first and second glance). It's taken me many hours just to crack the basics.

I'm a fan of OO, having migrated recently from MS office. I would say the biggest challenges for OO are
1. Getting the balance right between software functionality versus user experience
2. Documentation
3. A sensible learning curve.

Examples I would use to illustrate these are SolidWorks and AutoCAD. Brilliant software both, but with amazing help files and really excellent learning curves.
I hope OO can balance the great software engineering with an equal push in user experience.

[papijo]

@Villeroy,
I have tried your attached movies_3.odb file and it works fine, with one exception. If the end-user inadvertently adds a second instance of an existing genre to the Genre table, an error message is generated.

I would like to use a similar example in my application, and would prefer to trap the error before it happens, in order to issue a more user-friendly error message.
I want to put a link to a macro in Events > Before record action.
a) Where should I put the link to the macro: in SubForm or SubForm_Grid or GenreID ListBox?
b) How do I retrieve the value of the currently selected Genre in the GenreID ListBox?
I know a bit about AOO BASIC, I know how to retrieve a selected value from a "normal" List Box, but have never yet worked with a "Grid".
TIA!

[MTP]

I would try using the Before Updating event on the GenreID ListBox (I haven't tested that, it's just the first way I'd try).

A listbox inside a grid is just like a freestanding listbox. You get the listbox object by going through the grid object:

Code: Select all

oListbox = oForm.GridName.ListboxName

Be careful to name the listbox; when the grid is initially created the wizard will name it 'Text Field 1' or something like that.

[papijo]

Thanks for your prompt answer, MTP. Actually I just found the answer to my question in that discussion: viewtopic.php?f=39&t=24013

[papijo]

I am attaching a revised version of the movies_3.odb file with macros controlling addition & deletion of records.
Hope that helps. Any remarks welcome.

[Villeroy]

User defined error messages are possible with an external database such as HSQL 2.3: viewtopic.php?f=61&t=68434&p=333105&hil ... ge#p332997

There are only three possible built-in error messages in a well made form:
"Violation of unique constraint", something with "nullable column" when the record is incomplete and something with "referencial integrity" when you try to delete an item that is referenced by another table's primary key. The other errors are avoidable by means of list boxes and subforms.