1 2 Previous Next 22 Replies Latest reply on Oct 15, 2014 10:17 AM by rothdavid

    Appendable Exports?

    rothdavid

      Is there an export format that allows for Appending data to an existing file?

       

      I was hopeful that exporting to another FMP12 format would insert more files into the existing file but it simply overwrites the former file.

       

      It would be a nice feature of a future version of the EXPORT script step that you could append records to an existing fmp db without error so long as table/field structure matches.

       

      I can host a db, and export several files and them import files into the other db I guess. Its less efficient.

       

      Any comments on this topic would be appreciated.

       

       

      Thanks,

       

      David

        • 1. Re: Appendable Exports?
          Mike_Mitchell

          If you want to go from File A to File B, why not just do that directly instead of doing the export? I'm missing something here ...

          • 2. Re: Appendable Exports?
            rothdavid

            I'm not understanding your comment either Mike. 

             

            I have a scripted export process.  I'm using the same record set, exporting the records mulitple times with a different revenue month assigned. 

             

            IE: Widget exists for 6 months at x value.. This record is exported 6 times, once for each of the 6 months is exists with 1/6 of the total value.

             

            As I export Month one, then I update the revenue month, export again, the prior file is overwritten.

             

            The goal is to get all of these exported records into one file so some reporting can be done.

             

            The report is temporary snapshot, I don;t need to warehouse the data moreso than to do the initial report. 

             

            What would be perfect is to append all the data into an excel document but it doesn't appear any export formats are appendable.

             

            I hope this clarifies what I am trying to accomplish.

             

            Thanks,

             

            David

            • 3. Re: Appendable Exports?
              PalmDBS

              Do it in the other direction.  Make your changes in File A, make your found set be the rcords you want to "export", then run a script from File B that imports from File A.  No middle files needed.  From File B you can directly import the records in File A's table's found set.  Instead of it being an "appending export" from File A, it's a standard import into File B

               

              Mike

              • 4. Re: Appendable Exports?
                Mike_Mitchell

                You don't have to warehouse the data. It can be temporary. You just send all those records to a single FileMaker table (modifying the necessary value each time) and, once they're in the destination, export once to Excel from there. This will accomplish the "append" for you.

                • 5. Re: Appendable Exports?
                  rothdavid

                  Thanks PalmDBS and Mike for your comments.    I can't replicate all the revenue months in File A, I would be duplicating several records over and over again in a live system and it would otherwise skew reporting if users happen to run some.  Testing the script I have so far, I start with 53 records in File A, but after exporting the same records over and over again, once for each revenue month they exist, I had 102 records total.  Each export, I had to exported into a new file, I had 10 total, and I imported the later 9 into the first one.  the process acheived the goal of having all these in single file ready for export but its not functional to script out that way.

                   

                  Over 15 yrs of FM development, I've never used the Copy Record Or Copy All Records script step.  Reading up on it this morning, using a test DB with a single table, copied the table, pasted it into the file so I had a blank copy of the same table.   Using COPY RECORD option (right click), I move to table 2 layour I have open in another window, I cannot seem to paste it into the new empty table.  I create a blank record, try paste, that doesn't work,  entering the first field, I paste, I get a tab delimited paste of all the records in that first field though.

                   

                  Mike, If I create a temp table to store these records inside the solution to replicate the records as needed, how is it that I "send all those records" to this new table.  I've read up on the copy/record / request and copy All script steps.  What I don't see is how you paste them into a different table.   I assume you mean I could copy the found set of records and paste them all into this temp table some how.  I can  do the export/import process into the temp table work with that.   

                   

                  I develop in a vacuum and I feel a bit silly that this is a bit perplexing for me. 

                   

                  This is a hosted solution on FM13 server win box, I'm using FMPA 13 win.

                   

                  David

                  • 6. Re: Appendable Exports?
                    Mike_Mitchell

                    Do what PalmDBS suggested. Create a "scratch" table with all the necessary fields. This can be inside your hosted solution or in a separate file; doesn't matter much. Then, write a script or scripts that perform(s) these steps:

                     

                    1) Go to a layout based on the scratch table.

                    2) Delete all records.

                    3) Begin a loop.

                    4) Go to a layout based on the source table.

                    5) Set your temporary field to the desired revenue month.

                    6) Go to a layout based on the scratch table.

                    7) Import the records from the source table to the scratch table (using Matching Field Names).

                    8) Loop back to step 4 (repeat as many times as needed).

                    9) Go to a layout based on the scratch table.

                    10) Show all records.

                    11) Export to Excel.

                     

                    Done.

                    • 7. Re: Appendable Exports?
                      rothdavid

                      I have one last question regarding step 7 above. 

                       

                      I was testing table import process this morning.   The source table has 120 records, I had 53 in my found set on source table, , I went to the Scratch Table (empty) in the same db,  I imported the source table and instead of only importing the 53 in the current found set, It imported ALL the records.   I seem to recall that if you had an active found set on a layout, when you imported from that table, only the found set would import.  That didn't happen this morning.

                       

                      I did come across another thread regarding this approach, you answered on this thread too.   https://fmdev.filemaker.com/message/108214#108214

                       

                      thank you for assisting with this process.

                       

                      David

                      • 8. Re: Appendable Exports?
                        Mike_Mitchell

                        It should only import the found set. Make sure the found set is on the same table occurrence from which you're importing. That would be at the top of the Import dialog.

                        • 9. Re: Appendable Exports?
                          rothdavid

                          I have two tables, Source and Scratch in the same file.  Two layouts. source/scratch

                           

                          source has 102 records - scratch is empty

                           

                          source window has 17 records in the found set. 

                           

                          from the scratch table, using file Import, I select the file, select source table, fields are mapped and import..

                           

                          I get 102 Records imported, not the found set of 17. 

                           

                          Screenshot.png

                          • 10. Re: Appendable Exports?
                            Mike_Mitchell

                            Please take a screen shot of your Import dialog.

                            • 11. Re: Appendable Exports?
                              rothdavid

                              How Ironic. I just did the same exact process again to collect the screenshot

                              ...  It only imported 17 records..  Tried again.. 17 records again.

                               

                              Screenshot2.png

                              • 12. Re: Appendable Exports?
                                Mike_Mitchell

                                Good.  

                                 

                                Use "matching records" instead of "last order" (in case you change something later), Otherwise, you should be good.

                                • 13. Re: Appendable Exports?
                                  BruceRobertson

                                  Don't forget that you can click the arrow >> by Field Names and see data from the records you are about to import; and also see the count of records that the import will process.

                                  • 14. Re: Appendable Exports?
                                    rothdavid

                                    I just set up the scratch table in the live DB, Mapped fields from the source table (using the correct TO that the source layout with the found set had) and it started importing a few 1,000 records instead of the 55 records on the source TO had on the other layout.

                                     

                                    I'm going to create an export fmp12 file to the app temp folder, go to Scratch table and import it. 

                                     

                                    Thanks guys for this lesson in APPENDABLE EXPORTS ! 

                                    1 2 Previous Next