Portals and relationships revisited

[Clayton King 0]

First, I appreciate any help I can get as this project has been evolving over two years and, as these kinds of projects to, has become more involved and complicated over that time. I've posted related questions, but exploring other parts of my project. As I'm not a programmer, but a sort of user nerd, my inquisitive mind wants to learn, but sometimes simple concepts elude me. Feedback, both on the specific goal at hand as well as the project overall is always appreciated.

Performers is one piece of a much larger project, but I'm trying to enhance the project and working on a piece at a time. 

BACKGROUND

I'm sharing the entire project layout to provide some insight into how the parts fit together. The application is a booking management tool for performers, consisting is several tables as shown:

Again my focus right now is the Performers table and how to use it in relation to the Gigs (and Gigs~Songs_Join) tables.

There is a Gigs layout which is the hub for performances by one or more Performers. On that layout is summary information about a gig and a portal which shows which songs were performed and in what order (Set & Slot fields). The portal also includes a Performer Name. The assumption at this point is that up to 4 performers will do a given gig. This might change in the future, and could impact the below goals.

Gigs~Songs_JOIN is a connecting table for Gigs, Performers, Songbook, Venues, Musicians, etc. Performers is a table of singers and includes fields for a serialized SingerID, name, area of speciality (Tenor, Baritone, etc.), etc., and additional fields for combinations of the same performers including: 

Multi: checkbox-indicates this record is more than one performer
PerformerName: calculated based on Multi: if NOT Multi, show performers Name, if Multi, show MultiName (below)
Type: value list-is this a duet, trio, quarter, all, etc.
MultiName: the description of this record (i.e., Duet-Clayton/Vicky)

Other summary fields include:

Total Gigs: count of gigs in which this person has performed
Total Songs: count of songs this person has performed
GigID: match field to Gigs~Songs_Join

GOAL

I have two goals, one related to the other. First, if the Performer record is checked Multi, I want to "attach" or "associate" individual singer(s) to the record (i.e., show what people are "included" in the MultiName). For example, MultiName=Duet-Clayton/Vicky, I want to show that Clayton and Vicky (each in separate records) are part of Duet-Clayton/Vicky.

Second, ultimately on the Gigs layout, I want to show which Performer sang which song (currently works in the portal), but more importantly, show variable summary information (goal layout below) which changes for each Gig as to each Performer, how many songs they sang and the total amount of time they sang. The catch is, in the summary I don't want to show the Multi records, but instead increment individual singers. So, in the summary info with related portal records in the example below, rather than showing Clayton sang one song, Vicky sang one song and Duet:Clayton/Vicky sang one song, I want to show Clayton sang 2 songs and Vicky sang 2 songs. In summary info, Duet:Clayton/Vicky wouldn't appear. In other words, the summary would show how much time each person was singing.

 In the summary block of the Gig's layout, each of the above sets is a one-row portal filtered by set. I want to have the SINGER1,2,3,4 replaced with the actual Performer's name, and each band/portal show summary info for the appropriate singer. 

Here is my relationship graph:

 

Edited Monday at 12:33 PM by Clayton King
typo

[comment 1,797]

There are two parts to your question:

1. How to organize the data;
2. How to present it.

I will address mainly the first part.

IMHO, you should have a checkbox field in the GigSongs table, to allow you to assign multiple singers to a performance of a song. Accordingly, each singer should have a single entry in the Performers table. You can easily tell that a song is performed by a duo or a trio by counting the values in the checkbox fields (or the related records in the Performers table). If you like, you could have buttons for often-used combinations of performers, that would enter their IDs as a return-separated list into the checkbox field. But the idea of having separate records for these combinations is counter-productive to your goal of summarizing the performances of each individual, whether solo or as part of a combo, together.

As for the second part, all I can say is that it's not easy. Filemaker is not good at cross-tab reporting. And there is no way to have a dynamic numbers of columns. And you seem to be missing a table of Sets. There are several ways to work around these limitations, with various levels of complexity. For example, you could script the creation of the summary. Or you could use a web viewer for the display.

There is a relatively easy way to produce totals for each performer, without breaks for sets: add a global GigID field to the Performers table and populate it with the currently viewed gig's ID. Then count the related performances and sum the related durations and show the results in a portal to Performers. (Note that if the same song can be performed more than once, you need to have the durations on the GigSongs table and take them from there.)