Music Library with complicated lookup issue
I have created a home-grown Filemaker solution for our concert band library. The library contains roughly 1,000 selections. Each selection in the library is linked via a linking table to composer and arranger information. These links seem to work as expected.
In addition to this basic library functionality, I also have concert performance table in this database, which is linked to the music table via another linked table. I use a portal on this performance table to record our various programs, using pull-down lists. I have a field in the portal linked to the music table, and I can select the song we performed using the pulldown. I can then go to the second line of the portal, pull down the title of the second song, and so on. This is where the problem occurs, however. We have multiple arrangements of certain songs, the "Star Spangled Banner" for example. In my Music table, each arrangement has a unique ID number, but an identical title. I generate the music pull-down value list from the music_id and music_title fields. If I sort the value list based upon the music title, the duplicated titles do not appear (i.e. I don't see three Star Spangled Banners, I only see one, and can't select a different version with a different composer and/or arranger). If I sort the list by the music_id field, now I *do* see each version of the Banner, but the list is now in numeric order rather than alpha, so the different arrangements appear in the order of creation (since the music_id field is an auto-generated serial number).
I had thought I might be able to "force" the additional arrangments to appear by creating a music_title_enhanced field, using the composer and arranger names. This would make the titles unique. However, I can't sort on that enhanced title because it contains the lookup values for the names.
Is there any way I can force Filemaker to show me all occurances of the piece in the pulldown, sorted in alpha order on the title? Thanks for any suggestions.
Filemaker Pro Advanced v11, Mac OSX 10.6.3