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

    Exporting Summary field to excel problem

    jonnyt

      Title

      Exporting Summary field to excel problem

      Post

      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
          TSGal

          jonnyt:

           

          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" ]

           

          Loop

             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.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Exporting Summary field to excel problem
            jonnyt
              

            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
              TSGal

              jonnyt:

               

              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:

               

              http://www.filemaker.com/company/feature_request.html

               

              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.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Exporting Summary field to excel problem
                comment_1
                   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
                  TSGal

                  comment:

                   

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

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Exporting Summary field to excel problem
                    MichaelJ
                      

                    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
                      TSGal

                      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.

                       

                      TSGal

                      FileMaker, Inc.