Sorry for not being clear. I see your relationship graph illustrates a many-to-many relationship between “gigs” and “songs”. However, this forces you to introduce “set” as a unique characteristic of the join.
While this will work, from a data modeling perspective there is nothing unique about the set number, correct?
In “real life” the arrangement is as I’ve indicated, is it not? “Set” is not a unique attribute of the current join, it’s part of the relationship between “gigs” and “songs”.
Similarly, “slot” is an attribute of a potential “set” table, not a unique characteristic of the current “gigs-songs” join table.
This is somewhat academic, I suppose, if adding a “set” table would have no added value to you.
Much (most?) of what I have developed was with the assistance of users on the filemaker (now Claris) forums. So naming conventions like "Role|Composer" were suggested in feedback obtained there...
The Set and Slot fields are not unique in the literal sense. They are used to portray the order of the songs, obviously. And by extension, early on, some counting/summing was done on them to get counts.
All in all, the solution worked fairly well until I needed to add additional singers. That was the monkey wrench.
I'm wondering if I could create another join table and somehow use it to calculate totals I'm looking for. Sort of in the same way the Musicians (Musician|Composer and Related Musician Names) and Roles (Role|Composer and Related Composers) are being used.
I''m including screen shots of the Gigs layout and the Musicians layout, both in layout and browse modes to see if that will help illustrate.
Is there a one-to-many relationship between the “gigs~songs” join table and “performers”? (One gig~song can have multiple performers.) If not, shouldn’t there be? This would allow you to summarize the number of vocalists associated with a performance.
I believe there is a one-to-many relationship between Performers and Gigs~Songs_JOIN.
Below is the actual (manual) count of the gig.
Set 1 is all Clayton with 12 songs; Set 2 is a total of 13 songs, performed by multiple people. (Note that as of now, Duets/Trios/All, etc. are separate singers - that is they each have a record in the Performers table). It's inelegant but I'm trying to reach my goal one step at a time.
In the Gigs layout (below), I have multiple portals. For the purpose of illustration, the Single portal (top right) filtered by Set 2 from the ~JOIN table is an attempt to count songs by singer.
There are 5 one-row portals (top center) which are also filtered by Set, one portal per set and a "total" underneath. (Assumption is 4 sets max).
I'm sure I'm just confusing things and likely become "too close" to the project at this point. Perhaps a vacation? In the top right portal, I'm just playing with different fields to see the outcome. In the ~JOIN table, I have two Summary fields: CountSetSongs (=Count of SongID) and CountSingers (=Count of SingerID). For grins and giggles, I duplicated these, changing them to Calculation fields with the same formulas (Count (SongID) and Count (SingerID), just to see what would happen.
They are displayed in the portal in this order:
CountSetSongs___CountSetSongs Copy___CountSingers___CountSingersCopy
What I get is the total number of Set2 songs (13), regardless of Singer for the summary fields. I'm frankly not sure what value the Calculation fields are showing (1).
That said, I'm sure Filemaker is doing exactly what I'm telling it to do; I'm just telling it to do the wrong thing!
Isn’t that part of the problem? In reality, that relationship should be many-to-many. One performance can have many singers, and one singer can have many performances.
Thanks, Oliver... I'll look at the ERD tool. But I have to say in the meantime, I thought that's what I already had. I thought my Gigs~Songs_JOIN table was used to join Performers (SingerID) and Gigs (GigID).
Yes, under your current structure this is an accurate statement. However, here’s an example of why this structure is not optimal: “Set” should be an attribute of “Gigs”, not the Join table. A gig has sets like a book has chapters.
Oh wow... this runs counter to everything I was told early on by folks in the Claris forums. In fact, even the naming convention was suggested by other users
So are you saying Set should be in the Gigs table and Slot should stay in the join? Or should both be in Gigs? I'm trying to conceptualize how that would work.
I guess I'll spend some time with the ERD and see what that turns up... I pulled up an early iteration of the database before there were multiple singers and see how much simpler it was; adding multiple singers has been the thrust of the complications I'm encountering.
I agree that each FileMaker solution should be backed by an ERD. However… ERD and relationship graph are not the same. @cpking mentioned he is a sort of user nerd, not a programmer. I suggest, then, that ERD be put to rest in the context of this thread.
Thanks, @bdbd. I worked in high tech for 20 years, but in marketing communications, so my understanding of dataflow models is enough to be dangerous. That said, I really want to get this right, and I love learning of any kind. Of course, my immediate goal is to create a usable application for myself and potentially others...
I am having a similar case at hand. A solution that was created a couple of years ago now needs a revamp due to new and changed business needs. What looked good and fit for purpose at the time doesn't pass the test today.
I took a fresh FM file and started drafting the ERD, using FMP as a tool. This helped me to overcome limitations that are inherent to the old schema.
I’d make Slots an attribute of the Gigs table. The combination of a Set and Slot identifies a specific Performance.
Don’t delete your Gigs~Songs table. Just add a Performances table. In the Performances table, create foreign keys for Gig_ID, Song_ID, and Performer_ID.
Wondering if you could elaborate just a bit more on your thinking with respect to tracking Set and Slot information?
I'm following your additions to this thread, and the introduction of the Performances table makes great sense to me, but I must confess that I am still in the dark as to where you are headed with the concept of the tracking of Slots and Sets.
Thanks @steverichter . I'll watch and see how this plays out. Definitely agree with Slots being an attribute of Sets, and also that a Set table is likely overkill. I'm still missing something about what you have in mind -- but that's ok. If I continue following this thread, I think I will likely catch on. Thanks for the elaboration. Very much appreciated.
the reason I'm confused about moving Set and Slot to the Gigs table is that, in my mind, the Gig is the basic Gig info (venue, date, time, etc.). The JOIN table includes the GigID so that from Gigs, I can pull all related with the same GigID (i.e., Set/Slot) which allow me to order the performance using those two fields. In other words, wouldn't the Gigs table become unnecessarily bloated?
