You'll need to use a different set up than separate composer fields in the Library table. Is this to show more than one composer for a given track? If so, this is a many to many relationship and you need a third table to serve as the "join table" between composers and Library. Your portal, in many cases, would be to this join table.
This is to show more than one composer for a given track, yes. Join table. Gotcha. I have an idea of how this works, but a little guidance would be greatly appreciated. How would I go about setting this up?
This question pops up so frequently, that I set up a widget in the Known Bugs List to write it for me:
Start with these relationships:
Composers::__pkComposerID = Composer_Track::_fkComposerID
LIbrary::__pkTrackID = Composer_Track::_fkTrackID
You can place a portal to Composer_Track on the Composers layout to list and select Library records for each given Composers record. Fields from Library can be included in the Portal to show additional info about each selected Library record (such as a song title) and the _fkTrackID field can be set up with a value list for selecting Library records by their ID field and a song title.
Ok, I think I get this. This would work if there were only one composer to many tracks, but as it is a many to many relationship, how do we get the composer ID to match potentially 4 composer IDs on the library table?
On the contrary, this is not the approach to use if there is only one composer to many tracks. This is specifically set up to link many composers to many tracks.
There would be NO composer ID's in the tracks table, only a unique Track ID. (This ID has to be unique over the whole library, it's not the track number for a specific CD.)
If the Song for __pkTrackID = 3, needs to link to composers with IDs of 3, 5 and 8, you would create three records in Composer_Track. They would all have _fkTrackID = 3 to link them to that track record and each would have a different value in _fkComposerID to link to a different composer.
This is where setting up a portal to Composer_Track on the Library layout comes into place. If "allow creation..." is enabled for Composer_Track, you can set up the _fkComposerID field with a value list of composers. When you select a composer, it create a new record with the current Library Record's track ID in _fkTrackID and enters the ID of the selected composer into the _fkComposerID field. If there's a second composer for that song, add another record in the portal where you select that composer's ID.
Alright. I think I understand. So let me ask this... If the relationships are set up right, when I put a ComposerID into the ComposerID field on the join table, should it autofill the Composer name? Same thing with the TrackID and the Track name? If it should, and it doesn't, what does that indicate isn't properly setup?
There would be no need to add a composer name field to the Composer_Track table under most circumstances, though this is possible. (It creates update problems if you misspell a composer's name and then go back in to correct the spelling--the name field in the composer_track table wouldn't update to show the change.)
Instead, the composer name field from the Composers table can be added to the portal row of a portal to Composer_Track placed on the Library table.
If the composer name field does not appear in the portal row when you enter/select a composer ID, then either the value entered into the field is not the ID of one of your composer records or the relationship is set up incorrectly.
Also, if you set up the composer ID field with a value list of composer ID's and composer names (the name would be "field 2" in the value list), you can set up the value list to only show values from the second field and use a pop up menu format for the field. The field will then store the selected composer's ID, but display the name.