8 Replies Latest reply on Sep 30, 2009 12:23 PM by imgaryshap

    sub-sub-sub summaries

    imgaryshap

      Title

      sub-sub-sub summaries

      Post

      hello,

       

      i'm using FMP 8 on a mac, and i'm a novice.

       

      I work in the entertainment biz.  I have a projects database.

       

      I have figured out how to sort by month and each sub-summary heading clearly (and correctly) lists the month and number of projects submitted in that month.

       

      I would like other information to be included in that sub-summary, monthly heading.  some of the other information is part of value list field.  for example - i have a field labeled "Source" that has a pop-up selection of either "Internal" or "External."   

       

      For example - I would like the report to show this:

       

      September - 2009: 28 projects

       12 - internal

       16 - external

      --------------

      list of all September '09 records....

       

      August - 2009:  30 projects

       10 - internal

       20 - external

      --------------

      list of all August '09 records

       

       

      Right now, what I'm getting is this:

       

       September - 2009: 12 projects

       12 - internal

      --------------

      list of all September '09 internal records....

       

      September - 2009: 16 projects

      16 - external

      --------------

      list of all September '09 external records....

       

      Any thoughts?

       

      thanks so much!

       

      -G.

        • 1. Re: sub-sub-sub summaries
          philmodjunk
            

          simply create another sub summary part located beneath the first. Set it to be "when sorted by" your popup formatted field. You can place a copy of the same summary field you have in your monthly header in this header to get these sub-subtotals.

           

          Now set up your sort order so that the records are sorted first by month then by the popup field. Since the "i" in internal comes after the "e" in external, set a descending sort order instead of an ascending sort order if you want internal values listed before the external.

          • 2. Re: sub-sub-sub summaries
            imgaryshap
              

            thanks for this.  we're getting closer.  

             

            I still want this:

             

            September - 2009: 28 projects

             12 - internal

             16 - external

            --------------

            list of all September '09 records....

             

            August - 2009:  30 projects

             10 - internal

             20 - external

            --------------

            list of all August '09 records

             

             

            With your suggestion, I'm getting this:

             

             September - 2009: 28 projects

             12 - internal

            --------------

            list of all September '09 internal records....

             

            16 - external

            --------------

            list of all September '09 external records....

             

            I like your suggestion a lot, but would love to have the breakdown in the header, directly underneath the month as well.

             

            With your suggestion, my new ideal chart would look like this:

             

             

            September - 2009: 28 projects

             12 - internal

             16 - external

            --------------

             

            12 - internal

            --------------

            list of all September '09 internal records....

             

            16 - external

            --------------

            list of all September '09 external records....

             

             any thoughts?

             

             

            • 3. Re: sub-sub-sub summaries
              philmodjunk
                

              OK, this can be done with relationships and aggregate functions.

               

              Define a pair of calculation fields: cInternalKey : "Internal" and cExternalKey : "External"

               

              Define two new relationships to two new Table Occurrences.

               

              MainTable::MonthField = InternalProjects::MonthField AND

              Maintable::cInternalKey = InternalProjects:: PopupField

               

              MainTable::MonthField = ExternalProjects::MonthField AND

              Maintable::cExternalKey = ExternalProjects:: PopupField

               

              Now you can set up two calculation fields to put on your layout:

              cMonthlyInternal : Count ( InternalProjects:: popupfield )

              CMonthlyExternal : Count ( ExternalProjects:: popupfield )

              • 4. Re: sub-sub-sub summaries
                imgaryshap
                  

                this looks like the right idea, but I am a self-taught novice at FMP 8.

                 

                i need those instructions broken out for me like I'm a 6-yr old, and I totally understand if you don't have the time to do so.

                 

                i have a ton of questions, i.e.:

                -what's the calculation for the calculation field cInternalKey?  

                -which aggregate function?

                -do i need to create one brand new table or two new tables (i.e., one table for internal, one table for external)?

                -what fields go into this (these) new table(s)?

                 

                thanks so much.

                 

                -G

                • 5. Re: sub-sub-sub summaries
                  philmodjunk
                    

                  -what's the calculation for the calculation field cInternalKey?

                  For each of the two calculation fields, the expression is just text in quotes. In the case of cInternalKey, it's "Internal". The value needs to match all records with "internal" in the pop up field.

                   

                  -which aggregate function?  Count() as shown in my earlier post.

                   

                  -do i need to create one brand new table or two new tables (i.e., one table for internal, one table for external)?

                  No new tables, you need to create two new Table Occurrences in the relationship graph so you can define two new relationships.  

                   

                  Two create a new table occurrence, open Manage | Database | Relationships

                  Each box on this graph represents a Table Occurrence, not a table--filemaker often refers to tables when they should be talking about a table occurrence and this confuses matters for many. Each TO has a data source table. You can see the name of this table if you hover your mouse over the arrow in the upper left corner of the TO box or if you double-click it. When you first define a new table, filemaker automatically adds a TO box to the graph and gives it the same name as its data source table. You can add as many additional TO boxes to the graph as you need to define different relationships.

                   

                  Select a TO whose data source is the table with the pop-up field with your mouse. Click the button with the two green plus signs to make a new TO that refers to the same datasource table. Name it "InternalProjects". Drag from MainTable::Month to InternalProjects to link the first pair of fields. Drag from MainTable::cInternalKey to InternalProjects:: PopupField. You now have the first relationship I described. Repeat for the second, but giving it a different name and using cExternalKey in place of cInternalKey.

                  • 6. Re: sub-sub-sub summaries
                    imgaryshap
                      

                    i'm up to here and unclear:

                     

                    "Drag from MainTable::Month to InternalProjects to link the first pair of fields."

                     

                    Main Table refers to my main table, Month refers to the field in that table.

                     

                    What are you calling InternalProjects?  I created the new TO and labeled that Internal Projects.    Am I supposed to create another field besides cInternalKey?

                    • 7. Re: sub-sub-sub summaries
                      imgaryshap
                         nevermind. i see what you're saying.   stay tuned...
                      • 8. Re: sub-sub-sub summaries
                        imgaryshap
                          

                        awesome!

                         

                        thank you so much!