4 Replies Latest reply on Jul 27, 2013 12:05 PM by philmodjunk

    Need Help with Report View Based on Join Table

    brianquillin

      Title

      Need Help with Report View Based on Join Table

      Post

           I need a report based on my EVENT DETAILS (join table) to display the following information in each row:

           Contact Name  |  Event Name  |  Season Contribution Amount  |  (and other things that are working)

           The following tables are at work here - CONTACTS::contact_id ---< EVENT DETAILS (join table) >---EVENTS::event_id

           I also have CONTACTS::contact_id ---< CONTRIBUTIONS::contact_id (and a series of other tables)

            

           Each event has a "season" field (ie. 2012, 2013, etc); Each contribution record also has a "season" field.  My problem (I think) is that these two "season" fields aren't related at all in my current structure.

           When I run this report, I need to see the contact's season total (I've already got that calc field) where the contribution season = the event season.

           Everytime I attempt to place another TO of CONTRIBUTIONS (either connected to EVENT DETAILS (join table) or EVENTS, I end up with either no data or valid data that references the wrong season (and it's always the first season someone ever contributed so its pulling the first record found - evidently)...  


           Sorry for the confusion in advance... Any help is greatly appreciated... Attaching a snippet of the cobweb also...  (I have removed my previous TO mistakes from the screen shot)

      Screen_Shot_2013-07-25_at_4.11.34_PM.png

        • 1. Re: Need Help with Report View Based on Join Table
          brianquillin

               CORRECTION - EVENT INVITES is the Join Table! 

          • 2. Re: Need Help with Report View Based on Join Table
            philmodjunk

                 You've put your finger on the problem. Currently, there is no data in Contributions that earmarks it for any specific event. If you had an Event ID field in contributions, then you could link up your records to Event_Invites by Event and Contributor ID fields.

            • 3. Re: Need Help with Report View Based on Join Table
              brianquillin

                   Thanks... It occured to me that I would need to probably work out a method to connect each contribution to an event.  The problem with that logic for me is that I need on the report I don't need to see each individual contribution per contact but rather their total in a given season.

                   Question about your response:

                   Did you mean that the relationship between Event_Invites should be connected to a TO of Contibutions (like ContributionsByEvent) using a new event_ID field and the contact_ID field?  Would this help acheive my goal?

                   Thanks...

                    

              • 4. Re: Need Help with Report View Based on Join Table
                philmodjunk

                     Once you have an EventID for each contribution, you can use a relationshiop to get the total contributions for a given invitee for a given event.

                     A total for a given contact of all their contributions for the season is possible without the eventID field--but I wonder how that total will work with your report. I suppose it would make sense though if your report lists all the events of the season for each invitee. A calculation field, cEventSeason can be added to Event_Invites that just copies this value from events.

                     Then this relationship

                     Event_Invites::contact_id = Contributions|EventSeason::Contact_id AND
                     Event_Invites::cEventSeason = Contributions|EventSeason::Season

                     will match a given record to all the contributions made by that contact for the event's season.