I'm back trying to essentially cross-tab some data.
I have a solution with multiple related tables.
A songbook table which includes a song title, duration, etc.
A gig table which includes information like performance date, venue, etc.
A Singers table which contains performers names and info.
A Performance table (join table) contains the following fields:
SongbookID (the song)
Set (in which the song is performed)
Slot (the place in a set where the song is performed)
SingerID (to identify the singer)
GigID (which Gig is being performed)
I have a SetSummary table which is related to the Performance table for the purpose of trying to summarize data in the Performance table. For example, there's a Set1Count field which looks at the GigID in Performance, then any songs with Set=1 and counts those records. This works fine!
The challenge comes when trying to calculate the length of the set. There is a duration field in the Songbook table which I'd like to sum based on Set number. I've tried both case and nested ifs (see below). The result in the field is for the entire gig - not just set 1.
If ( gigs_PERFORMANCES::GigID=GigID;
If ( gigs_PERFORMANCES::Set=1;
sum ( gigs_performances_SONGBOOK::Duration)
))
I can share the whole database and relationships, but thought I'd through this out there.
Many of you have helped in the past with this Gigs solution.
A field of type ‘Summary’ in the ‘ gigs_performances_SONGBOOK’ table, that summarises the content of the ‘Duration’ field can do the job.
It provides the total duration of all songs in a portal that shows related records in the songbook table. All you have to do to finalise is placing a box on the layout that displays the summary field
I have a slight suspicion, based on the excerpt quoted above, that you may be performing your conditional calculation logic from the context/perspective of some parent-type of record where you would like to display your summed/aggregate value. If this is true, you may have a little learning to do with respect to how the SUM function works (@Torsten has given you a good hint about this).
I am not even 90% certain, though, that I have understood your approach well enough, so please take the above thought with a grain of salt.
Agree with @steve_ssh on this. If your are in GIGS and you ask for the sum( child::field ), you get the sum of all related records.
Your logic is controlling the conditions in which the calc is performed, it's not controlling the data that the calc is performed on. You are saying:
- Is the the correct gig?
- If so, is this the first Set?
- Great. Now get the sum of all SONGBOOK::Duration related to this GIG
You have a few options. You can:
(a) use executeSQL with the right foo-bar mojo, or
(b) create a relationship which is sensitive to Gig and Set and display a summary field (total of Duration) from Songbook , or
(c) use portal rship based on Gig, filters based on Set, and display a summary field (total of Duration) from Songbook within the portal.
(d) use a script which finds the correct set of records and generates a sum total.
Pretty sure I wound up choosing (c) portal relationship based on gig and singer, the filtered for set 1 and displayed a summary field from performances. However, the results seem to be inconsistent and/or just wrong.
