1 2 Previous Next 22 Replies Latest reply on Sep 12, 2015 12:19 PM by electon

    Trying to Figure Out Summary Fields

    hbrlv

      Ok I know this is probably a pretty simple problem but here's my example.  The data is for home sales and building permits.

       

      I need to output a report with data pertaining to the Neighborhoods table.

       

      Each Neighborhood has data in child tables called Permits and Closings

       

      In Permits and Closings each record has a date field, example 8/14/2015.

       

      I need to create summary fields that total the number of Permits (or Closings) for:

       

      Each month (I have a field already defined that outputs the date as August 2015 for example)

      Last 12 months

      Year to Date

      Average per Month Past 6 months.

       

      As you can tell my experience is pretty limited so any help would be GREATLY appreciated.  I can provide better examples if needed or a spreadsheet with the fields on it.

        • 1. Re: Trying to Figure Out Summary Fields
          electon

          Sounds like you need to create sub summary fields in Permits and Closings.

          If it's the number of items per given period, create a summary field ( count of primary key ) for example.

          Is your field August 2015 a calculation stored as text ?

           

          On the report you create a sub summary part for the field you want to sort on, my guess it will be the August 2015 field if you want to sort it by month of the year.

          On same sub summary part you place your summary field(s) and when you sort the records by the field the sub summary is based on, you will get the totals for that period.

          If it's only the totals you're after then you don't need to put the body part on the layout.

           

          Bare in mind all is based on the found set. If you want to display Year to date the you need to perform a find ranging from 01/01/2015 ... current date. Also you will first need to sort by Date, then by your calc field since it's most probably a text field and will sort alphabetically.

          You will have to script somehow to get all the date ranges you're after and perform the finds.

          Another way It can be also done is via relationships to a table of globals where you specify date ranges and use Go To Related Record.

           

          What I do with tables that need frequent reporting on date ranges is create calculation fields ( numbers ) for Year, Month, Quarter, Week of Year. It becomes much more manageable if I have these.

          You can put as many sub summary parts on a layout as you want and depending on the sort state they will show accordingly.

          This way you can either show only overviews or drill-downs within the same layout just based on the sorting.

           

          As for the Avarage it'll be sum total of items divided by number of months.

          Not sure if you can achieve this by summary fields alone.

          • 2. Re: Trying to Figure Out Summary Fields
            erolst

            Here is a sample file that hopefully will make this clearer to you.

             

            Some notes:

             

            1. I suspect that Permits and Closings are rather similar Events, and that you could consolidate them into one table, with an additional type attribute. This would avoid having to duplicate certain things, or use more complicated workarounds.

             

            2. You don't need one summary field per found set, but rather per field you want to summarize; its result depends on the found set, and into which part you place it.

             

            3. The sample file allows you to search for either type, but not both; this could be changed easily by adding another sub-summary part to the layout and adapting the script slightly.

             

            4. If you don't need to see the individual records, but only the summarized results, you can remove the body part.

             

            5. Calculating the average is a bit complicated, because you need the count of date (month) groups; study the way this is done here (though there as – as usual – other methods).

             

            6. You cannot use a calculated text field like "August 2015" for grouping, because it will not sort chronologically; use a calc field that returns a date or a number and sort/group on that value; for display, use the original date field and format its layout display.

             

            7. Understand how sub-summary parts, their “break fields” and the sort order work together to create groups and summarized results.

             

            8. Note how the script uses script parameters (that are assigned to the buttons) to search for pre-defined ranges; this, too, can be easily extended.

             

            Happy coding!

            • 3. Re: Trying to Figure Out Summary Fields
              erolst

              EDIT: minor bug in that earlier sample file; here is a corrected version.

              • 4. Re: Trying to Figure Out Summary Fields
                erolst

                electon wrote:

                What I do with tables that need frequent reporting on date ranges is create calculation fields ( numbers ) for Year, Month, Quarter, Week of Year. It becomes much more manageable if I have these.

                Why not rather create different queries (scripts) that work on the original (stored) date field(s)?

                • 5. Re: Trying to Figure Out Summary Fields
                  electon

                  I guess because I haven't figured out how to do them.

                  There are reports that need to, for example show weekly or monthly sales so sorting by week or month number was easier that way. Let's say I need to see data for august of every concurrent year.

                  I allows me to create value lists for all years, months and quarters for the user to select from when preparing reports.

                   

                  How do I show week numbers on the sub summary report when those fields are not there?

                  So the sub summary parts are.

                  Year

                  Quarter

                  Month

                  Week

                   

                  As always with FileMaker there are many ways to skin a cat.

                  I'll poke around your file to see how others do it.

                  • 6. Re: Trying to Figure Out Summary Fields
                    hbrlv

                    Wow thank you so much for taking the time to do all this! However, its not quite what I'm looking for.  I think its close though.  The end result is that I need to export to Excel with something that looks like this (obviously leaving some filler data out):

                     

                    LatitudeLongitudeBuilderNeighborhoodAreaPermits 2015Permits 2014Permits 6 Mo AveragePermits Last 12 Mo
                    36.01314-115.214119American West HomesBrentwoodSW????

                     

                     

                    There would be columns for Closings as well but the calculations/scripts would be the same as for Permits.  These two actually do need to be their own separate tables and I already have thousands of records in each of them.

                     

                    So instead of listing every occurrence of a closing or permit for the neighborhood in the summary report and then giving the totals, I just need a field with the totals/averages as in the table above.

                     

                    Let me know what you think and thank you so much again for your time.

                    • 7. Re: Trying to Figure Out Summary Fields
                      hbrlv

                      Please see my reply to erolst and let me know if you have any thoughts.  Thank you so much for your time!!

                      • 8. Re: Trying to Figure Out Summary Fields
                        electon

                        It's a different thing altogether.

                        You will need at least a virtual list technique and possibly some ExecuteSQL to accomplish that.

                        It may possibly be done with XSLT when exporting to excel but it can be very challenging.

                         

                        All because you are trying to make your aggregate ( sub summary ) rows into columns.

                        I'll try and cook up an example file if time permits, in the meantime look for anything you can find on virtual list techniques.

                        • 9. Re: Trying to Figure Out Summary Fields
                          erolst

                          hbrlv wrote:

                          So instead of listing every occurrence of a closing or permit for the neighborhood in the summary report and then giving the totals, I just need a field with the totals/averages as in the table above.

                           

                          Did you try the script? It it doesn't list every record of a group, just one per group – with the group totals.

                          • 10. Re: Trying to Figure Out Summary Fields
                            hbrlv

                            Ok does that still apply even if I don't need the script to actually do the output to excel?  I can do that manually (I'm the only user of the database as well as doing the development). 

                             

                            But I get what you're saying about the aggregate I think.  I'll do some research and see if I find something that will help me.  That example would be great if you are able to throw something together.

                             

                            Just brainstorming here but what about this...

                             

                            Create the Sub Summary Fields within the specific tables and then get my found set of "Neighborhoods".  Then make a script or series of scripts that would take in the dates I need and spit out the calculations in the fields??

                             

                            Or do I just make those fields into calculation field instead of "summary" fields? Would that be easier??

                             

                            Sorry I'm still a newbie with scripting and anything above an intermediate level with FMP.

                            • 11. Re: Trying to Figure Out Summary Fields
                              hbrlv

                              Yea in looking at the output it looks like I can maybe take parts of it and it might be able to work for me.  Like the Total and Monthly totals.  When I click "Save as Excel" it definitely isn't what I'm looking for...

                               

                              Like I said in my other reply to electon, would it be easier to just create the fields as calculations so that can always be updated automatically as more data is entered over time?  Like Year to Date for example, having a calculation that would keep that updated as new records are added?

                              • 12. Re: Trying to Figure Out Summary Fields
                                erolst

                                You didn't answer my question – do you want to export one result row per group?

                                 

                                If so, you cannot use summary fields: on the one hand, you need all group records in the found set to summarize – on the other, you must not have them when you export.

                                • 13. Re: Trying to Figure Out Summary Fields
                                  electon

                                  hbrlv wrote:

                                   

                                  Like I said in my other reply to electon, would it be easier to just create the fields as calculations so that can always be updated automatically as more data is entered over time?  Like Year to Date for example, having a calculation that would keep that updated as new records are added?

                                  I think it would be possible with un-stored calculations to get the summaries you need.

                                  Just like in the file, provided you add all the calculations you require.

                                  The trick is to output it in the format you want. When you export your sorted sub summaries you're also forced to export every single record as a row into a spreadsheet.

                                   

                                  In this case I think how that can be achieved is via another table, a virtual list table.

                                  Because of the nature of the report ( you want specific data related to the current date ) it's a moving target.

                                  So the report needs to be created on the fly.

                                   

                                  You need to figure out your primary sort order: how do you want your data to be grouped by, is it per Builder or per Neighbourhood?

                                   

                                  Your table would look like this ( without the virtual list magic fields )

                                   

                                  id_Neighbourhood     Permits Last 12 Months     Permits Year to Date     Average Last 6 Months     ETC.

                                   

                                   

                                  If you collect a distinct list of their primary keys, you can fetch the correct data into the table ( one id per row ), take care that it collects the id's for the largest range of dates from your summaries, and start pulling in the summaries into respective columns.

                                  ExecuteSQL will help greatly with this.


                                  Related data like names and other stuff you can get by linking relevant tables to the id field.

                                  This way when you export that table it will look exactly as you want it to.

                                   

                                  It's not the easiest thing in the world and it intimidated me at the beginning but a great technique to learn.

                                  Otherwise stick to what is there out of the box and present the data in that way. It might be easier.

                                   


                                  Gotta run, HTH.

                                  • 14. Re: Trying to Figure Out Summary Fields
                                    electon

                                    I've cooked up a little example file to demonstrate the technique. It uses one ExecuteSQL statement and the rest is done via relationships. Not everything is there and some things are simplified like date calculations but you will get the idea.

                                    Let us know if that's something you're after.

                                    1 2 Previous Next