5 Replies Latest reply on Feb 4, 2010 4:26 PM by satellite_1

    Need Help Merging Data from Two Databases into One Database

    fredmckinnon

      Title

      Need Help Merging Data from Two Databases into One Database

      Post

      Hey Gang,

       

      First, I've researched and read the "Synchronizing or Merging Two Databases While Updating Records" article.  I *THINK* this may be what I need, but I honestly get lost in it and can't quite understand it.

       

      I have two separate database files from my distributor.

       

      One file includes the items I'm approved to sell, and includes these fields:

       

      Database 1:  Approved Items

      item_no

      price

      map_price

      qty_available

      upc_code

       

      Database 2:  Full Inventory

      item_no

      vendor_sku

      MSRP

      brand

      category

      title

      description

      upc_code

      length

      width

      height

      weight

       

      Now, I need to pull several of the fields from Database 2 into Database 1.  

      item_no would be the best field to use as a "match" (trigger?).  

       

      Is there anyone out there who would be patient and gracious enough to give me a step-by-step on how to do this?  Assuming I have both files open (I'm using Filemaker 7, but assume it will still work).

       

      I would like to pull ALL of the fields mentioned above in database2 into database 1, using the item_no field to match them up.

      (this would be required instead of going the other direction, as there are items in database 2 I'm not authorized to sell ... Database 2 has over 20,000 records, while Database 1 has around 17,000)

       

      Thanks in advance!
      Fred 

       

        • 1. Re: Need Help Merging Data from Two Databases into One Database
          philmodjunk
            

          Instead of merging the files, simply use item_no to link them with a relationship. Much simpler and faster with much less modification of your database design.

           

          With such a relationship designed, you can place fields from database 2 on a layout in database 1 and see data such as title or description for the current item.

           

          I'd give you step by step, but don't have filemaker 7 installed on my machine and it may differ in a few respects from FMP 10.

           

          In general terms, you'd add a new table to your relationship graph and use "add filemaker data source" to find and specify the table in database 2. Then you'll drag from item_no in one table occurrence to item_no in the other to relate them.

           

          Then you can add fields from this new table occurrence to a layout in database 1.

          • 2. Re: Need Help Merging Data from Two Databases into One Database
            fredmckinnon
              

            Phil,

            I'll try that ...  as you can see, I'm a novice Filemaker guy, so you lose me at:

            "relationship graph"

             

            I'll see what I can come up w/ though!

            • 3. Re: Need Help Merging Data from Two Databases into One Database
              philmodjunk
                 Press f1 and look up terms like table and relationship and see what you get. In filemaker 10, you'd select manage | Database from the file menu--but believe you have different options in the file menu for version 7.
              • 4. Re: Need Help Merging Data from Two Databases into One Database
                fredmckinnon
                  

                Thanks gang,

                 

                I figured out pretty easily how to define the relationships between the two files.  I copied the fields i needed into one layout from both files ... voila!

                 

                Then, when I went to export the filemaker database to a .txt (Tab-Delimited) so that I could copy/paste it into the required upload format/fields for Amazon.Com's marketplace, I realized I didn't even really have to drag the fields into the layout ... in the export windows, I can choose what fields I want exported from all fields in the related databases.

                 

                So, it took me like 10 minutes, and I've got it.  Thanks so much for your help!

                • 5. Re: Need Help Merging Data from Two Databases into One Database
                  satellite_1
                    

                  I have a related question to Fred's.

                   

                  I need to update a master database with records from a secondary database. The latter will have some completely independent records and some records which contain more accurate field data than their corresponding record in the master. Right now, the only way I can figure this out is per the "Synchronizing or Merging Two Databases While Updating Records" article: creating lookup relationships for all the fields in the master database so that I can run a relookup based on a key field common to both databases and pull data into the master. The problem with this is twofold: one, the databases have a lot of fields so that's a lot of lookup-field mapping, and two I have to do this about a dozen times with a dozen different secondary databases. 

                   

                  I had thought that this was doable with import and not-empty validation in the fields to be updated. It almost works but just one empty field in the source file will cause the import to skip the entire record.