7 Replies Latest reply on Apr 7, 2009 2:01 PM by TSGal

    Exporting Summary field to excel problem



      Exporting Summary field to excel problem


      What I am trying to achieve is as follows:-


      A dataset showing the number of records added each day.


      I want to then export this to excel to create a line graph to display the trend over a period of time. The export to excel part is where it ceases to work!


      I have created a layout with the Date when the record was added.

      I do a date range search and then filemaker sorts the data by date and then a recordCount summary field


      The recordCount field is a summary field which is a Count Of the number of Date_Record_Added.


      The layout displays the summarised data and the summarised record count perfectly as follows:-


      Date | No.Added

      01/01/2008 | 275

      02/01/2008 |167

      03/01/2008 | 345

      04/01/2008 | 327

      etc etc



      The problem I get is when I try and export this to excel. Filemaker will export each individual date in one Column BUT only the entire count of all of the record in the date range in column two Cell2 -  B2



      How can I get filemaker to export each summarised record count with each date?





        • 1. Re: Exporting Summary field to excel problem



          Thank you for your post.


          There is no direct way to export the sub-summarized values.  I've had to resort to a script, taking the values and putting them into another table before exporting.


          For this example, assume all of my information is in a table "DETAILS".  I create another Table "SUMMARY" with the following fields:


          GroupField (Text)

          Summary (Number)



          My script reads something like:


          Enter Browse Mode []

          Go to Layout ["SUMMARY"]


          If [Get (FoundCount ) > 0]

             Delete All Records [No dialog]

          End If


          Go to Layout ["DETAILS"]

          Sort Records [Restore] 

          Go to Record/Request/Page [First]

          Set Variable [$group: Value: "zzzzz" ]



             If [$group ≠ DETAILS::GroupField]

                Set Variable [$group; Value: DETAILS::GroupField]

                Go to Layout ["SUMMARY"]

                If [Get (FoundCount ) > 0]

                   Set Field [SUMMARY::Summary; $value]

                End If

                New Record/Request

                Set Field [SUMMARY::GroupField; $group]

                Set Variable [$value; Value:0]

                Go to Layout ["DETAILS" ]

             End If

             Set Variable [$value: Value: $value + DETAILS: NumberField]

             Go to Record/Request/Page [Next; Exit after last]

          End Loop

          Go to Layout ["SUMMARY"]

          Set Field [SUMMARY::Summary; $value]


          Export Records...




          In short, this removes all records from the summary table.  We then go through each record in the DETAILS table.  When the GroupField changes, we put the total into the summary field, add a new record for the new GroupField, reset the accumulation variable to zero and continue on.  At the end of the file, we update the Summary value.  You can then export.


          There are several ways that this can be accomplished, and I'm sure others will pipe in with their own take on it.


          If you need clarification for any of the above steps, please let me know.



          FileMaker, Inc. 

          • 2. Re: Exporting Summary field to excel problem

            I thought I would have to resort to using a new table, it will just take a lot more time to setup!


            Perhaps a new recommended feature for filemaker in the future!



            Thanks for your kind help.

            • 3. Re: Exporting Summary field to excel problem



              It may take more time to setup, but once it is setup, you can run it ad nauseum.  :-)


              Seriously, other users also want this feature.  In order to make sure your suggestion is heard, please go to:




              This is our "feature request" page, and normally I would copy and paste your post, but there are some questions asked that only you can answer.  This form is automatically sent to our Product Marketing and Development departments.



              FileMaker, Inc. 

              • 4. Re: Exporting Summary field to excel problem
                   This feature already exists. You just need to add a calculation field (result is Number) =

                GetSummary ( recordCount ; Date_Record_Added )

                Find the records you want to export, sort them by Date_Record_Added, and export them grouped by Date_Record_Added. Export the new calculation field instead of the recordCount summary field.

                • 5. Re: Exporting Summary field to excel problem



                  Thank you for your post.  By far, this is a better solution.  Thank you!



                  FileMaker, Inc. 

                  • 6. Re: Exporting Summary field to excel problem

                    I Have encountered a similar problem when attempting to export to Excel from preview mode by clicking the export to Excel icon.


                    I tried the suggested option to Export records and then save as -- which worked -- but that requires extra steps and some extra manipulation in Excel.


                    The idea is to be able to get a clean report of those records flawlessly, straight out of Preview mode, as seems to be the case with PDF  exports.


                    Is this a bug in the program?


                    Suggestions much appreciated. Thanks. 

                    • 7. Re: Exporting Summary field to excel problem

                      Michael J:


                      Thank you for your post.


                      Export gives you the option to use the formatting of the field.  Also, you are able to sub-summarize data.


                      With the "Save as Excel" icon, it is saving the RECORDS in an Excel format.  If you don't have a Body set up in your layout, then no records are saved in the Excel file.  There is no option to use the field formatting using this option.  This is as designed.



                      FileMaker, Inc.