Checkbox set as pop-up menu?

[Kidd Cabbage]

Hey, so I'm new to this program, or databases in general. I'm trying to just make myself a personal database for all the sheet music that I own to organize and easily find them.

One field that I'm trying to check off is the instrumentation of the sheet music, but I want the checkbox to pop out. There are like a billion different instruments in these scores, and this field would take up like a quarter of a page on its own. Any way to do this?

[bcooney]

Hello and welcome to the forums!

I'm not sure, but perhaps you need three tables: Instruments, Score and a table that contains the combinations of the two (a join table) "ScoreInst."

Then, on the Score form layout, place a portal to ScoreInst. Relate the two tables by ScoreID. ScoreInst would also have the InstrID and that field would be formatted as a popup menu with a value list of InstrID and InstrName. This value list is from the Instruments table.

This structure allows you to assign as many instruments to a score, and to assure consistent data entry since you are picking from the list of instruments defined in the system.

[jdu98a]

Just to add to bcooney's response...

You want to avoid having to scroll through one long portal when searching for specific instrumentation later. To make things a little easier for yourself later I would suggest actually making several portals. One each for strings, woodwinds, brass, percussion, and special (for those occasional harpsichords or harold trumpets) This way when you go back later to find a piece that includes a part for saxophone, you can just take a quick glance at the woodwind portal rather than a wall of text from a general instrumentation portal.

If you wanted to get a little more complicated you could actually create unique relationships to the Instrument table based on a second SectionID field. By sections I mean instrument types (strings, brass, etc...) This would allow you to do more broad searches such as for scores that include ANY brass and ANY strings but NOT woodwinds. This would save you time over having to search for instruments individually.

Also...

You can leave violas off the list all together. They're actually not needed. The instrument was just invented to act as a separator between quarreling cellos and woodwinds. Little musician humor.

...sorry if you play viola.

Edited April 21, 2010 by Guest

[Kidd Cabbage]

Hey, thanks for the welcome and the reply.

I seems you know what you're talking about. The only problem is that I'm having a hard time following what you're telling me. I tried figuring and doing what you said, but no luck - I'm sure that I'm not doing it right, though. Here are my issues with following your directions.

1) I placed a portal on the Score form linking to ScorInst (I didn't select any options in the portal menu, as I'm not sure what most of them mean). Should I do anything with the field that appears at the top of the portal box on the form? When I get out of editing the layout, nothing from the portal shows up.

2) I went to edit the ScorInst layout, and all that's on there is InstrID. So, I clicked that field and made it a pop-up menu getting its values from a list that I made called "Instruments List." I can't figure out how to tie this pop-up data to anything relating to the Instruments table.

3) I don't really know what to do with this Instruments table. It has an InstrID and InstrName field, and the InstrID is related to the InstrID in the ScorInst table, but I haven't figured out how to use this table beyond just relating fields. The values in the above InstrID are coming from a value list, which is separate from the tables, if I'm not mistaken.

Like I said, I'm pretty new, so if I'm missing something that should be obvious, let me know. Maybe it would be easier for you to just give directions more step-by-step and idiot-proof. Your call.

Thanks again for trying to help me with this.

[bcooney]

Here is a link to comment's basic join table demo.

http://www.fmforums.com/forum/showpost.php?post/246136/

This demo should help you out. As you'll see, data entry in the portal (which is the join table), is enabled by allowing creation of related records in the relationship (dclick on relationship in Relationship Graph to edit). Also, value lists are used to populate the keys in the join table. Comment show the IDs and uses drop down lists (easier to see what's going on). I prefer to not show IDs and use Popup Menus. If the popups get too long, there are other techniques to select an ID.