Layout design for many-to-many entry

[krainey]

I have three tables in a FMPro9 database which will track my books.

Tables (simplified to relevant fields):

Titles: id, Title

TitlesAuthorsLink: id, TitleID, AuthorID

Authors: id, Firstname,Lastname

Relationships are setup Titles.id to TitlesAuthorsLink.TitlesID and Authors.ID to TitlesAuthorsLink.AuthorID

I have the Titles layout able to view/edit/add Titles records, as well as view any linked Authors in a portal. Existing records in the Authors portal are displayed and may be edited via the popup menu connected to the field.

I can't, however, add a new row in the portal to add another author to the currently displayed title.

Am I able to achieve this while on the Titles layout, or must I do the 'add authors' while on a layout based on the TitlesAuthorsLink table?

[T-Square]

It should be easy to do; check to see whether you have the "Allow creation of records" option checked in the relationship properties (accessed by double-clicking the relationship on the relationship graph). With that setting, and a dropdown on the TitlesAuthorsLink.AuthorID, you can add an existing author by selecting them. Note that adding a completely new author may require more interface design.

David

[krainey]

I do have the 'allow creation of records' enabled on the TitlesAuthorsLink side of both relations.

The single field on the Authors portal on the Titles layout is set up as Control Style= popup menu, displaying values from "Authors List" (a value list), and Display Data From is set to the Authors table, Name field.

When I click into the first blank row of the portal, I would expect a new record to be created and be allowed to select another Author from the list. Nothing happens, however.

Thanks for your help.

[comment]

the Authors portal on the Titles layout

The portal should show records from the TitlesAuthorsLink table (that is where you want to create new records).

The field in the portal should be the AuthorID field from the TitlesAuthorsLink table. You can also display the name field/s from the Authors table in the same portal - but you should not be entering anything into them.

See also:

http://www.fmforums.com/forum/showpost.php?post/246136/

[krainey]

Beautiful!

Thanks much for the helpful information.