Cross-tab portal (I think)

Questions
, , ,
1

I haven't posted since the name changes, so if this is the wrong spot, please let me know.

I'm back working on my performance management solution and the Gigs layout. Below is the main Gigs layout, which includes a portal to gigs_PERFORMANCES (colored rows) of songs in a performance. Each row also has a set and slot to be assigned which is summarized in the set of one-row portals at the top (circled).

image
image1094Ă—664 60.1 KB

What I have now is fairly straight forward - each summary portal is one row and filtered by Set number. In the set list portal, each row also includes the Roster of who will be performing, where there will be one or more performers.

I'd like to have the summary area look something like this (mocked up) where the same info is there, but in addition, there would be columns for each performer. It's not a problem if the song count is duplicated (i.e., two singers might increment the song count) if I could also show a column where duets (or multi-performer songs) are being performed. The goal is to provide performers with a copy of the list and show them how many songs on which they will be involved. The number of columns would be automatically created based on the singers found in the Roster field of the current gig (and portal row).

image

Any suggestions? And thanks for input.
Relevant TOs are here:

image
image891Ă—705 43.9 KB

1 Like
2

Based on my research, there are four ways to build a cross tab report: define new fields (which is the method from the “dark ages” before virtual lists), write a looping script and write the results to a new table, create a virtual list, or use Execute SQL.

3

I wonder if an entirely different approach might work. What if I have a new table (SetListSummary) which would contain GigID, SingerID, and fields for sets 1-5 (5 sets is extreme in a performance) which would contain set totals.

Create a relationship between gigs_PERFORMANCES and SetListSummary using GigID and Singer in the relationship.

SetListSummary contains calculation fields for each singer for each Gig. The calculation fields are S1, S2, S3, S4 and S5 (one for each set) and the calculation is:

ValueCount ( FilterValues ( List ( gigs_PERFORMANCES::Set ); "1" ) ) (replacing "1" with the appropriate set number.

I've tested this and it basically words. The Duration field isn't totalling correctly, but I'll keep working on that). Then the next step is to find a way to look at the Gig, determine what singers are performing, and pull the appropriate singer's record from the SetListSummary table.

1 Like