2 Replies Latest reply on May 12, 2010 1:09 PM by johnendahl

Music Library with complicated lookup issue

johnendahl May 12, 2010 12:11 PM

Title

Post

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

--> John

1. Re: Music Library with complicated lookup issue

philmodjunk May 12, 2010 12:20 PM (in response to johnendahl)

I can think of two options here.

1) Modify your music title fields to be unique. Your three Star Spangled Banner options might be changed to Star Spangled Banner, Orig; Star Spangled Banner, Mancini arr; etc. You can even set a validation on this field to enforce unique values for the future.

2) Script a name based search field where you can enter all or part of the music title and let a script search the table for matching music records. If there's just one match found, the ID number is copied to the desired field. If there are mulitple matches, you see a list of the matching titles with enough additional fields to be able to tell the difference and you simply click a row on the list to copy its ID number to where you need it.

Here's a tutorial on this second approach:
Tutorial: How to use an auto-complete drop down list when selecting records related by ID number
Like Show 0 Likes(0)

Actions
2. Re: Music Library with complicated lookup issue

johnendahl May 12, 2010 1:09 PM (in response to johnendahl)

Phil,

I think your script is the solution I'm looking for! Thanks.

--> John
Like Show 0 Likes(0)

Actions

Go to original post