Counting unique portal records based on a field value

[Clayton King]

Similar to a previous post but different.

My portal contains records where a musician (MusicianID) may have multiple entries based on participation on a song (SongID). My goal is for the song count (circled) to show how many songs this musician has 'touched' regardless of the role or involvement. In order words, in this example, the song "For Good" appears twice because the musician has two records for this song in the portal - one for Cover Artist and one for Comp/Lyricist. However, the song "For Good" should only get counted once in the summary field.

The song count field is a summary field =Count of SongID in the related table.

I'm sure this is a fairly simple exercise, but I'm going in circles.

[comment]

Assuming you have a table of Songs where each song has a unique entry, and your relationships are:

Musicians -< Roles >- Songs

a calculation field in the Musicians table defined as =

Count ( Songs::SongID )

will tell you how many songs a musician has "touched".

Alternatively, define  a summary field in the Songs table to count the SongID field (or any field that cannot be empty) and place it on the layout of Musicians.

 

Edited February 17, 2021 by comment

[Clayton King]

Thanks for replying, Comment.

SongID doesn't exist in the Musicians table - only in the Roles table (and obviously Songbook table). So I can't count SongID in the Musicians table - only in the Roles table where the MusicianID appears one or more times.

I'm not sure I'm articulating the challenge properly, but happy to keep trying. I appreciate any help I can get.

[comment]

13 minutes ago, Clayton King said:

SongID doesn't exist in the Musicians table

I should hope not. What I suggested is to count the related field in the Songs table. Going by your screenshot, it should be =

Count ( Songbook::ID )

 

 

Edited February 17, 2021 by comment

[Clayton King]

29 minutes ago, comment said:

What I suggested is to count the related field in the Songs table. 

 

Now I'm feeling dense... I had the original count field in a one-row portal from the Roles table. You've just proved that sometimes things are much easier than we want to make them.

Thanks so very much for your patience - Some difficult concepts I grasp quite easily, and some simple concepts elude me! 

[Clayton King]

On 2/17/2021 at 7:00 AM, comment said:

What I suggested is to count the related field in the Songs table. Going by your screenshot, it should be =

Count ( Songbook::ID )

 

 

Comment, thanks for your help - it worked perfectly. Now I have a next level objective...

Here is my current relationship graph and my previous Gig layout. When this was originally created, there were just two of us performing, so I had a Singer value list created which included Clayton, Vicky and duet (both of us). The "Set List Summary & Counts" section of the layout (circled) was done with summary counts and the headings (Set, Songs, Duration, Clayton, Vicky, Duet) are just text on the layout. Each row in the Set List Summary & Counts section is a one-row portal to the same layout (Gigs~Songs_Join) filtered for the Set number. The assumption is that a max of 4 sets would be performed, thus four rows. The bottom row in the section shows how many sets, songs, duration, and counts of songs by singer.

Essentially I have several related tables that grab info into a join (Gigs~Songs_Join) table. Tables include:

 Gigs: Date/time, venue, producer, etc. I

Songbook: song title, duration, keys, genre, lyrics, etc.

Musicians: used in Songbook to include original artist, lyricist, composer, etc.

Venues: address, contact for venue, etc., used in Gigs above

WhoSang: List of singers

The attached snapshot is of the existing layout. 

We now have additional singers, so I'd like the Set List Summary & Counts section to be more dynamic to account for multiple people. Recognizing a practical limit of how big this section can be, and assuming there never to be more than four singers, I'd like to heading in the section to be dynamically created and provide the same info as currently displayed. Rather than use a Singer value list as before, the WhoSang table includes the various combinations of people.

Before, the Gigs~Songs_JOIN table had summary count fields for each of the three singers. Those are now gone since the singer is not actually a field in the JOIN table, but rather a field that looks up the SingerID in the WhoSang table. Ideally, I want is some kind of dynamic field(s) to populate the header row. In the case of duets (i.e., Clayton & Vicky), rather than show that as a duet for the purpose of the summary section, I'd like to increment Clayton and Vicky's counts. So if Clayton sings 3 songs, Vicky sings 4 songs, and they sing 3 duets together, Clayton's count would be 6 and Vicky's count would be 7. Also, if Lanny and Ella perform in a gig, they would (dynamically) appear in the section, with solo and duet counts handled the same way.

I hope this is clear enough to provide some background, and appreciate any help given! I'm stuck at  how to approach it.

 

 

[comment]

You did the right thing when you posted this as a separate question.

 

[Samuel Toombs]

Try this:

ValueCount ( UniqueValues ( Role|Composer::SongID ) )

Or some variation thereof.