8 Replies Latest reply on Apr 5, 2016 3:31 AM by Abingdon

    Export records to Excel with singe ‘totals’ row

    Abingdon

      I am wondering if there is an elegant way to export records to an Excel file but have the total(s) for each field (column) show on the final row of the exported file.  i.e. by not exporting a summary field which always appears as an extra column.

       

      Reason being, I have a database that regularly exports thousands of records with the user then having to put the Excel Autosum calc in manually at the foot of each column. Mistakes can be made doing this and I would rather have Filemaker do it rather than a user.

      I have managed to do this with a script (see sample file), but it does seem one hell of a hack, even to me!

      Just wondering if anyone has a better solution?

       

      Many thanks as always.

       

        • 1. Re: Export records to Excel with singe ‘totals’ row
          beverly

          Why is this not the 'better' solution? It works very well! I might make a sort after the 'total' record is added and make the column (Branch Name) value "_Totals_" or something that would alpha-sort and put at the top (but only if you need it in the first row). Then export.

           

          I can do something with XML/XSLT to create the Excel with formulas (which would show the totals as formulas, not just values). But that would only be worth the effort, IF the end-user needed to "play with the values" and get new totals as the values changed. The same could work for the column "NumberTotal" (make as a formula).

           

          Go with what you've got!!

          beverly

          • 2. Re: Export records to Excel with singe ‘totals’ row
            Abingdon

            Hi Beverly,

            Well, yes it works for what I need it to do so I'll upgrade it from 'hack' to 'solution' :-)

             

            I just thought that adding final totals to an Excel export was such a common need that somewhere out there in the FileMaker Multiverse a better one might exist.

            My only main concern with what I've done is creating then deleting a 'utility' record.  However, as long as it's the script that has the privileges for this rather than the user, then I guess I should stop being so purist about it.

             

            Many thanks; I always find your comments on posts valuable reading.

            • 3. Re: Export records to Excel with singe ‘totals’ row
              beverly

              I'm watching you now and looking forward to more good contributions from you.

              Thank you for sharing!

              beverly

              • 4. Re: Export records to Excel with singe ‘totals’ row
                beverly

                p.s put this in as a suggestion (idea) to export as EXCEL and include totals if desired.

                • 5. Re: Export records to Excel with singe ‘totals’ row
                  Abingdon

                  Hey, no pressure then...:-)

                   

                  Didn't think of adding it as a suggestion.  Maybe FM will name it after me if adopted, 'The Abingdon Function', now how classy is that!  Could also be a great rock 60s retro band name.  Anyway...

                   

                  Many thanks.

                  • 7. Re: Export records to Excel with singe ‘totals’ row
                    Doug Staubach

                    Hi Abingdon:

                     

                    It looks like the solution you found was quick and easy, but I might have a different way to solve the problem.

                     

                    I've just released my own (generic) XSLT template that includes the ability to create a column that contains a real Excel formula (so if you change one the values in your source columns, the total would be updated correctly). I'll be the first to admit that editing XSLT files can appear daunting at first, but the results are pretty awesome.

                     

                    Feel free to take a look at my solution and use the parts that are helpful to you.

                    The XSLT file can be found in my announcement. I'm calling it FMP2XLS.

                     

                    And let me know if this helps?

                     

                    Thanks,

                    Doug Staubach

                    • 8. Re: Export records to Excel with singe ‘totals’ row
                      Abingdon

                      Hi Doug,

                       

                      Wow.  There is a huge amount of work gone into this!  I have downloaded your demo file and given it a (brief) run.

                       

                      I will admit, I am an absolute beginner when it comes to XSLT so it may mean me doing some much needed homework.

                       

                      However, in case it is useful, I have attached two screen shots of 'errors' I get when running the Excel export scripts.

                       

                      Both of these scripts initially give me the following message from Excel.DateFromLayout.PNG

                       

                      However, when I continue with opening the Date/Time from layout sheet it opens fine.

                       

                      The Date/Time from System does not open and gives a further error as below.

                      DateFromSystem.PNG

                      Not sure if you also want the log file if it would be useful?

                       

                      Anyway, this is an amazing piece of work.  Congratulations!