Statistical Help, please...

[dukewhite]

I have used FileMakerPro in the past for cataloging collections of videos both professionally and personally, but am now trying to take it in a completely different direction. I am a rabid david Bowie fan, and am trying to make a database of statistics for his current tour. My main objective is to be able to list the setlist each night, and have filemaker total up the number of times each song is played. Here's where i run into the problem. I have assigned each song a two digit code, and was trying to make the program search for each code and count the number of times they appear, but I am having trouble getting that to work. Can anyone offer any suggestions?

Version: v6.x

Platform: Mac OS X Panther

[Reed]

I would create two files (tables), one with a list of all the songs, and one for set lists, where each record has a song id number and a date on which it was played. All of the artist/title info would only be kept in the songs table. To summarize the total number of times each song is played, you need two things:

A relationship from the songs table to the setlist table where song_ID=song_ID

A calc. field that does a count of song IDs (in the songs table)

Use the relationship to create a portal in the songs file that show when each song was played and then use the calc field to do a count of the related records.

I'm sure there are other ways to do this, but this will work.

Dana

[dukewhite]

Does each date need to be in a seperate field, and if so how do you suggest I lable them? Or, can I just make a list of all the song ID's in one field on each setlist page?

For example, if there are twelve songs played on a given night (with ID's 01-12) do I need 12 different fields, one for each song, or can i just list "01, 02, ... , 11, 12" in one field and then search within that field in all setlists at once to pull up a total number of times that, say, 11 shows up?

I think my biggest problem with what I'm trying to do is that I don't know how to do functions and stuff like that on FileMaker, as I've only used ot for lists and label making in the past, never for calculations. Your help is greatly appreciated.

[Reed]

what you basically have is a many-to-many relationship between sets and songs. (each song can appear in many sets and each set can contain many songs) To properly account for this, you need three files. (In filemaker seven you can do it with one file that has three tables but in FM6 each file has one and only one table.)

I have attached something close to what you might be looking for. There are 3 files: sets, songs and setlist_items. If you open sets.fp5, the other two related files will open automatically (hidden). The set_id field relates the sets file to the setlist_items file and the song_id field relates the setlist_items to the songs file. If you create a new record in the sets file, you can enter the set date, location and whatever other information you want pertaining to the set. There is a portal in the sets file that shows all the songs relating to that set. These records actually live in the setlist_items file. I have set up the portal to allow addtion of records so if you scroll to the bottom of the portal, you can click into the song_id field and a popup list will appear with all the songs in the songs file. (There are better ways to select songs when you have a lot to choose from but this is simple) When you select a song_id, it will create a new record in the setlist_items file.

The songs file contains one record for each song. There is a portal in that file to the setlist_items file as well as a calculation field that counts the number of related records in the portal. The portal shows all occurrences of the the current song in setlist_items, so the calc. field would then count the total number of times that each song has been played.

Hopefully my late-night rambling is comprehensible. Let me know if you are still stuck.

Dana

Version: v7.x

Platform: Mac OS X Panther

song_example.zip

[dukewhite]

Wow! Thanks for the time you put in making those for me. It seems that they will work perfectly. Just one more question, though. Can I enter data into any of the three forms or do I have to enter it only into one of them? Again, thanks for all your help!

[Reed]

You can enter data anywhere, but I would typically enter songs into the sets file portal, since it will automatically create the join record with the correct set id. If you enter into the join file directly, you would manually have to assign set_id and song_id.

[dukewhite]

I'm on a roll now, entering all this data in here. You're a miracle worker! I would like to add one more field on the sets.fp5 form, to total up the number of songs listed in the scroll window, so I can then see how many songs were played that night. What function do I need to do when creating a new field to count the number of lines with data in them?

[Reed]

you would use the count function. Define a calc. field, and when the dialog comes up, find the count function in the aggregate functions group.... then you have to define what field you want to count. You could count either song_id or set_id. Just look at how I did it in the songs file to count the total number of plays for each song. It is analagous for the total number of songs per set.

Look in the help menu too, as it has descriptions and examples for all of the functions in the calcualtion dialog box. You should probably read the chapter in the manual about relationships as well... hope this was helpful

Dana