5 Replies Latest reply on Jul 1, 2016 7:47 AM by sccardais

    Sub Summary Report To Display Related Records

    sccardais

      I want to create a Class Schedule Report based on two tables: CLASSES and DATES.

       

      CLASSES and DATES are related as shown below.

       

       

      DATES has 366 records - one for each day in 2016. Fields in this table include the Date, MonthName and DayName (Monday, Tuesday, etc.) and

       

      CLASSES currently has a record for each Class, describing a Class, Start_Date, End_Date and DayName (Monday, Tuesday, etc.) that the class is being held.

       

      I want to show a Class Schedule that shows the dates for each Class sorted by Month.

       

      From a layout based on Classes with a portal into the TO described above, I can display the correct Dates but portals aren't great for reporting. For some reason, I can't display the same dates using a SubSummary report based on Classes and showing fields from the Dates TO. Instead of getting a list of Dates, I get only the first matching date.

       

      What am I doing wrong?

        • 1. Re: Sub Summary Report To Display Related Records
          Mike_Mitchell

          Base the report on Dates, not Classes. Reflect any fields you need from Classes as related fields.

          • 2. Re: Sub Summary Report To Display Related Records
            sccardais

            Mike:

             

            Thank you. I will try this but I’m confused because I can display the data I want in a portal placed on a layout based on Classes but can’t seem to do this with a sub-summary report based on Classes referencing fields in Dates table using the same TO.

             

            I’m obviously missing something basic or I didn’t explain it properly in my original post.

             

            For each Class, I want to show a list of the dates based on Start and Stop dates in Classes and based on which days of the week the Class is being held. Days of the week are selected in Classes using a checkbox field.

             

            I’m not understanding why this report should be based on Dates when it works fine from Classes via a portal to Dates.

             

            I’m not disagreeing with you. Just wanting to understand.

             

            Thanks

             

            Scott

            • 3. Re: Sub Summary Report To Display Related Records
              Mike_Mitchell

              Portals point from the parent table to the child table. They show a one-to-many relationship. The context is the parent table, which means each record is a parent (in this case, a class). They filter and display records; they manage the relationship.

               

              A summary report is based on a list view. You want to summarize the records in a found set, grouping them together. This doesn't work from the perspective of the parent. You don't want to group classes together, you want to group dates together. Therefore, you want each record to be a date, not a class. So you base the report on the dates.

              • 4. Re: Sub Summary Report To Display Related Records
                sccardais

                Thank you.

                 

                Scott

                 

                Sent from my iPad

                • 5. Re: Sub Summary Report To Display Related Records
                  sccardais

                  Mike:

                   

                  I understand. Thank you. It’s now clear that I have a Many-to-Many relationship. One class could relate to many days in DATES and one day in DATES could relate to many CLASSES so I need a JOIN table to hold ID’s from both tables.

                   

                  FYI - I created a couple of scripts to automate the process of creating Join records. One script uses a List to create a found set of Dates based on the criteria for each Class. Another script imports the record ID’s for the found set and populates the Class foreign key.

                   

                  Thanks again for your insight and help.

                   

                  S