8 Replies Latest reply on Nov 3, 2012 9:50 AM by freeride

    Time-based report with summary lists from all related items in related tables

    freeride

      Title

      Time-based report with summary lists from all related items in related tables

      Post

            

           I would like to know how to produce a summary report in my database of school bookings based on Terms (Spring, Summer and Autumn) for any given year.
            
           Each booking in the found set will have multiple presenters drawn from a presenter table, and is for a specific school drawn from a schools table
            
           As well as summary data on number of students, no of bookings etc (easyily achieved) I need a list of all active presenters for that term, and all schools presented in. I only need the schools and presenters listed once, even though often a school is visited more than once, and a presenter often does multiple presentations.
            
           When I try to achieve this using portals, or using value lists calculated from related items, I only get one school and a list of the presenters from one booking.
            
           I am sure there must be an easy way to do this, as it must be a common problem. Any suggestions would be greatly appreciated.
            

        • 1. Re: Time-based report with summary lists from all related items in related tables
          philmodjunk

               You'll need to tell us a lot more about your database before we can provide a suggested solution. How your tables are linked to each other in relationships will be crucial to devising the best solution.

               Also, please let us know what version of FileMaker you are using. FileMaker 12 offers some new options that use ExecuteSQL to pull data from other tables without needing relationships to do so and in formats not easily produced in earlier versions of this software.

          • 2. Re: Time-based report with summary lists from all related items in related tables
            freeride

                 I am using FileMaker 11 (but considering upgrading, especially if you suggest it would make this easier).

                 It is a pretty simple database. The Bookings table is the parent, kp_ bookingID joined = to kf_bookingID in 2 link tables for presenters and schools,

                 kf_presenterID and kf_schoolID = kp_presenterID and kp_schoolID in resepctive child tables.

                 The link tables are populated via portals in a layout based on Bookings table. So for each booking, I add a school and a selection of presenters.

                  

            • 3. Re: Time-based report with summary lists from all related items in related tables
              philmodjunk

                   I interpret that to mean that you have these relationships:

                   Schools------<Booking_School>------Bookings-----<Booking_Presenter>-------Presenters

                   Schools::kp_SchoolID = Booking_School::kf_SchoolID
                   Bookings::kp_BookingID = Booking_School::kf_BookingID
                   Presenter::kp_PresenterID = Booking_Presenter::kf_PresenterID
                   Bookings::kp_BookingID = Booking_Presenter::kf_BookingID

                   While the join (link) table names may be different, does this structure match what you have here?

                   

                        I only need the schools and presenters listed once, even though often a school is visited more than once, and a presenter often does multiple presentations.

                   If you try to create an example of your report with paper and pencil, you'll find that this is not possible as you have many presenters presenting at many schools. Your report can either list each presenter once or each school once--not both.

                   I think that you want a report that looks like this:

                   Bookings For Jefferson High School
                      Autumn Term
                           Joe Smith  (include any summary data for this booking here)
                           John Jones
                           Mary Applegate
                     Spring Term
                           Joe Smith

                   Is that correct?

                   And for any given record in "booking" can their be more than one presenter booked? And for that one booking, can they be booked at more than one school?

                   I ask that because it looks to me like you can simplify your relationship structure to be:

                   Schools------<Bookings>------Presenters

                   and if that can be made to work, your report becomes much simpler to set up.

              • 4. Re: Time-based report with summary lists from all related items in related tables
                freeride

                     You have interpreted the relationships exactly right.

                     Any booking will be with one school, but there is a team of around 4 presenters for each booking, normally with a different team for each booking. In answer to your question, presenters cannot be booked for more than one school for that one booking. A booking is specific to the one school.

                     The problem I have is that the layout and content of the term report has been determined externally by the parent charity, so is not flexible.

                     The report I am required to produce is more like this:

                Autumn term

                       
                •           No. Schools visited this term
                •      
                •           List of names of schools visited (named once even if visited more than once)
                •      
                •           Presenters active this term (list of names)
                •      
                •           Plus other summary stats (from the booking table, I know how to do this part)

                      

                     If I try a report with subsummary layout sorted by term, the summary stats are correct, but it only pulls out the first school name and the team who visited on that first booking. Same if I put a portal on this report, or if I use a calculated value list.

                      

                • 5. Re: Time-based report with summary lists from all related items in related tables
                  philmodjunk

                       To start, you can dispense with the "link" table between schools and bookings. If a given booking record never links to more than one school, there is no need for that link table as you have a one to many relationship from school to bookings instead of a many to many relationship.You do need the link on the other side of Bookings to facilitate the many to many relationship with presenters.

                       This report would indeed be much simpler in FileMaker 12 using ExecuteSQL to pull up a list of schools and a list of presenters using the DISTINCT key word to drop out duplicates. But FileMaker 12 represents a significant change in many areas and even with three "bug fix" updates, there still are a number of issues with it. You might download the 30 day free trial and see how your Database looks and functions after conversion to the new file format before deciding whether or not to buy.

                       The parts of your report are more easily produced as separate items on different layouts.

                       The first two can be achieved by performing a find on the schools table specifying a date range or term in a field in the related bookings table. This finds all schools record with at least one related bookings record that meets the time interval criterion. That lists the schools and the number of records found is the number of schools.

                       After finding all bookings records, you can get a list of active presnters by using Go To Related Records with the Match Found set option to pull up a list of Presenters on a Presenters layout.

                       So one option here is to use a script to do exactly that, but then copy this data into Large, sliding text fields on a report layout based on your Bookings table.

                       Copy All Records can be used to copy a return separated list of school names from a layout based on Schools if that layout only has the school name field on it. This can then be pasted into a text field and ValueCount ( SchoolListField ) would then tell you the number of schools in that list. The same trick can be used to get a list of presenters.

                       Note: depending on what data from Bookings is needed, you may be able to not do this for Schools and just do it for Presenters. If you define a list view layout based on Bookings where you remove the body layout part and replace it with a sub summary part "when sorted by Schools::__pkSchoolID", you can get a list of all schools booked in the specified time period and each school will be listed only once. This does, however require using the "How to count the number of unique occurences in field." method for getting a count of the number of schools in that list, however.

                        

                  • 6. Re: Time-based report with summary lists from all related items in related tables
                    freeride

                         Thanks that's very helpful. 

                         Could you possibly also give me an idea of the script "using ExecuteSQL to pull up a list of schools and a list of presenters using the DISTINCT key word to drop out duplicates"?

                          

                          

                          

                    • 7. Re: Time-based report with summary lists from all related items in related tables
                      philmodjunk

                           It's not a script, it's a calcualtion field. The results from your query would be displayed in a single, large text field set to slide up/resize enclosing part.

                           See this thread:

                      A new way to count unique values in FileMaker 12

                      • 8. Re: Time-based report with summary lists from all related items in related tables
                        freeride

                             Thanks, that's so much easier than in FM11,

                             I haven't come across any bugs in my database so far, so looks like it makes sense to upgrade

                             Really appreciate your time