How to populate a join table (newbie)

[ken_s2007]

I am trying to understand how to populate a join table. This is a very basic, newbie question.

TABLE: Composer

FIELDS:

pk_ComposerID (Auto enter SN)

ComposerName

TABLE: Songs

FIELDS:

pk_SongsID (Auto enter SN)

SongName

(JOIN) TABLE: Composer_Songs

FIELDS:

fk_ComposerID (relationship: many to one (pk_ComposerID)).

fk_SongsID (relationship: many to one (pk_SongsID)).

Composers can write many songs (actually compositions; I'm using "Songs" to make the example easier to read here.) Songs can have more than one composer. Since this is many to many, I create a join table.

Goal: Be able to track what songs composers have written, and what composers are credited for specific songs.

Flow: A user creates a new Songs record, and a Composer record (unless it already exists). Then I need to create an association of the two in the Composer_Join table.

I added two fields to the join table: Composer::ComposerName and Songs::SongName, and made the pop-ups using a value list, and Values from field pointing to the source table field. I also tried creating fields belonging to the join table, and using those same value lists. I cannot pop-up a value. I obviously don't understand how to make this work. Thank you.

Ken

[T-Square]

One way you could do this: On your song layout, add a portal based on the join table that includes the fk_ComposerID. Set this field up as a dropdown based on your Composers value list. Place your Composer::ComposerName field on this portal. To add a composer to the join file with the current song ID, you'd select from the dropdown. Oh, yes, the relationship between Songs and the join table must allow creation of records.

To add a new composer to the Composer Table, you could add a button that scripts going to the Add composer layout; a "Done" button there would trigger a script to close the window and refresh the value list--maybe even adding the composer to a new join record.

HTH,

David

[ken_s2007]

[...]the relationship between Songs and the join table must allow creation of records."

DOH, that was part of the problem, of course.

I'll pursue the idea of automating too. Thanks!

Ken

Edited April 21, 2006 by Guest