10 Replies Latest reply on Feb 4, 2015 3:07 PM by SeligL14FM

    Save Records as Excel and Export Records behave differently

    SeligL14FM

      Summary

      Save Records as Excel and Export Records behave differently

      Product

      FileMaker Pro

      Version

      13.0v3

      Operating system version

      Mac 10.8.5

      Description of the issue

      I have a Call table and a related Substance table. I want the users to do a daily export of the data for backup. Currently they do this in 3 steps:
      1. Export Records from a button on the Call List (Export set to Call Table to ensure all fields exported).
      2. Same with Substance Table, from the Substance Table List
      3. Save a Copy of the database (whole database)

      This works fine and the exports are fast (7000 and 4000 odd records).

      I have been asked to combine these in one script and have tried both Save Records as Excel and Export Records and fine that I am getting different results (smaller file with fewer fields in Save as Excel).

      So first question is should these be the same, or is there a difference between Save Records as Excel and Export Records?

      In order to do this in the combined script, I switch to the Call List Form then export the Call records, then switch to the Substance List form to export the Substance records. To be sure I got all fields I actually switch to the Input (Detail) forms to be sure.

      When I set the export I actually set to to Call Table and not a Layout but since I am not showing a dialog, I can't see what is actually being used.

      The related Substance table with half the records, exports very slowly (in comparison to doing the export manually from a button on the Substance List from).

      I also see that when I am writing the combined script, I must be in the Call List to set the Export to using the Call Table and must switch to the Substance List to o set the Export to using the Substance Table.


      SUMMARY.
      1. There seems to be a difference between Save as Excel and EXport Records that I did not get from the documentation.
      2. Even wehen scripting for the two exports I need to be on the corresponding table (form) else the wrong Table/Forms are shown.
      3. Question to prevent all the form changes I want to use Freeze Window then Redraw Window and presume that will still work the same way?

      SCRIPT (In case you want to review it)
      ATTACHED - as SCRIPT WHOLE  -I had to combine the screen shots so sorry is tough to read

      SCRIPT_WHOLE.jpg

        • 1. Re: Save Records as Excel and Export Records behave differently
          TSGal

          Selig Leyser:

          Thank you for your post.

          Save as Excel will export the fields on the layout to an Excel file.  The export order is dictated by the placement on the layout, top to bottom, left to right.  For instance, if you have fields F1 through F10 in the first column, F11 through F20 in the second column, the export order will be F1, F11, F2, F12, ... F10, F20.

          If there are portals on the layout, the contents will also be exported.

          With Export to Excel, you can specify which fields and field order you want exported.

          Just as you would manually, go to the layout where you want to perform the Export Records or Save as Excel so you can use the desired reference.

          What are you seeing in your Excel files that is unexpected?

          TSGal
          FileMaker, Inc.

          • 2. Re: Save Records as Excel and Export Records behave differently
            SeligL14FM

             

            Thanks for your reply.

             

            There are actually two issues that have materialized:

            A. Save as Excel or Export Records (to Excel) don't behave the same - from the same routine with just the Save as Excel and Export (as Excel), I see a smaller file with fewer fields - Call Table - Save as Excel to Excel column W, file size 249 kb, while Call Table - Export (as Excel) to column BS, 512 kb

            B. Export of related file may be very quick or prolonged without explanation. In the latter case Numbers has to be a Forced Quit as it can't open the file.

             

            I think I have established the conditions under which it occurs:
            If I perform  the Export of Substance (many related file to Call) from the Substance List form all is well.
            BUT if I run the same script from another List form e.g. Call List, the export of the Substance file takes several minutes (even though it is half the size of the Call file) and when I try to open it in Excel I have to Force Quit.
             
            Now if I run the Call Export from the Call List it seems OK. And if I then switch to the Substance table, the first time I run the Substance Export, I get the delayed export and the apparent;y corrupted file. If I REPEAT the Substance Export from the Substance Export it is fine.
             
            If I run an Export routine from the Substance List form with a Dialog that requires me to set the Export to be from the Substance Table and then move all the fields to Export it is OK.

            So the Export without a dialog in a Script and the Export with a dialog run from a button on the appropriate List form do not behave the same when the script is run form another Table (even through the Layout and hence the Table is switched in the Script)


            Comments.
            1. With the exact same script with the only difference being Save as Excel or Export Records (to Excel), I am getting significantly less fields with Save as Excel, allowing for File vs Detail form and very different file sizes,

            2. I have a button on the list forms and a script in the script menu that both perform Export Records (with dialog) and I have also coded to perform the exports of the two related files without dialogs, choosing to export from the Table (not a form) so ALL fields will be exported.

            The buttons on the List forms that use a dialog seem fine.

            3. The many related table exports either very quickly or takes several minutes. The conditions under which this problem occurs is when I run the script from a different List form (even though I switch to the List or even Detail form for the correct file and set the Export from the Table)

            4. The latter frequently doesn't open properly with Numbers (or other office suites). Numbers has to be Forced Quit.

             

            5. Since the purpose was to automated the export of both Call and Substance data, this buggy behavior makes it untenable.

            Any suggestions greatly appreciated.

            • 3. Re: Save Records as Excel and Export Records behave differently
              TSGal

              Selig Leyser:

              With the Save as Excel, it will use the fields displayed on the layout and export the data.  It will not include all fields unless you have all fields on the layout.

              The Export asks you to specify the fields.  Since this is going to column BS, this means 71 fields have been exported.  Check the export order.

              If running the script from the active Call List table is taking "several minutes", how many records exist in the Call List table?

              TSGal
              FileMaker, Inc.

              • 4. Re: Save Records as Excel and Export Records behave differently
                SeligL14FM

                 

                TSGal

                Thanks for prompt reply.

                There are 7000 Call records and 4400 Substance records.

                I see the difference with the Save as Excel and Export (as Excel) and since I want to export ALL the  fields, using Export and choosing the Table rather than the form should do this.

                I have found that even when I use the Export function with a dialog from the List form of Substances, I sometimes get th along export the FIRST time I run it and it usually is OK the second time.

                Very frustrating.

                • 5. Re: Save Records as Excel and Export Records behave differently
                  TSGal

                  Selig Leyser:

                  The initial export needs to read all the data from the table (especially any unstored calculations) and store it in the cache.  Subsequent exports would be quicker due to the data being cached.

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: Save Records as Excel and Export Records behave differently
                    SeligL14FM

                    BUT

                    1. Why does the delay not occur in the Call (One) file and only in the Substance (Many) file.

                    2. Why is Numbers unable to open the Substance Export that took a long time and in fact Numbers has to be Forced Quit.

                    This suggests to me that in the scenario I have outlined corruption of the Export must be occurring.

                    Note that the Substance Export that seems to work OK  takes less than a second for 4400 records and takes at least 5 minutes to complete when I cannot open the file in Numbers (or LibreOffice or OpenOffice)

                    • 7. Re: Save Records as Excel and Export Records behave differently
                      TSGal

                      Selig Leyser:

                      Without seeing the file, I cannot determine why one way is taking longer than another.  If you want, send in your file, and I'll try it here.  Check your Inbox at the top of this page for instructions where to send your file.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: Save Records as Excel and Export Records behave differently
                        SeligL14FM

                         

                        OK I have more data and at least a way to reliably reproduce the problem.

                        1. If you run a Call Export (to Excel) running without a dialog, switch to the Call List and setting the Export to all fields from the Call Table you get an export that takes about 3 secs for 7700 re cords and opens fine in Numbers.

                        2. If you then run the Substance Export (to Excel) (the related Many Table) STARTING AT THE CALL LIST (or for that matter another List form even an unrelated Table) but switching procedurally to the Substance List then Export using the Substance Table, THE FIRST TIME it will export VERY SLOWLY, taking 7 minutes for 4400 records. This will freeze Numbers (LibreOfiice or FreeOffice or Preview (spacebar) - which I believe suggests it is CORRUPTED. THIS IS REPEATABLE. File Size 325kb (larger than the fast viable export - see below)

                        3. NOW IF YOU REPEAT THE SUBSTANCE EXPORT FROM THE SUBSTANCE LIST it will export fine, taking 1 second, and it will open fine in Numbers and Preview, File SIze 277 kb.

                        4. I removed all the relationships between the two files and the above behavior remains.

                        MY CONCLUSION:

                        Using the Export Records (as Excel) procedurally, IT MUST BE RUN PHYSICALLY FROM THE APPROPRIATE TABLE (FORM) EVEN WHEN THE FORM IS SWITCHED PROCEDURALLY.

                        If you do not do this, the first export will take a very long time to open (7 minutes vs 1-2 sec s for 4400 records) and will be corrupt. If the export is repeated from a form from the appropriate Table it will work correctly the second time.

                        This is true even when the relations and portal are removed.

                        CANNOT EXPLAIN

                        Not sure why the Call Table Export works correctly even when run from another Table (even though the Form/Table is set procedurally)

                        • 9. Re: Save Records as Excel and Export Records behave differently
                          TSGal

                          Selig Leyser:

                          I received your file.  Thank you.

                          In your Substance Export, you are including a List Summary field (which lists all values from the records) plus an unstored Calculation field against the same List Summary field.  The export completes after several minutes (as expected), and at the end, I do get a message that data has been truncated due to the length of the Summary values.  After dismissing the error, the resulting Excel file then closes, and I am then able to open the file in Excel.

                          If you remove the Summary field and Calculation fields from the export order, the export is quick.

                          If you need to have the same value of the Summary field for each record, along with the unstored calculation result, it would be quicker to place those values in another text field, replace the field contents, and then export the data substituting the Summary field and Calculation field with the two new text fields.  If you just need it for one record, the write a script to go to the first record and replace the values into the text field.  For example:

                             Go to Record/Request/Page [ First ]
                             Set Field [ <First Text field> ; <Summary Field> ]
                             Set Field [ <Second Text field> ; <Calculation Field> ]

                          Let me know if you need additional clarification.

                          TSGal
                          FileMaker, Inc.

                           

                          1 of 1 people found this helpful
                          • 10. Re: Save Records as Excel and Export Records behave differently
                            SeligL14FM

                            Thanks so much for posting this out. I had forgotten about those fields as I do not use them.

                            Works beautifully after I deleted them.

                            I appreciate your prompt replies.