12 Replies Latest reply on Jul 15, 2011 10:56 AM by philmodjunk

    Export table with Array fields.

    VeenaHampapur

      Title

      Export table with Array fields.

      Post

      Hello All,

        I am trying to export a table which contains array fields (one whose maxRepetition > 1) and Filemaker crashes when I export it to Excel.  But I was able to export to access.  I am clueless as to the format of this data file since it contains more than one item in the field.  Anybody have any clues how I can do this?  Do I need to break up the export?

      Tried it using the Menu option and also using a script.

      Appreciate any help.

      Veena.

        • 1. Re: Export table with Array fields.
          aammondd

          You might have to script the export to "transform" the records into a more normalized format in a temporary table(s) and export them from there.

           

          There are 3 approaches to this

          1 combine the repetitions as seperate fields in your extract, You would do  this by setting up a temp table with a seperate field for each repetition and setting those fields to the coresponding repetitions. (In   this case if the repetitions are empty you will have empty fields)

          2 creating a seperate "child" table and create linked records for each repetition  that has a value. (In this case you would need to populate a field with the orginal records primary key.

          3 is a hybrid of  the two where you create new records duplicating all other values except the repeating ones. (In this case for each new repeating field value you write out the entire record.

           

           

           

          • 2. Re: Export table with Array fields.
            VeenaHampapur

            Thank you for your response aammondd.  Can you post any sample code for the various choices.  How do I create a temp table in FM?

            Veena.

            • 3. Re: Export table with Array fields.
              philmodjunk

              What's the format you need to see for this in the excel spread sheet? One repetition in each row or one repetition in each column?

              • 4. Re: Export table with Array fields.
                aammondd

                a temp table is a just the same as any  table of field names in Manage Database I call it a temp table because the data is only in it for a temorary time.

                 

                • 5. Re: Export table with Array fields.
                  VeenaHampapur

                  Dear Phil and AAmmondd,

                    I am new with filemaker and would appreciate anything I can get.  So, for the repetition, if there is no content, I could ignore it.  So, I would want the list of the array which has data in it.  Do I create a table in the script and copy the contents and then export that table?  Do I have to copy field by field into the new table with the primary key.  When you say temp table, can I just create it in the script and dump it after I am done exporting?  Or do I need to create the table using ManageDatabase...... menu option?

                    Thanks for all your help.

                  Veena.

                  • 6. Re: Export table with Array fields.
                    aammondd

                    The table is created  in Manage Database.

                    Ill get a psuedo code script in a bit.

                    • 7. Re: Export table with Array fields.
                      philmodjunk

                      Since your are new to FileMaker, consider replacing your repeating field with a table of related records. This can make your export to excel simpler--depending on the format you want to see in Excel--as well as being much easier to work with in many other ways within your databaswe.

                      If you want to see an Excel spread sheet, with one row of data for every repetition in your array, replacing the array with a related table means you can export the data without any temporary table used to make it happen--a much simpler option for you here.

                      • 8. Re: Export table with Array fields.
                        aammondd

                        Phil is correct. It is the better way for your database in general.

                         

                        • 9. Re: Export table with Array fields.
                          VeenaHampapur

                          Thank you all for your help.  Even though I am new to Filemaker, the database exists and I am not planning on changing it.  So, I will have to go with the option of retrieving only those fields and inserting them into an other excel spread sheet.  I will look for the pseudo script.....

                          Veena.

                          • 10. Re: Export table with Array fields.
                            aammondd

                            Are you unable to add a new table to the database. Adding an export table and creating a script would work for you without changing any of your existing functionality.

                             

                            You would use 2 loops

                            A loop of the found set of records

                            Set Variable [$L1; Value= 0]

                            Set Variable [$L2; Value = 0]

                            Goto layout [temptable]

                            Show All records

                            Delete All Records

                            Goto Layout [Maintable]

                            Perform main find // the main records to be exported.

                            Loop

                            Set Variable [$Field1; Value =  MainTable::Field1] //repeated for each non-repeating field in the record

                               Loop

                                Set Variable [$L2; Value = $L2 +1]

                                 If [GetRepetition(fieldname;$L1) <> ""]

                                       Set Variable [$RepeatingField; Value =    GetRepetition(fieldname;$L1)]

                                         Goto Layout [Temptable]

                                           New Record/Request

                                           Set Field [ Temptable:Field1; $Field1] //Repeated for all fields

                                        Goto Laoyt [Original]

                               Exit Loop if $L2 > //max number of repetitions

                               End Loop

                            Exit Loop if $L1 > Get(Foundcount)

                            Set Variable[$L1; Value = $L1 +1]

                            End loop

                             

                            this is very rough psuedo code so dont take it as gospel.

                            Phil might have a better solution

                            This will write out one complete record for each repetition.

                            The alternative is to create a temptable with a field for each repetition and when looping through the repetitions set each temptable field accordingly.

                             

                            • 11. Re: Export table with Array fields.
                              VeenaHampapur

                              Thank you very much.  

                              Veena.

                              • 12. Re: Export table with Array fields.
                                philmodjunk

                                Just so you know, converting a repeating field into a related table is one of the easier changes you can make to your database structure. FileMaker provides a very handy tool you can use with Import records to pull the data from the repeating field into new records in the related table, separated into individual records for each repetition.