AnsweredAssumed Answered

Showing indirectly related data

Question asked by AlastairMcInnes on Jun 17, 2012
Latest reply on Jun 21, 2012 by AlastairMcInnes

Title

Showing indirectly related data

Post

I have a table of book information - title, publisher, etc.

Each book may have multiple authors and/or editors.

It seemed to me to be good practice to create a table of Contributors to store the details of these people and then make a link table to tie them to the books with which they are associated.

So, I have Three tables (in this scenario):

Titles
Contributors
Contributions

The Contributions table had three fields when I first set the database up - the TitleID, the ContributorID (both numbers and being the foreign keys of the relevant tables) and the Role - a text field.

What I wanted to do on the layout was have a portal to display the unknown number of contributors - selecting those where the TitleID was the same as the current Title being displayed on the layout.

I ran straight into a problem - the portal won't let me show the Contributor's name because it's not in the Contribution record - only a reference to it is held there.

I have now added a fourth field to the Contribution record and made it a Calculation field with the result being:

Contributors::Full Name

I can add this field to the portal, no problem. However, when the layout is displayed, only the current line of the portal actually shows the name - if I click on one of the other lines, that name is displayed but the first one vanishes.

It must be possible to show all the names all the time, but I don't see what else I can do.

In reality, I'm told that no title will have more than 6 contributors (or perhaps we're just going to ignore the 7th and subsequent) so I could just have 6 fields on the title layout to show them but this seems a little untidy and not very good database practice.

I've attached a small screen shot to show the portal.

I'd be grateful for any pointers. 

Authors.jpg

Outcomes