2 Replies Latest reply on Mar 28, 2013 5:22 AM by matyson

    How to export filtered records to Excel using scripts

    matyson

      I have a database that once a month a specific nurse will need to export the records she has created within a specific date range and save in shared folder on a server.

       

      I would like her to choose her name from a dropdown list, enter the date range to search, and then choose the out-going file name from a dropdown list. I envision her clicking a button to run the script with the above criteria to go to the layout.

       

      I need her to be able to view the files in table view and then after review, able to click another button to perform the export to the specified file name. I do not care if the chose for the file name is on the first layout or the results layout.

       

      Is this possible? I am fairly new in relational databases and any help would be useful. Thanks so much, Mary.

        • 1. Re: How to export filtered records to Excel using scripts
          taylorsharpe

          This is a something that you would handle with script(s) and layout(s).  Obviously you will need a button on a layout that she will click to start a script running.  The script has to prompt her and ask her the questions you stated above (date range, file name, etc.).  There are two ways to do this.  The first is to use the Script Step called "Show Custom Dialog" which has all the wonderful look and functionality of FileMaker version 3.  It has a limit of 3 buttons and 3 input fields and it is black and white and you can't change the look or anything.  But it is has many of us have been prompting for years.  Alternatively you can make a layout that looks pretty and has some global fields in it to input the information she needs to input.  One advantage of using a layout is that you can have a pop-up window with a list of file names or the date fields can have the calendar popup.  That is much nicer than what you can do with a dialog box.  It sounds like you'll need 3 global fields, 2 date input fields and a file name field.  And you'll need a value list for the file name field.  I recommend storing these global fields as variables in the script.  You can then continue the script (or start a new one with a button on the input layout) that goes to the report layout, enters find mode, searches for the date range.  Then you can use the script step "Save Records as Excel" and have it use the name she provided that points to the correct file location to store the excel document.  One of the nice things about this script step is in the window where you specify the path of the file name, there is check box for "Automatically open file" which is useful for people who can't seem to figure out where the file was saved.  In the Output File Path, remember to use a variable.... something like $Path or $FileName.  And note that file paths are different on Macs and Windows in case you have a mixed environment. 

          • 2. Re: How to export filtered records to Excel using scripts
            matyson

            Thank you so much for replying.  This is very helpful and has pointed me to the right path.  Mary

             

            Mary Tyson, BSN, RN, NCSN

            School Nurse

            Macon County Public Health

            1830 Lakeside Drive

            Franklin, NC 28734

            (828)421-3506

            http://www.maconnc.org

             

            Pursuant to North Carolina General Statues Chapter 132, Public Records, this electronic mail message and any attachments hereto, as well as any electronic mail messages that may be sent in response to it may be considered public record.  Also, any information contained in this message that may be considered “Confidential” will be withheld from any public record requests.  If this e-mail contains protected health information or personal identifying information, you are hereby notified that any further dissemination and/or distribution of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by replying to this message and deleting it from your computer.  Thank you.