Proper Pop-up list

[Sam]

I have created 3 tables:

1. Songs

2. Collections (of Songs)

3. Concerts

I want the capability to show certain collections to be viewed in a pop-up list in table 3. My first try at this was to create a global repetition field that allows the user to select the collections he wants to appear and use in the pop-up list, but it will not allow me to index a global repetition field in order to create a popup list in the 3rd table. How can I go about doing this?

[Ender]

Why a repeating field? Why a global? Are you trying to build a Song list for each Concert or a Collection list for each Concert?

When you say "show certain collections to be viewed in a pop-up list", what do you mean?

[ESpringer]

Given you're on FM7, it seems to me that you actually want two more tables (and no repeating fields):

Song_Collect_Join for collection membership: that is, a simple two-field table whose records say things like Song1 is in CollectionA; Song1 is in CollectionB; Song2 is in CollectionC; Song3 is in collectionC, etc. Then your real collections table (which needs only one field, plus trivia) can display related songs in a portal very easily without any built-in limit to how many songs per collection, how many collections per song, etc.

Collect_Concert_Join:Then, you want another join table in which a concert is aligned with various collections. This will allow a value list with all related records of the songs that belong to the collections that are relevant this concert (if I understand your project correctly).

If you're just doing this for one-time data entry, you could keep the concert-collection join table very compact by creating a relation between ANY concert and ANY record in a one-field join table, and then just create and change/clear records in the join table as needed through a portal.

Since FM7 will tunnel relations through multiple steps on a path, you might as well use a well-articulated set of tables...

Does this make sense? (I haven't fully worked through all the details.)

[Sam]

Espringer, I don't quite follow you. Let me clarify.

I want to create in the preference section of my database the abililty for the user to select certain collection of songs to select from a popup list as he is designing a concert. I have 200 collections, but want the capability to choose lets say 10 collections to draw from, so I am not going through a million songs in all 200 collections. Once the collections are chosen, I want only the songs from the chosen collections to be shown in a pop-up list.

[ESpringer]

That's what I thought, and that's what I was suggesting a plan for, although I wouldn't call it a "preferences" section of the database...

Anyway, on spelling it out, now I recommend you add even one MORE table. Here's the logic: you have three kinds of things (concerts, collections, songs), and three kinds of connections: concerts have associated collections; collections have associated songs, and concerts have associated songs. Since all of these connections can be many-to-many, you should really have join tables. And in FM7, why not?

Added bonus: in the join files, you can do things like specify an ORDER. So, the JConcSongs file (which songs are in the concert) has records naming songs for a specific concert, and each record can also specify its order within the concert, or any other notes specific to how that song goes in *that* concert (extended version/with soloist... etc.).

Since you use multiple collections in setting up the song list for one concert-- especially if you don't want to limit the number of available collections in advance -- then I'd recommend setting up a table to connect the concerts file to the collections file. So, your relationship diagram should look like this (titles of DBs at top, basic fields and keys below):

JConcSongs:

[Sam]

espringer,

That makes sense! I'll give it a try.

Thanks for your help!

Sam