Maybe I should upload the FM file. I'm being told (now) that my naming conventions are nonstandard, although those were suggested to me by folks (IMO) much smarter than me... That said, perhaps picking at the application would help explain my confusion about your suggestions.
Zip file
I’ll take your file and make some edits either tonight or tomorrow night and post the results.
1 Like
I look forward to seeing what you do with it.
Clayton -- Here's a revised portion of your graph. I renamed your join table to "Performances" and created a second join table ("Type") to connect Performances to Singers. Is this close to how you want to show the singers on separate lines in the portal? (By the way, looks like Duration is formatted as a time field.)
1 Like
I think I see what you're doing here, but want to be sure I understand. Since I had a joinID in the Performances table (formerly Gigs~Songs_JOIN), are you just using that so you can count/sum etc., by singer (formerly Performers) via the TYPE table? I'm assuming the GIGS table would remain tied to PERFORMANCES by the GigID field in both?
I think part of what's confusing me in the nomenclature. What is "Type" representative of? This probably sounds nitpicky, but it confuses me. For example, in terms of how I thought of the structure:
GIGS is a table that contains info related to a particular concert.
SINGERS is a list of performers.
SONGBOOK is a list of songs.
MUSICIANS is a huge list of artists, composers, etc., who created/sang the songs in SONGBOOK.
Gigs~Songs_JOIN (now PERFORMANCES) pulled info for the portal on the GIGS layout to create/edit the actual setlist for a particular GIG.
So in terms of how what you've done fits in with what I have, I'm just trying to understand what TYPE actually is. I'm starting to see how it might work, but don't want to break my solution (yet-lol). I've also been tweaking things a bit since our last posts and found a couple of solutions to other problems.
[EDIT] It just occurred to me that perhaps the TYPE might speak to the issue of duets where more than one singer sings a given song, but we want the singer's count and duration to increment - not the "fake" record of "Duet-Clayton/Vicky" currently employed. Correct?
But please, keep going! I see it taking shape in my foggy brain.
The duration field IS a time field because when I started this thing, I couldn't find a way to enter the data any other way; folks from the FM Forum told me this was best, and just format it as 00:00:00 on my layouts. I'm absolutely open to better ways to do it as data entry is a pain for this particular field.
On the singer portal/count, one thing I realized is my brain was stuck on orienting data horizontally; that is, Set numbers as row labels and Singers as column labels. However, in your solution, pivoting that and having Singers as row labels and Sets as column labels seems smarter. I can count using portal rows something like this (I didn't sum the durations as it's from my sample excel file):
In this way, only singers who actually sang would show in the portal (my challenge with the old layout). Right?
1 Like
I prefer to use UUIDs for all primary keys, so I’m using join_ID as a tracking number for the performances. I’ll change the name of the join_ID field later. (Probably to performanceNo.)
Gigs remains connected to Performances by gig_ID, yes.
I am using Anchor Buoy to organize the graph; if you don’t know what that is there are a lot of articles on the net. AB uses groups of table occurrences that flow from left to right. The layout is based on the Anchor, or the left-most TO.
You need a join table between performances and singers because that is a many-to-many relationship. I called it Type but you can call it whatever you wish. For me, Type makes sense because of the song type (solo, duet, trio, quartet). This is what is driving the need for the join table.
I would make duration a number field and use seconds. Then convert to minutes with a calculation. (People with more experience might know why a time field works better.)
Have not experimented with the sets and counts report, yet. You may need to use a virtual list for that.
1 Like
In trying adapt to this model, a couple of things...
The portal on my Gigs layout (the actual set list) is broken. I'm working on that as I think it's simply a matter of pointing to the right TOs to populate it. One confusion is grabbing the singer's name for the portal on my Gigs layout. The confuses me because performances_type_SINGERS contains that field.
My PERFORMANCES table has 485 records in it, which represents the number of records formerly pulled into the portal on the Gigs layout.
Of course, the TYPE table has 0 records. My thought it to copy all the records from the PERFORMANCES table into the TYPE table for the purpose of populating portals that previously only looked at PERFORMANCES (formerly Gigs~Songs_JOIN). Presumably I can delete redundant fields from PERFORMANCES that are now in TYPE.
Also, since TYPE is meant to account for Duets/Trios, etc., I'm unclear how to populate the actual Gigs portal (PERFORMANCES) such that the Duet shows (i.e., Duet-Clayton/Vicky) but at the same time singer counts (for Clayton and for Vicky) increment separately. Or perhaps I'm getting the cart before the horse. Right now, the TYPE table only has four fields: joinID, performanceID, singerID and typeID.
As to AB layouts, I thought I sort of followed that (notwithstanding current naming conventions; mine were suggested long ago) inasmuch as the anchor was previously Gigs. Everything else flowed into it. There are several "groups" of things happening. For example, the Songbook pulls data from Musicians to show the original artist. A second TO of Musicians provides for related artists (i.e., cover artists, original artist, composer, etc.). Other than showing the Main artist for the purpose of a set list, they are not related to Performances per se.
Gigs, Venues and Accompanists all house information about a given occurrence, but are otherwise isolated as well. So the crux of it is managing the actual Performances and Singers who performed.
I'm honestly not being argumentative - I'm just trying to sort through it all (and my first COVID vaccine has me a bit muddle-headed)... That said, I've spent some time rearranging, renaming, grouping, etc., and also began color coding because while my naming conventions told me what was what, having something quickly visual seems to help (me at least). I'm posting an update view of my relationship graph, which may look like fruit salad to many. Also a grab of the Gigs layout; remember the SETS & COUNTS portal is trash at this point.
As always, thanks for your knowledge, and more, your patience!
You will still have Gigs as an anchor for your Gigs layout. I added a separate TOG and anchor for Performances in order to build a Performances layout. This is what I posted above.
You will have to populate the performance_ID and singer_ID foreign keys in TYPE to be able to connect performances to singers.
If a performance has three singers, the performance foreign key will have three entries in TYPE — one for each singer.
The Performances join table will have to include the set and slot data.
This is actually making sense to me. So my question about data, copying the PERFORMANCES data into the TYPE table would be a start, I think, since the bulk of it is there already. They I could delete the redundant data from PERFORMANCES.
In terms of data entry when creating a gig, what's the method of entering the singer (Duet-singer1/singer2) so that Duet shows on the portal such that it creates 2 records in TYPE for the two singers? Does that question even make sense?
You will want to minimize having duplicate data between the Performances and Types tables. I recommend you make a list of what data goes where on paper first. Off the top of my head I can’t think of any dupes other than foreign key fields.
The biggest short term task will be creating the records in the Type table, Every duet needs two records — one for each singer.
Ultimately, when you create a new Gig and select songs, you’ll have a script that creates records in the Type table. If it’s a solo, the script will create one record. For duets, it will create two records. For trios, it will create three records.
So in terms of records, PERFORMANCES should have one record per song sung, regardless of when (i.e., one record per song per gig).
TYPE would contain the same number of records as PERFORMANCES, plus additional records for multi-person songs (three singers=three records). Right?
Because the relationship between these two tables hasn't completely settled in my brain, I'm having difficulty determining which fields would go in which tables.
I’ve mentioned in another forum that I believe you’re likely able to fit your needs into a very common quote/ invoice, line item, product, resource model.
If I were not in the wild creating this post using the miracle that is the iPhone, I could possibly be more helpful with graphics.
Songs are material resources along with their creators. Your cover artists are human Resources. Your products are the combinations of those resources. From your quote / invoice / gig layout, choose a product via the line item portal.
2 Likes
Further, a line item of yours will show a static product combination of a cover artist and the material (song) they perform but along with variables of set and slot.
1 Like
I saw your post, but don't think I'm being clear in my goal.
For the purposes of this application, MUSICIANS are (i.e., composers, cover artists, lyricists, , etc.). The ROLES table has records for each Musician's participation on a song. Only one of those people would show on the set list associated with that song. So if Barbra Streisand was the original artist, she is checked as the Main Musician for that song. If Marvin Hamlisch composed the song she sang, he has a record with that song, but is not checked Main. In this way, if we want to assemble a list of songs Marvin Hamlisch wrote, we can easily find them, regardless if he is the original artist or not. In some (many) cases, the same person both wrote the song and is the composer, thus they have two records in ROLES, one of which will have Main checked, which causes their name to display on the set list (Gigs). The Musicians layout shows us what songs the Musician is involved with, where they are the "Main" person, etc. Also, collaborators and others for the same songs are shown in a separate portal.
.
In much the same way, SINGERS are the folks I hire to sing those songs, and have a single record in the SINGERS table. In the PERFORMANCES table, they will have multiple records which are tied to a Gig. It would be nice, in the portal at the bottom to show how many songs that Singer sang at a Gig. But I can live without it for now.
So I have a Gigs layout based on the GIGS table. On that layout is a portal to PERFORMANCES (the join table). A portal record from PERFORMANCES contains the Song, Set, Slot, Musician, Genre, Tempo, Year, Keys, Singer and Duration of each song.
The SETS & COUNTS portals are broken because of the multi-singer challenge, and that's what I'm working on. The changes @steverichter, along with users like yourself, have generously been helping me with were initially around this issue.
There are really two different but related things going on: 1 is the management of bookings (Gigs) and performance of those songs (Gigs layout above) and, 2 is a songbook which is a list of all the songs, with Musicians who created/originally sang them (below).
When there were only two singers, everything worked quite well. It was when I needed to allow for additional Singers that things went awry, specifically because my summarization of a Singer's involvement in a Gig was no longer easy to calculate. When there were two singers, I could simply have fields in the PERFORMANCES table that counted songs by set. Now that there are multiple singers, it's become a variable that's more difficult to address.
That made me laugh, which I needed very much!
@cpking -- this is what GIGS looks like using Anchor Buoy. Not sure everything on the right side is correct. I have not looked at those tables, yet.
2 Likes
@steverichter , cool. I've adjusted my graph to reflect these changes, although kept some of the groups in because of how they populate the Gigs layout.
In the portal you shared, which table is the Join (119 on the first row) coming from?
Also, Type="Duet"... where is that field?
Tangentially, one of the reasons I dislike such long TO names (notwithstanding AB "clarity" is that formatting layouts in Filemaker is one of the banes of my existence. Fields automatically resizing if I so much as sneeze at them has me spending literally hours fixing stupid little things.
Below is a merge field (in gray) which represents the same info as the Key fields (Start and End).
If I touch the mergefield (making it bold, for example), FM decides it should be larger (2nd image). This is particularly frustrating when formatting portals.
I know there are many discussions about layout all across the interwebs, but I've not found 'the' solution to the problem.
Okay-enough kvetching... So as I mentioned before, in an effort to avoid breaking the whole database, I copied all the records from my previous JOIN table (now PERFORMANCES) into the TYPE table. Now I need to remove the unneeded fields from both of those tables.
Assuming TYPE tells me whether the Singer is performing a solo or duet, I remain unclear on how to populate the Performer's name on the Gigs setlist. Notwithstanding creating a script to enter records in the TYPE table, I need the singer's names on the setlist so that when that list is handed out to all the performers, everyone knows who's singing what. All the way back to the beginning of this odyssey, I had 'fake' records for Duets such that two names were entered as a single singer.
Join is coming from Performances::_join_ID but we need to change that to performanceNo since it’s not a primary key or foreign key.
Type is coming from Performances::type.
I’ll tackle the singer and fake records issue tonight if I get a chance.
1 Like
FM18; yes I use cards for "quick entry" of musicians, for example, when editing the Songbook.
1 Like