10 Replies Latest reply on Dec 29, 2016 11:11 AM by SethRedlus

    Export Columnar Data to Excel

    SethRedlus

      Hopefully I can make sense of this request. I have a database with essentially 3 tables:

      1) People

      2) Questions

      3) Answers

       

      The people table stores information about people who use the system.

      The Questions table is a list of questions to be asked of these people

      The Answers table is columnar data responses to the questions.

       

      For Example:

      The Questions Table contains:

      1. What is your favorite color

      2. What is your favorite animal.

      3. What is your favorite city.

       

      The Answers table contains:

      PeopleIDQuestionID
      Answer
      11Blue
      12Dog
      13New York
      21Red
      22Dog
      23Minneapolis

       

      What I'm looking to do is to export the data from the database (either CSV or Excel) so that my rows are the people, but I want to columns to be the answers to their questions, i.e.

      RecordID
      Question ID 1
      Question ID 2
      Question ID 3
      1BlueDogNew York
      2RedDogMinneapolis

       

      My mind is a little fried lately, but I'm not sure how best to accomplish this. Since the answers table is columnar, it's possible to have a hole - like if someone doesn't answer Question 2, then the value would be blank. Anyone know a good way to output the data like this?

        • 1. Re: Export Columnar Data to Excel
          philmodjunk

          You are going to need to transform rows into columns. You can move the data from your current tables into an intermediate table configured for export purposes or you can use relationships and calculation fields (or they can use ExecuteSQL) to move your answer data into columns so that you have the needed structure for export.

          • 2. Re: Export Columnar Data to Excel
            beverly

            There are probably as many ways to do this as there are FM developers. LOL!

            You are wanting Pivot Table or Cross-tab (cross tabulation) reporting, which is not native in FileMaker. However you can search for 'filemaker pivot table' or 'filemaker crosstab report' (on this forum and any search engine) for some of the ways others have done this.

             

            beverly

            • 3. Re: Export Columnar Data to Excel
              philmodjunk

              There are also some third party tools for exporting to excel. You might investigate those to see if any will transform rows for columns as part of the process.

              1 of 1 people found this helpful
              • 4. Re: Export Columnar Data to Excel
                SethRedlus

                Thanks for the input - just in case someone finds this in the future, figured it best to explain what I've decided to do.

                 

                I am a Mac guy, but will be working in a mixed environment on this project. I have a handful of users that want the data exported to excel and refuse to touch FileMaker, so that put me in a bit of a bind on how to deliver what they wanted.

                 

                I had considered developing an export with XML and using XSLT to transform the data into what I wanted in Excel, however Excel's mac versions don't support XML while the PC version have complete support for XML (Ugh Microsoft!).

                 

                After much research, I found plugins from both MonkeyBread and 360Works, and I'm going to use one of their solutions since it seems like a much less painful route by keeping all of the work within FileMaker rather then to have to build something else from scratch. Thanks again for everyone's input.

                • 5. Re: Export Columnar Data to Excel
                  beverly

                  I have to disagree with XML doesn't work with MacOS Excel. I use it all the time. You can't create .xlsx, but .xls should read just fine.

                   

                  Sent from miPhone

                  • 6. Re: Export Columnar Data to Excel
                    estar

                    In manage database create these fields.

                    Screenshot 2016-12-29 13.41.43.png

                    Then create two scripts as follows

                    #1

                    Screenshot 2016-12-29 13.50.39.png

                     

                    #2

                    Screenshot 2016-12-29 13.41.05.png

                    #3 and a third script to find only the ones you want for export to xls

                     

                    (make RecordID a button performing third script)

                     

                     

                    Screenshot 2016-12-29 13.33.04.png

                    Layout one with all records:

                    Screenshot 2016-12-29 14.00.08.png

                    Layout two with RecordIDs only

                    Screenshot 2016-12-29 14.01.25.png

                    These records can be exported as cvs or xls whatever you want.

                     

                    Brgds Sacpilot

                    • 8. Re: Export Columnar Data to Excel
                      erolst

                      You really shouldn't use Copy & Paste unless it is essential to the task at hand (ie paste an image from an outside source). The combo of Set Variable[ ] and Set Field[ ] is preferred for a number of reasons.

                       

                      You also don't need to create fields to hold the found count and calculate 1/3 of it, respectively, when you can calculate the result in the script, store it in a variable and use that to control the loop.

                       

                      Finally, you could replace your lines 6-15 with

                       

                      Set Field [ Questions::Q_ID2 ; GetNthRecord ( Questions::answer ; 2 ) ]

                      Set Field [ Questions::Q_ID3 ; GetNthRecord ( Questions::answer ; 3 ) ]

                       

                      which in this case is even better than SetV/SetF because you have a known data structure (or a naïve approach ...)

                      • 9. Re: Export Columnar Data to Excel
                        beverly

                        or for this even task

                        Set Variable []

                        would be preferable to copy/paste!

                        • 10. Re: Export Columnar Data to Excel
                          SethRedlus

                          I should have been more specific. In the PC version of Excel, there is a special toolbar specifically for mapping XML files and managing them within Excel. The toolbar and functionality is missing in Excel for the mac - though you can still open the files there.