9 Replies Latest reply on Apr 25, 2013 4:52 PM by chadmccomas

    Exporting multiple files at once based on specific criteria

    chadmccomas

      Title

      Exporting multiple files at once based on specific criteria

      Post

           I bought Filemaker pro yesterday and am going to go through all the tutorials over the weekend.  In the meantime, I created a new database with about 2.8 million records.

           What I want to do is find a way to export everything into multiple files based on the contents of the area code field each record has.   So for instance, if area code 239 - all those records are saved into an Excel file and then it goes to the next area code and exports that.

            

           Is there a way to do this?  So I don't have to literally find and save each area code group of records?   It'll take probably 20 hours to do it that way.  : (

            

           Thanks!

            

        • 1. Re: Exporting multiple files at once based on specific criteria
          philmodjunk

               Yes, you need to find and save or export each group of records by area code. No, this shouldn't take 20 hours if you use a script to do this for you instead of doing it manually one group at a time.

               But you just bought FileMaker. Are you ready to try your hand at a script yet?

          • 2. Re: Exporting multiple files at once based on specific criteria
            chadmccomas

                 yeah I'm ready.  I originally wrote a similar program in PHP using MySql, but our server couldn't handle the massive data.  My work purchased me a new iMac and I *was* going to use Access but that isn't available and someone told me about Filemaker.  To be quite honest - so far it seems to be much, much more intuitive than Access is.  And more powerful.

                 So - in regards to scripts - I'm *guessing* it uses a better scripting language than VB.   (Hopefully.  I know PHP.  I hate VB)

            • 3. Re: Exporting multiple files at once based on specific criteria
              chadmccomas

                   I went into scripts, created a new one.

                   Some things I'm not sure about.

                   In PHP I could create an array for the area codes.   In this - do i need to specify each area code the script will go through?  or is there another way to do it?

                   Also - how would I tell it to save each file with a unique name?

              • 4. Re: Exporting multiple files at once based on specific criteria
                philmodjunk

                     This is a script that will cover a number of concepts. Please look these steps and functions up in help to learn more about them.

                     First to get that list of existing area codes:

                     We'll use a little trick--a value list can be defined that pulls its values from the index associated with your area code field. Hmmm, you might just have a simple phone number field and need to define a calculation field that returns the area code to set up a such a field. The format and content of your phone number field may make that simple or complex.

                     Once you have such a field, select Manage | Value Lists, create a new value list (I'll call it Area Codes) and select the Use Values from a field option. Select the Area code field as the field that will be the source of values for your value list. Since this type of value list will list one instance of each unique value in the field, we just need to get that list and loop through it to find and export our records by area code.

                     Set variable [$AreaCodes ; value: ValueListItems ( get ( FileName ) ; "Area Codes" ) ]
                     Loop
                        Set Variable [$K ; value: $K + 1 ]
                        Exit Loop If [$K > ValueCount ( $AreaCodes ) ]
                        Enter Find Mode [] ---> Clear the pause check box
                        Set Field [ YourTable::AreaCode ; Getvalue ( $AreaCodes ; $K ) ]
                        Perform Find[]
                        Set Variable [$Path ; Value: "file:" & Get ( DesktopPath ) & YourTable::AreaCode & ".xls" ]
                        Export Records [No dialog ; $Path...
                     End Loop

                     For more examples of scripted finds: Scripted Find Examples

                     For more about file paths in FileMaker: Exploring the use of a $Path Variable in Scripts

                • 5. Re: Exporting multiple files at once based on specific criteria
                  chadmccomas

                       Thanks for that.  I appreciate far more than you know.

                       I have a question though - it works fine, as in sorting all the data one step at a time.  But a dialog keeps popping up that says, "No fields were selected for export".  And there aren't any files saving.

                       Attached to this is a screenshot of the script.

                       Any idea on what I've done wrong here?

                        

                       Thanks!

                        

                  • 6. Re: Exporting multiple files at once based on specific criteria
                    philmodjunk

                         It sounds like you haven't specified all the needed options for the export records step.

                         When you click that step to select it, two buttons appear at the bottom right. I would guess that you need to click the bottom button: Specify Export Order and specify the fields that you want to export.

                         You also need to click the upper button and type in the name of your variable: $Path.

                    • 7. Re: Exporting multiple files at once based on specific criteria
                      chadmccomas

                           What do you mean type in the name of my variable $Path?  Isn't $Path the name of that variable?

                      • 8. Re: Exporting multiple files at once based on specific criteria
                        philmodjunk

                             Yes, but you need to:

                               
                        1.           Click the Export Records step to select it.
                        2.      
                        3.           Click the button next to "specify output file" and...
                        4.      
                        5.           Type $Path into the Specify Output File dialog that has now opened.

                              

                             When you have done that, $Path will appear in the brackets to the right of Export Records---which is how I can tell that you have not yet done this.

                        • 9. Re: Exporting multiple files at once based on specific criteria
                          chadmccomas

                               Ahhh.   I owe you a beer, chicken, tabasco sauce, milk or whatever floats your boat.  I think I've had my long-awaited-epiphany.

                               Let me see...