7 Replies Latest reply on Jun 9, 2014 5:15 PM by rmittelman

    Need Hints for Exporting Data to Excel

    rmittelman

      I'm not sure if this is a question for General forum or Sharing Data forum, but here goes:

       

      My membership database needs a functionality to export records to Excel. The database used to be in MS Access, and I had VBA methods to query the proper data, then using Excel automation, create the workbook and simply write the data records into it, then save the workbook.

       

      Now that I'm using FMP, this is not going to happen. So I need a couple of pointers.

       

      Here is the basic architecture:

       

      Members table:

      Mem ID
      LastFirstLast 2First 2Address
      27SmithJohn
      Julie

       

      Communication Table

      Mem IDComm IDTypeDescPhoneEmailEmail Reports?
      27331John Cell8055551212

      27342John
      John@me.com1
      27352Julie
      Julie@me.com

       

      So here are the questions:

       

      1: Do I need a "report" type layout, or a "list" type layout, or doesn't that matter?

      2: I guess I base the layout on the communications table, then include fields from the linked Members table, right?

      3: Once I have the layout designed and sorted / filtered, I'm guessing I can simply script the export, right?

       

      Thanks for the help.

        • 1. Re: Need Hints for Exporting Data to Excel
          jlamprecht

          Check out the Save Records As Excel script step. Found here:

           

          http://www.filemaker.com/help/11/fmp/html/scripts_ref1.36.70.html

           

          This should take care of your needs.

          • 2. Re: Need Hints for Exporting Data to Excel
            erolst

            rmittelman wrote:

             

            So here are the questions:

             

            1:  Do I need a "report" type layout, or a "list" type layout, or doesn't that matter?

            2:  I guess I base the layout on the communications table, then include fields from the linked Members table, right?

            3:  Once I have the layout designed and sorted / filtered, I'm guessing I can simply script the export, right?

             

            1. In most cases, there's no difference … Anyway, it doesn't matter since you can explicitly specify to export the current record or the found set, regardless of the view you employ (form, list or table).

             

            2. If you want to create one row per Comm record – yes.

             

            3. Yes. I recommend you take some some long, hard looks at the available script steps* to get a feeling for what is scriptable in FileMaker (short answer: just about everything).

             

            Not to forget the capability of executing shell scripts, and AppleScripts in OS X, which basically opens up much of the OS, and, at least on the Mac, many other applications. Not sure if you could maybe even execute VBA scripts to postprocess your Excel files. (Otherwise, get a Mac … )

             

            * For example here: http://fmhelp.filemaker.com/fmphelp_13/en/html/help_script_cat.34.1.html#947319

            1 of 1 people found this helpful
            • 3. Re: Need Hints for Exporting Data to Excel
              keywords

              Since you are apparently changing your work process you probably should also be asking whether you still need the export to Excel process, or whether you can achieve the same purpose within FM itself. If the export to Excel process was used as a convenient way of producing reports, then I doubt you need to do it at all now. You would get better value for your time learning how to produce FM reports than learning how to export, in my opinion.

              • 4. Re: Need Hints for Exporting Data to Excel
                rmittelman

                Thanks, @jlamprecht. This does seem simple. I guess the challenge is to create a list-type layout that has the exact records and fields that I need, then go to it and export the records, via script steps.

                 

                Appreciate the answer.

                • 5. Re: Need Hints for Exporting Data to Excel
                  rmittelman

                  Thanks, @keywords. I am getting fairly good at making FM reports. Unfortunately, in this case I still need the Excel export. This is for a club management database, where we are all volunteer labor. I am in charge of handling all membership tasks, and we have another member who handles all of the emailing to members. Every so often, when members have been added or deleted, or email addresses change, he needs a new list of members and addresses, both physical and email. He imports the Excel I give him into his Outlook.  Therefore, things need to stay the same in that regard.

                   

                  Thanks for the reply, however.

                  • 6. Re: Need Hints for Exporting Data to Excel
                    keywords

                    No problem. I said "you probably should also be asking whether you still need the export to Excel process". Clearly you do. All the best.

                    • 7. Re: Need Hints for Exporting Data to Excel
                      rmittelman

                      Thanks, @erolst. As usual, concise and to the point.

                       

                      The last paragraph bears answering: As mentioned in my other answer, I do need to export an Excel workbook of the proper records. Other club members who use that Excel don't have access to my FMP application. I have started playing with both shell scripts and AppleScripts called by my FMP app on my Mac. In Access I used VBA to query a recordset out of my Access DB, then automate Excel to produce the output workbook file. As long as I can properly prepare the right records on a new list-type layout, the export script step should work.

                       

                      I think all I need to do now is create some extra calculated fields, because the Member records have 2 sets of names, for member 1 and member 2. The related Communications table has multiple records, one for each person. There is a description field in there, containing the person's name. So I think my calculated name field needs to make a decision based on that description field (which usually contains the correct person's first name), so it knows which first name and last name fields to use from the related member table. This should be fairly simple.

                       

                      Thanks for pointing me in the correct direction.