3 Replies Latest reply on Apr 23, 2010 10:40 AM by blewvelvet

    EXPORT multi tables to ONE tab delimited file.



      EXPORT multi tables to ONE tab delimited file.


      So I've got a bunch of individual scripts which pulls various fields (in a specific order) from separate tables into a tab delimited file.


      I end up with multiple files. One file per table/export


      In the end..this is to IMPORT into Accounting software...and to eliminate having to manaully merge large amounts of data into one excel sheet and then have to run macros which insert a blank line in between each record..This line break tells the accounting program that it is to be treated as a new invoice/expense/job/deposit.


      If there is no extra blank row break in between each record/line..the Accounting software  treats the lines together as belonging in the same document (for example ) multiple invoices...the charges would be put on the same invoice.


      I would like to build a script which "holds" the export data in "limbo" while the others are compiling and export into one tab delimited file.


      Is this possible?

        • 1. Re: EXPORT multi tables to ONE tab delimited file.

          What would you think about creating a table called "Export Data".  Rather than writing your script to perform exports immediately, write it to go to the various tables and (through loops), create new records in the Export Data table.  Then have the script export a single file from the newly-created temporary records.  After the export, the script can go back and delete all of the created records.



          • 2. Re: EXPORT multi tables to ONE tab delimited file.

            I would start from the other direction.  What is the format that you accounting system needs for the import to the file.  The one I know off the top of my head was Peachtree8.  I could import invoices for payment to Peachtree8, but it had to be two files, one for the invoice and one for the invoice distribution.


            In my current situation, I key invoices into a Filemaker, a table for the invoices and a table for the distributions.   However, my export file has to be one file, I export from the distribution table.  The first four fields of each distribution record is from the invoice table:  vendor number, invoice number, invoice date, and invoice total. 


            I hope this helps.

            • 3. Re: EXPORT multi tables to ONE tab delimited file.

              This is brilliant! I think this is my answer.


              I need to experiment with this. I find that when I do scripts..since I don't know all the rules..it's a "trial and error"  (and that works for me because I will remember them long term.) So far I've gotten so many things to work like custom PDF "file naming" so there is never a need to rename a PDF file...and also finding talent types in one click (i.e. ALL FEMALE Asian (age range) - which our talen scout loves!) Even casting (1st, 2nd and 3rd choices) sheets to send to the client. We can generate that in minutes! 


              For the other response here..Thanks..but I think I was confused reading it ( I may have to go reread it over). However, I cannot go backwards and reprogram any aspect of this database for the benefit of the accounting program. It was built and evolved for the sole purpose of efficeint administrative flow for a film/theatre production and scheduling.


              We did some testing of various accounting software and found that there were WAY too many blocks and hurdles for us to import data in programs like Quickbooks and to be able to manipulate the key fields back and forth between the ID in accounting software and the record ID in filemaker.


              In "AccountEdge" (AE) software we have control of keyID of any record id / invoice id / job IDs and it will update the data from filemaker database!!! This was the deal breaker! So AccountEdge allows us to generate any invoice#, estimate# or record ID we need to match up and will update it if we want it to.  We are ONLY exporting (in one direction) to "AccountEdge". By comparison to Quckbooks (and a few others) AE turned out to be the best "Mac", "Filemaker" and "import friendly" program we found. So we chose it. AccountEdge has it's own user interface limitations..but we covered all that in our own database and AE hasn't disappointed us yet. I've inputted so many expenses, projects and invoices (backtracking since January to get caught up to date) in less than 2 days..(the 2 days were fine tuning the export scripts) Now with the scripts..I could do the same backtracking job in a few hours (maybe under 2 hours!).


              The ONLY reason we even needed a separate accounting program is to write checks and for year end tax purposes! The filemaker database already gives us up to the minute project tracking and reporting on balance due, live budgeting (over or under budget during film production) percentage profit, per project or per found set., etc. While I'm sure AccountEdge can do this also..it just doesn't have all the creative "user interface beauty" of the customization of Filemaker (which I think is one of the best programs ever created next to video editing software - and do I really need to preach this in a filemaker forum..no..but I just love it.) Filemaker custom databases has made accounting, tracking and doing this stuff actually fun!  Filemaker may replace our filing cabinets soon!


              Thanks again for the input. I'm going to start on the "export data" and scripts to "pull and compile" in a few days. I guess a lot of "copy field" and "set fields" will be used back and forth between two open windows in these scripts.


              So it would go something like this?


              go to related record - Open new window (Table)

              copy field (blah blah)

              Open new window (name) go to Layout (Table - Export Data)

              New Record

              set field (blah blah)

              Go to (back to) Window (Table)


              Rinse and repeat...for each field in each record


              there may be a faster way to do this using variables..maybe...probably..I would have to experiment


              Thanks so much.