4 Replies Latest reply on Sep 10, 2010 8:21 PM by ChuanyauLim

    Merge two tables with identical fields but different records into one final table

    ChuanyauLim

      Title

      Merge two tables with identical fields but different records into one final table

      Post

      Hi I have two offices running identical FM file with each side storing separate records. As a supervisor, i would like to monitor two offices records in one single FM file.

      eg. Office A (sold item X) while Office B (sold item Y). I would like to see both records (item X&Y) both appear in the supervisor's copy of FM.

      The simplest way would be to manually export records from both offices and import all into the supervisor FM file. However this is cumbersome and not dynamic.

      If i have access to both separate files on my desktop, is there a way to automatically pull the data out of each FM file and auto merge the records into one complete table in the supervisor FM file? 

        • 1. Re: Merge two tables with identical fields but different records into one final table
          philmodjunk

          Yes, but there are picky details that can make this a major headache to do depending on how you've set up the two files.

          Do you have serial numbers being used as primary keys in your tables. Have you done anything to make all the serial numbers generated in office A's file distinct from those generated by Office B? If not, then when you import the data to merge the files, you'll get records with duplicate values that match to a group of related records that come from both files--a real mess!

          If you are going to import the data on a regular basis, you'll also have to manage updates and changes to existing data. You can add fields that auto-enter modification dates for this, but again, this becomes another detail you need to manage during the import process. You'd probably do a find for all records with modification dates later than the last merge date and use an update matching records import with the right option selected to add the records that don't match.

          If you could host your database from a single server that both offices could access--either a WAN with FileMaker clients or a web published system with web browsers used to access the data, you could avoid this whole issue. Security settings can be designed to restrict each office to only their own data.

          • 2. Re: Merge two tables with identical fields but different records into one final table
            ChuanyauLim

            All items contains an unique ID.

            My current system are as such: Each office have a copy of FM housing their respective items. It auto-creates a backup hosted on Dropbox. So now i have two FM DB backup on Dropbox.

            The supervisor has 2 copies of FM, each automatically pulls data from the respective backup using "Manage data source" function found by right clicking respective tables under relational graph section in "Manage my database".

            The limitation of "Manage data source" is that it can only pull data from 1 table instead of multiple tables.

            The workaround is that I could manually export data (pulled from backup) from one supervisor file to the other to merge the two DB from different office into one complete table. However, i'd like to find a solution that automatically does this for me each time i open the supervisor file.

            • 3. Re: Merge two tables with identical fields but different records into one final table
              philmodjunk

              I can't imagine how you'd be able to use Manage data source in this way without the risk of major problems. Simply opening the separate file copies would be much safer.

              As I said before, you can merge your data with the import records command. A script that performs an import records step for each table in your file can make this a once click operation. There are design issues to be managed carefully here to avoid issues.

              If you decide to continue to have separate files here--to me that's a major problem to be avoided if at possible--Make sure both files have identical table and field names as this will make the import process much safer by avoiding a long standing bug that can scramble your data during import after you add a new field to one of the tables and don't also update your import script.

              For More Information on this bug see:    Data loss bug : Spontaneous and erroneous import matching of new fields in specified imports !

              This is one of many acknowledged bugs that can be found in the Known Bugs List here in the Report an Issue section of the forum.

              This list can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html