5 Replies Latest reply on May 16, 2012 10:09 AM by philmodjunk

    Transfer Data to Master Database

    rounakjain

      Title

      Transfer Data to Master Database

      Post

      This is my file:

      http://dl.dropbox.com/u/9738427/Restart%20copy%202.fmp12.zip

      I want to do this:

      1. Enter data everyday in this file. Basically, data entry is made only in Invoice Table and its portal of Line Table. Every other table gets data directly or indirectly from the Invoice Table.

      2. At the end of the day transfer data to another file.

      3. Delete data from the file in which I enter data everyday.

      4. While deleting data, retain records in specific tables like "Customer", "Product", "CustomerProductJoin" so that the data contained in them is available to me in the drop-down lists and look-ups.  (This probably means that I only need to delete the records contained in Invoice and Line Table tables)

      Kindly guide me.

        • 1. Re: Transfer Data to Master Database
          philmodjunk

          Don't have time to type up the full steps for this, but we export our PO's (they'd be invoices but the money goes in the opposite direction...) every night to an archive file.

          For starters, look up import records as a script step. You'll run a script from the archive file that imports data from each designated source file in your invoices file into a matching table in the archive file.

          From there you'll want to mark records as exported and delete them only after confirming successful import into the archive table.

          And this is not a script that's easy to set up as server schedule. We use a robot file as it greatly simplifies the process.

          But we do use a server controlled script to check for successful export and then deletes the exported records.

          • 2. Re: Transfer Data to Master Database
            rounakjain
            For starters, look up import records as a script step. You'll run a script from the archive file that imports data from each designated source file in your invoices file into a matching table in the archive file.

             

            From there you'll want to mark records as exported and delete them only after confirming successful import into the archive table.

            What type is this archive file? By saying "into a matching table in the archive file" ...... do you mean your archive file is a .fmp file?

            How to mark records as exported? Does "confirming successful import" mean matching the critical grand-totals of exported records in the master and slave file?

            • 3. Re: Transfer Data to Master Database
              philmodjunk

              The archive file would be a fileMaker file. From your original post, I am assuming that you want to use a separate file for this, but it could be a set of tables in the same file. It can even be a clone of your current file. The key is that you need a set of "archive" tables with exactly the same fields as the tables from which you plan on archiving this data.

              "marking" a record as exported would be setting a field in that record to a specific value to show it as exported. It could be a field that is empty until a 1 is entered to show that it is exported. This may not be necessary in your case, but I found it useful in mine.

              To confirm a successful import, there are several ways. One is to use a script to count the number of exported records and then count the records in the Archive file for that same date and confirm that they are the same. A relationship between the two tables can be used if it matches by date. Comparing totals would also be an option for confirming the export.

              Before doing any of this, please double check and make sure that it's truly necessary. We implemented this method back when Filemaker, CPUs and Networks were much slower and needed to have client machines that were just as fast to respond to the user as possible so that employees could process a large number of customers as quickly and as smoothly as possible. I'm not sure it's truly necessary if I were to start over from a blank page and design this system in FileMaker 11.

              • 4. Re: Transfer Data to Master Database
                rounakjain

                I am using Filemaker file as my archive file. I have an import script which has "updates matching record in found set" selected and "Add remaining data as new records" checked for every table. The primary key of the table is the matching record. It works fine except for a table "Line Table". Only those records are imported which are in the current found set (generally records of the last invoice). I don't know how to do a "Show all Records" in the source file via script which is present in my target file.

                • 5. Re: Transfer Data to Master Database
                  philmodjunk

                  In your target file, create a script and add the Perform Script step.

                  Click the Specify button to select a script to be performed.

                  In the very top drop down, that says "current file", select your source file. If there is no existing external data source reference to the source file, select "Add FileMaker Data Source". This opens a dialog box where you can find and select source file. Once you have selected the file, you'll see a list of the scripts in that file and you can select the "Show all records script" you need to update the found set to all records before you import records from that table.