Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Self-join portal with multiple options

Clayton P. King

Recommended Posts

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'm not a programmer, but a sort of user nerd, so 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. 


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



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 othe 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. 



KHSongbook Relationships.JPG

Edited by Clayton P. King
Link to comment
Share on other sites

2 hours ago, Clayton P. King said:

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)

This is the starting key to what you want to do.


What you have:  one field, Name, which has a value such as "Clayton" or "Vicky" when only one person is singing, but which is *empty* if the song is a duet or trio or ensemble or whatever; and a different field, MultiName, which is empty if the song is sung by just one performer but contains a string like "Duet-Clayton/Vicky" if the song is performed by multiple performers.

What you NEED:  one field in that table that contains all the performers regardless of whether there is only one or more than one on that song; and when there are multiple performers, there needs to be a hard return separating them so each is on its own line.

What you really OUGHTA have:  Performer ID, a serial number field in Performers, and link the other tables to a record in Performers by Performer ID not Performer's name.  If you've got somebody named, let's say, Puff Mommy, and next year she wants to be referred to as P Mitty instead, you'll want to be able to change the name field in Performers without breaking your relational connections.  But this is a digression from the immediate concern at hand so I'm going to bracket that off for now.


You could retool your system so that you no longer have separate Name and MultiName fields, or you can keep what you've got and create a calculation field of result type "text" and use that to anchor your relationships.  If you go with a calc field, you can Case out whether or not MultiName is empty and go from there:


Case (IsEmpty (MultiField); Name; Let (snippet = Middle (MultiName; Position (MultiName; "-"; 1;1)+1; Length (MultiName)); Substitute (snippet; "/"; "¶") ) )


When it's not just Name (in other words when MultiName isn't empty), if MultiName is "Duet-Clayton/Vicky" then snippet is "Clayton/Vicky" and the substitute function gives you:




...on separate lines where they can function as a multikey in a relationship.  


That gives you a start.  As long as the stats for song length etc are identical for all the different people singing on the same piece, that should let you snag summary info for any individual performer.


Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use