6 Replies Latest reply on May 15, 2014 6:24 PM by rfs0123

    Send 'found/sorted' summarized data to Excel

    rfs0123

      Our large mechanical construction company has used Lotus' Approach database software & a program called R&R Report writer for years to 'takeoff' various pieces of equipment from construction drawings, then summarize the data using several possible sort patterns, depending on the situation, to our main Excel Bid sheet to generate our final bid price. The software we used are no longer supported, especially in Windows 7/8, which we have just been forced to install. I have been searching for updated alternatives for a few years, knowing that we'd need to change eventually. We tried Alpha 5 and some of the other non-SQL type applications without success. Alpha 5 worked, but it used the older dbf data format and an awkward interface with Excel. Also tried a few others without success. I recently downloaded the trial version of Filemaker 13 to see whether it would work. I have successfully replicated (and improved) our original database, along with the calculations required. I have also created a working summary report, that consolidates the similar equipment items. The problem is that I haven't been able to send this report successfully to Excel. Instead of the summary fields indicated the summarized values giving me the 'break' sub total value, based on the sort field(s) as they do on screen, they indicate the grand total value for all of the line items.

       

       

      I have also tried printing the report to a text based file (assuming that I could import the data into Excel), but this didn't work either, since the result was not a delimited CSV file, butr a disorganized text file. Not a good solution anyway, since we often have to use single and double quotes to indicate inches and feet for measurements and sizes and this messes with the fields going to the right column.

       

       

      Any idea of a solution or where I can find some help? I've done numerous on-line searches, but have not found anything similar to this problem. I hate to give up on FM, since it seems that it would be easy all around solution, but without the ability to send this summary information to Excel, its use would be limited.

       

      Thank you,

       

      Bob

        • 1. Re: Send 'found/sorted' summarized data to Excel
          erolst

          When exporting a sorted found set, you can select the 'Group by‘ option and choose one or more break fields; when you add summary fields to the export order, you will get an 'artificial' counterpart for each summary field that hold the values of a sub-summary for that field by the selected break field(s).

           

          Well, its easier for you to just try it out, than for me to explain it. In short, this is definitely something you can do “out of the box”.

           

           

          rfs0123 wrote:

          Any idea of a solution or where I can find some help?  I've done numerous on-line searches, but have not found anything similar to this problem.

           

           

          I think this feature is described in the built-in help system.

           

          btw, for more esoteric/exotic data grouping wishes, there is always the approach to use a utility table, fill it with pre-summarized or otherwise massaged data and export from there. (Plus probably things like XML export using XLST stylesheets, which I can tell you zilch about …)

           

          And Welcome to The Wonderful World of FileMaker …

          • 2. Re: Send 'found/sorted' summarized data to Excel
            rfs0123

            Thank you, for your quick response.  I had actually done an export in that manner when I got the grandtotals for the entire bid in each field rather than those just for the field sort break.  I think I need to play around a bit more.  I'm used to dedicated report writers, Like R&R Report writer or Crystal Reports, so it might take a bit more work on my part to get what I need from Filemaker.  I'm reasonably certain that there has to be a way and that it's some small detail I'm missing.  I wrote a script that came close, but again some more work is needed.

             

            Thank you again, I appreciate your help.

            • 3. Re: Send 'found/sorted' summarized data to Excel
              wsvp

              A good option here, may be the use of the ExecuteSQL function combined with a virtual list techique.  You have control of the delimiters, and you can group the data as you wish.  I cannot give you a good example (as I am just beginning to learn SQL myself.)

              • 4. Re: Send 'found/sorted' summarized data to Excel
                rfs0123

                Thank you for your response.  Worth looking into.  I'm a little surprised though that this is so hard to accomplish easily.  Consolidating data and then exporting should be a fairly simple thing to do, based on a combo of sort fields.  Since I'm new to FM, I know that there's a lot more to learn.  It's reassuring to see such an active forum willing to help the new guys on the street.

                 

                Bob

                • 5. Re: Send 'found/sorted' summarized data to Excel
                  Stephen Huston

                  Another thought which might help if you are simply using Excel as a convenient transfer medium rather than for additional data manipulation. You can design a FileMaker layout to look exactly like the Excel result you want with subsummaries, etc., and then export the file as a PDF. It will look like Excel even though it is not editable as Excel.

                  • 6. Re: Send 'found/sorted' summarized data to Excel
                    rfs0123

                    The original data starts out in an on-screen takeoff program, which exports the data in an Excel sheet.  I then import this data into a database where I store, add or modify the records.  I then summarize and export them into a very large worksheet (40+ worksheets) where we add in sub contrsctors bids, quotes on the equipment brought in from the database and other material & labor items to come up with our final bid.  The nice thing about keeping the data in the database is that if the project is rebid we can copy the old records, make the necessary changes and send theis data back to the main Excel worksheet.  It sounds a bit convoluted but actually works quite well for projects valued over $25 million.  If we get the job, we can  then generate a report to give to our project manager with the number of items per floor, with their cost & labor.

                     

                    Bob