join table data entry from parent w/ portal

[ken_s2007]

I'm trying to learn the rudiments of data entry when creating a two-way join table, using one of the parents and a portal for data entry, as opposed to creating the association in the join table layout itself (either in a join layout directly by the user, or scripted).

Following is what I've done so far, and it seems to work, but I wanted to check if I'm missing anything. I've kept this as simple as possible. I'm following normal conventions for the primary ("kp") and foreign ("kf") field options.

A 'song' must have 1 or more composers.

A 'composer' can have composing credits on 0, 1, or more songs.

Tables:

Composer: kp_Composer_ID; ComposerName

Song: kp_Song_ID; SongName

Composer|Song_join: kf_Composer_ID; kf_Song_ID

Composer::kp_Composer_ID related 1:many to Composer|Song_join::kf_Composer_ID. "Allow creation of records in this table via this relationship." in Composer|Song_join::kf_Composer_ID.

Song::kp_Song_ID related 1:many to Composer|Song_join::kf_Song_ID. "Allow creation of records in this table via this relationship." in Composer|Song_join::kf_Song_ID.

In a Composer layout, there is a portal with:

Composer|Song_join::kf_Song_ID, using a value list with Song::kp_Song_ID as the first field; and Song::SongName as the second.

Composer|Song_join::SongName, which gets filled in after the ID is chosen.

Composer|Song_join::kf_Composer_ID; and Song::SongName as the second.

ComposerName which of course has the value of the record you're at, in the new (uncreated) portal row.

I can create new join records in the portal. The only thing a bit odd, is the user sees the current ComposerName in a a new portal row, and that might confuse her. Anything else I'm missing? Thanks.

Ken

[T-Square]

Ken--

It looks like you're on the right track. Keep in mind that a large song set will make it more difficult to use a dropdown for selection.

One point though: if I'm reading this right, you're including the ComposerID in the portal for the composer's song list. If so, I don't think you need this--a user won't be changing the composer to another composer from the composer window (are they?). If you remove that, then you don't confuse your users. (The Join::kf_Composer_ID field automatically gets the current composerID and is unnecessary in the portal for that reason).

HTH,

David

[ken_s2007]

David,

You're correct about leaving ComposerID and Composer out of the portal -- the composer would only be the one on the active record. When I was experimenting with different methods, at one point I thought I had to have the foreign key ComposerID in there to make it 'work' in the join table, but it turns out I don't. Thanks!

And I hear you about a posssible long value list to scroll through in a drop-down.

Ken

[T-Square]

There have been discussions on how to create an autofill feature on a field that you might want to look at for the song field (I haven't done that myself). Of course, you'll still have to figure out how to handle different songs with the same title...