I've got a database of musicians, with a bunch of info about them,
including their instruments. I want to list them by instrument.

How could I tack on to a layout that lists the musicians alphabetically
by name, a list by instrument? The list by instrument would look
something like: TRUMPET: Louis Armstrong, Miles Davis; PIANO: Bill
Evans, Oscar Peterson; VOICE: Louis Armstrong, Ella Fitzgerald...etc.

Thanks for any help.

<zimbabwhat (AT) gmail (DOT) com> wrote:

If there's never more than one instrument per musician, a field
"instrument" in the musicians table will do. If there's more than one
intrument, try a seperate instruments table and a join table where one
record is one instrument played by one musician.

Sort by instrument, then by name. Go to layout mode and add subtotal
ranges. Put the instrument in that range and view the result in preview
mode.
--
http://clk.ch

Thanks a lot for this course of action. Is there any way to automate
this? There's hundreds of musicians, and their instruments (there are
often multiple instruments per musician) are listed in an "instruments"
field, separated by commas when they play more than one instrument.

I'm also not too clear on how the "join table" should work. I created a
separate "Instruments" table. I manually entered each instrument. Now,
I create a "join table" - but how do I populate this? Do I need to
create relationships?

Thanks again.

In article <1162150016.231542.147330 (AT) e64g2000cwd (DOT) googlegroups.com>,
"dj" <zimbabwhat (AT) gmail (DOT) com> wrote:

Yes, if you have multiple instruments per musician, you need a
relational structure with three tables:


Musician, Instrument, and Musician_Join_Instrument.

Each table would have a unique ID number for each record, assigned
automatically, and not editable. You would need to make it editable for
the inisitial setup, but non-ediable thereafter. These should be number
fields, defined to be filled by serial number. Call these ID fields:

In the Musician table: kpMusicianID
In the Instrument table: kpInstrumentID
In the Join table: kpJoinID

The Musician and Instrument table would of course need other fields for
data peculiar to that entity (musician or instrument)

The Join table would also need number fields to hold Musician ID and
Instrument ID, to make the relationship. Call these two fields
kfMusicianID
kfInstrumentID

You may want to have other fields in join table that are peculiar to the
specific combination of musician and instrument, such as:
When the musician started playing that instrument, how good the musician
is with that instrument, etc.

The basic relationship structure is as follows:

Musician::kpMusicianID = Musician_Join_Instrument::kfMusicianID
Instrument::kpInstrumentID = Musician_Join_Instrument::kfInstrumentID

The notation Musician::kpMusicianID indicates the field kpMusicianID in
the Musician table, and so on.

What you are starting with is something like the join table, that is, an
assignment of each musician to a collection of instruments. You need to
go through some manipulations of that data to get it into a form that is
really useful. I have done that several times, and know how to do it,
using multiple relationships and scripts. The goal is to get to a
situation where each musician has one record in the Musician table, and
each instrument (trumpet, violin, bass, etc) has one record in the
Instrument table, and then the Join table has one record for each
combination of Musician and Instrument.

The way I would proceed is to start with what you have now, which I
presume is a single-table Filemaker database.

For each musician that has more than one instrument, make a duplicate
record and assign one instrument to each record. You then have one
record for each individual combination of musician and instrument. This
will end up being your Join table, after some more manipulations. Go
ahead and define the fields number fields kfMusicianID and
kfInstrumentID, but do not fill them yet. Define the number field
kpRecordID as a serial number, and go ahead and fill that using Replace
Field Contents by Serial Number, update next serial number. After you
have doen that, go to the field definition and check Do not allow
modification on data entry.

Define two other tables, Musician and Instrument, put the kpID fields in
them as described above, and define those fields to be automatically
filled by serial number, with modification on data entry NOT allowed.
Define other fields in those tables as needed for the Musician and
instrument respectively.

Import the data from the Join table you have already set up. The
musician data should be imported into the Musician table, and the
Instrument data into the Instrument table.

Manipulate the Musician table and the Instrument table to eliminate
duplicates. This is best done by a script, described elsewhere; I may
post a description of this later when I have time.

After you have eliminated duplicates from the Musician and Instrument
tables, set up a relationship as follows:

MusicianName::MusicianName = Musician_Join_Instrument::MusicianName
InstrumentName::InstrumentName = Musician_Join_Instrument::InstrumentName

Where MusicianName and InstrumentName are new table occurrences of
Musician and Instrument set up just for the purpose of relationship
based on names.

Now fill the contents of
Musician_Join_Instrument::kfMusicianID and
Musician_Join_Instrument::kfInstrumentID

by doing Replace Field Contents, using a calculation for each:

Musician_Join_Instrument::kfMusicianID = MusicianName::kpMusicianID
Musician_Join_Instrument::kfInstrumentID = InstrumentName::kpInstrumentID

The above are calculation formulas for the Replace Field Contents
commands, not to be confused with relationships.

Now the fields kfMusicianID and kfInstrumentID should be correctly
populated. You can remove the temporary table occurrences MusicianName
and InstrumentName, and thereafter manipulate the database usign the
primary relationships based on the ID numbers.

The reason for using relationships based on ID numbers is that those
numbers do not change, no matter how other data in a record may be
changed. if you were to use names for operating relationships, any
modification of a name would break the relationship.

After you are confident you have things working right, you can eliminate
the extra fields for Musician and instrument from the Join table, so
that data such as Musician Name only occurs in the Musician table, and
so on.

--
For email, change <fake> to <earthlink>
Bill Collins

Bill,

This is a very thorough explanation. I just wanted to offer a modified
approach to make it more automated and a little easier/quicker for a newbie:

Given that you have a table (Musicians) with one record per musician, and a
field called Instruments which has every instrument the musician can play,
separated by commas, then do the following:

0) Make a backup of your database file before making major changes.

1) Add to Musicians a new calculation field, TempInstrument, which has the
following formula:

Trim(
GetValue (
Substitute (
Extend(Instrument); ","; "¶");
Get ( CalculationRepetitionNumber )))

Specify that TempInstrument is to have 50 repetitions (or how ever many the
maximum number of instruments a musician could possibly know). This formula
will automatically break out the Instrument field so that you have one
instrument per repeating element.

2) Create a new table, Instruments. Add the fields:

ID (number)
Instrument (text)

In the Validation tab of Field Options dialog, specify that each of them
have to be "unique values only" and ALWAYS validate (don't allow user to
override). For ID, also specify an auto-enter serial number.

3) Import Musicians into Instruments.

- Map TempInstrument --> Instrument
- Do not import into ID

When you click the Import button, FileMaker will show you the "Import
Options" dialog. Make sure you select the option to "Perform auto-enter
options while importing" AND the "Splitting them into separate records"
radio button. Then click Import.

FileMaker will magically build the Instruments table so that you have only
one record per unique instrument. Each different instrument will be given a
unique ID number.

4) Create a new table, MusiciansInstruments. Add the fields:

MusicianID (number)
InstrumentID (number)
TempInstrumentName (text)

If you already have a field in Musicians which is a unique serial number
then skip to step 6.

5) Go to Musicians. Add a field:

ID (number)

In the Validation tab of Field Options for Musicians::ID, specify it must be
"unique values only" and ALWAYS validate (don't allow user to override). In
the auto-enter tab, specify an auto-enter serial number.

Then go to Browse mode. Make sure you're in the Musicians layout/table. Be
sure you "show all" records. Then click into the Musicians::ID field and
choose "Replace Field Contents..." from the Records menu. Choose the
"Replace with serial numbers" option (leave "Update serial number..."
selected) and click Replace.

6) Import Musicians into MusiciansInstruments.

Map Musicians::ID --> MusicianID
Map TempInstrument --> TempInstrumentName

When you click the Import button, FileMaker will show you the "Import
Options" dialog. Make sure you select the radio button "Splitting them into
separate records" and click Import.

Now you have the beginnings of a "join" file. You have a list in
InstrumentsMusicians of every unique combination of musician (known by their
ID) and instrument (currently known by its name). We want this to be a
"proper" join file so we have a couple more steps.

7) Go into the Relationships tab of "DefineDatabase."

- Position MusiciansInstruments so it sits between Musicians and
Instruments.
- Click on Musicians::ID and drag it on top of MusicianID in
MusiciansInstruments. FileMaker draws a line between the two.
- Click on Instruments::Instrument and drag it on top of InstrumentID in
MusiciansInstruments. Another line appears.

Now, technically you're done. The three tables are related to each other and
you could actually begin working with it like it is. However, we want the
MusiciansInstruments table to actually contain just codes (IDs) and not the
name of the instruments, for a variety of reasons. So, we need a couple
cleanup steps:

8) Go to the MusiciansInstruments layout. Be sure you "show all" records.
Then click into the InstrumnentID field and choose "Replace Field
Contents..." from the Records menu.

- Click "replace with calculated result"
- Choose "Instruments" from the drop-down menu
- Double-click the ::ID field so that the formula reads simply
"Instruments::ID" (without quotes).
- Click OK and then Replace

FileMaker copies over the correct ID number for each instrument.

9) Now we can clean up and add some final touches.

- Delete the field TempInstrumentName from MusiciansInstruments.
- Delete the fields TempInstrument and Instrument from Musicians.
- Go back to the relationships graph and delete the line between
Instruments::Instrument and MusiciansInstruments.
- Still in the relationships graph, click on Instruments::ID and drag it on
top of InstrumentID in MusiciansInstruments.

Now we have a "proper" join table which uses ID numbers. One more thing to
do in the relationships graph is to make it so that you can automatically
add and remove items from the join table when working with Musicians.

- Double-click the equals (=) sign that labels the line between
Musicians::ID and MusiciansInstruments::MusicianID.
- Under the MusiciansInstruments side of the dialog, click the two check
boxes "Allow creation of records in this table via this relationship" AND
"Delete related records in this table when a record is deleted in the other
table."
- Click OK

All that remains is to see the results:

10a) Show how you can see the Instruments for each musician. Create a new
layout based on Musicians.

- Add the Musicians::Musician field to the layout.
- Click on the Portal tool and draw out a box about 3.5in wide by 1.5in high
- The Portal Setup dialog appears. Indicate you want to show related records
from MusiciansInstruments. Select to show the vertical scroll bar.
- When you click OK, you get the "Add Fields to Portal" dialog box. Choose
Instruments from the drop-down list, then move the Instrument field over.
- Click OK and go to browse mode.

You'll see that each musician has their associated instruments listed.

10b) Demonstrate you can make a report that shows a list, by instrument, the
musicians who play that instrument.

Method One:
- Make a new layout, based on MusiciansInstruments. Make it of a type,
"Standard List/Report."
- In the Specify Fields step of the New Layout/Report dialog, choose
Instruments from the drop-down menu and move ::Instrument across.
- Change the drop-down field to Musicians and move ::Musician across
- Click Next
- Pick your theme and click Finish.
- The new layout appears. Click the part tool and drag it across so the line
is between the ::Instrument and ::Musician fields. You want a "subsummary
when sorted by InstrumentID."
- Sort the table by InstrumentID and preview.

Method Two:
- Go to the Instruments table
- Add a new calculation field, RelatedMusicians (text result) with the
formula:

Substitute ( List ( Musicians::Musician ); "¶"; ", ")

Add this field to the layout based on Instruments. You will see for each
instrument a list of the musicians separated by commas.

Thanks so much to both Bills for the extremely detailed how-tos.

I couldn't get this step 8 to work:

I've gone over everything a few times and when I click Replace, nothing
happens (meaning, the InstrumentID fields remain empty).

Thanks again - because of your help, I'm beginning to understand this
"relationship" stuff...

Whups. My error was in step 7. Should read:

7) Go into the Relationships tab of "Define Database."

- Position MusiciansInstruments so it sits between Musicians and
Instruments.
- Click on Musicians::ID and drag it on top of MusicianID in
MusiciansInstruments. FileMaker draws a line between the two.
- Click on Instruments::Instrument and drag it on top of TempInstrumentName
in
MusiciansInstruments. Another line appears.

That is, TempInstrumentName NOT InstrumentID

"zimbabwhat" <zimbabwhat (AT) gmail (DOT) com> wrote

Thanks! It works perfectly now.

How can I expand on this so that I can enter new musicians with new
instruments (and potentially add new instruments to old musicians, if
they learn them)? I think I'm stuck on how to allow the portal to
accept edits.

Or maybe I should quit while I'm ahead! :-)

Answer: Value lists.

The portal we created in the previous topic shows a "blank" record at the
end of the instrument list, but you can't really enter anything into it...
clicking into the blank space does nothing.

This is because the field I had you add to the portal belongs to the
Instruments table. What you really want to do is add new records in the join
table, MusiciansInstruments. But none of the fields from
MusiciansInstruments appears in the portal.

So, here's how to set up the portal and create a value list that will enable
you to easily add instruments:

1) Go to the Musicians layout, the one with the portal.

2) Double-click the ::Instrument field in the portal. The Field/Control
Setup dialog appears.

3) Change which field appears. On the right-hand side, drop down the menu
that says "Display data from Instruments" and change it to say
MusiciansInstruments. Select the ::InstrumentID field.

Don't click OK yet. But just consider for a minute about what we've done.

Before, you saw the instruments for that musician in the portal. It was
pulling the names of those instruments from the Instruments table. The
information wasn't stored in Musicians; that just has musician names and ID
numbers. And it wasn't stored in MusiciansInstruments; that just has two ID
numbers. It was through the power of relationships and the existence of the
join table MusiciansInstruments that FileMaker was able to "tunnel through"
and grab the right instrument name.

Now, we've changed it to InstrumentID in the MusiciansInstruments table.
That enables us to "click into" the field for the purposes of adding or
changing someone's instrument (as listed in the join table). But remember,
MusiciansInstruments only stores numbers. Do you remember whether
"xylophone" is instrument number 13, or was it 31?

That's what the left side of the box, and value lists, are about.

4) On the left side, choose "Pop-up Menu" from the Display-as drop-down
list. Some new options appear. The most important one is "Display values
from:" and it currently shows "<unknown>." Drop down that menu and you see
the only other option is "Define Value Lists...." Choose that, and the
Define Value Lists dialog appears. Click "New..." here.

5) Now we see the Edit Value List dialog. At the top, give it the name
"Instrument List." In the original versions of FileMaker, you would be
typing all your possible instruments into the "use custom values" section,
but the new versions of FileMaker are much more intelligent. Select the "Use
values from field:" radio button. The "Specify Fields for..." box pops up.

6) This is the "heart" of setting up your value list properly. Now remember
(a dozen dialog boxes ago) that you are trying to make it easier for people
to enter the proper NUMBER for InstrumentID into the join table,
MusiciansInstruments. We can't put "xylophone" into that field. We have to
enter 31 (or is it 13?). This field specifies how we will make that
super-easy.

- On the left side, with the heading "Use values from first field," choose
Instruments from the drop-down list of tables.
- The list of fields in Instruments appears. Click "ID" (remember, we need
to store the NUMBER of the instrument in the join table. If we stopped here,
you'd get a popup menu with not-so-helpful items like
1,2,3,4,...13,14...31,32.
- As soon as you do this, the heading on the right side, "Also display
values from second field," becomes enabled. SELECT this check box.
- FileMaker gives you another list of fields from Instruments. In THIS list,
choose Instrument (the field that has the actual name of the instrument).
- As soon as you click "Instrument" on the right, the check box option "Show
values only from second field" becomes enabled. SELECT this check box.

Now, what you've done is instructed FileMaker to consider the Instruments
table as the source of a popup menu that will show all the possible
Instrument names in your system, but "under the hood" it will actually put
the ID number of the instrument that is picked.

7) Close all four levels of dialogs by clicking OK until you're back at the
layout, then switch to Browse mode.

You'll see that there is a "blank" popup menu at the end of every portal in
the system. By clicking this menu, you will see all the instruments. As you
choose an instrument, another blank row is added.

We've got a couple more things to do before we're finished.

Go back to layout mode.

First, let's make it easy to get rid of all those instruments you added when
you were playing around with your new popup menu.

- Shorten the width of the ::InstrumentID field about half an inch.
- Type the word "Delete" in the space you created and make the text
tastefully small and maybe in red ink. (You could also drop in your favorite
little icon for deleting things.)
- Right-Click on the text (or icon) and choose "Button Setup..."
- In the Button Setup dialog that appears, scroll down to the "Records"
section, select "Delete Portal Row" and click OK.

Second, let's hide the distracting 3D border around the popup menu. Click on
the ::InstrumentID field and set the pen width to None.

Return to Browse mode and enjoy your new, fully-normalized relational
database with whizzy user interface features!

Oh, a couple notes/comments.

* Some of those other Control Style options look really cool, but the popup
menu is the only one that will show the instrument name both while it's
"normal" and while it's "popped up." Edit boxes and drop-down lists will
revert to the stored ID number after you select an item.

* You might be tempted to use the check box formatting, but that won't fill
out the join table properly. The check boxes actually work a lot like your
original system did, putting multiple values into one field.... not multiple
values into multiple records.

* If you want to add a wholly new instrument, do NOT use the option "Include
'Other...' item to allow entry of other values" no matter how tempting it
appears. This will NOT properly create a record in the join table, and you
(or your users) would doubtless put in the name of the instrument when the
field actually requires/uses an ID number. Instead, go to the "Instruments"
layout and add one there.


"zimbabwhat" <zimbabwhat (AT) gmail (DOT) com> wrote

In article <6didnb5zmYzVSdvYnZ2dnUVZ_qidnZ2d (AT) comcast (DOT) com>,
"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote:

Bill Marriott,

I really appreciate your whole description in this thread.

Thanks,
Bill Collins

--
For email, change <fake> to <earthlink>
Bill Collins