5 Replies Latest reply on Apr 10, 2013 12:05 PM by philmodjunk

    Relating Two Files/Databases

    LeahRiley

      Title

      Relating Two Files/Databases

      Post

           I have two distinct databases.  One is about customers (who they are, what product they received, when, etc).  The second is about our products/data (Our expected performance, the physical properties, etc).

           These two databases can be linked by the Product Name.  

           Both databases are complex with many connections/tables.  For example, the products/data database has product name, linked to format tested linked to performance data.  

           PROBLEM: I want to be able to be in my Customers database and be able to pull up performance data from my second database.  However, I don't want to load up every single table and relate all of them.  

           QUESTION: Is there a way to use my existing links to get my information?

        • 1. Re: Relating Two Files/Databases
          philmodjunk
               

                    These two databases can be linked by the Product Name.

               You can, but it's not really a good idea in most cases. Names Change and when they do, your links to other data via that name based relationship get screwed up. Using a serial number bsed Primary Key is a much more robust way to link your data.

               QUESTION: Is there a way to use my existing links to get my information?

               Don't know. Don't know the design of your existing links.

               BUT: Filemaker used to be limted to one table for each file and we routinely linked multiple files in relationships in order to link multiple tables in relationshps. The current versions of FileMaker still have this capability. You can go to Manage | Database | Relationships and use the button in the far left, bottom corner to create a new table occurrence. Inside the dialog box that pops up when you use this tool is a drop down where you can select "Add filemaker data source". YOu can then add an external data source reference to the table or tables from another file as needed in order to link your data.

          • 2. Re: Relating Two Files/Databases
            LeahRiley

                 "BUT: Filemaker used to be limted to one table for each file and we routinely linked multiple files in relationships in order to link multiple tables in relationshps. The current versions of FileMaker still have this capability. You can go to Manage | Database | Relationships and use the button in the far left, bottom corner to create a new table occurrence. Inside the dialog box that pops up when you use this tool is a drop down where you can select "Add filemaker data source". YOu can then add an external data source reference to the table or tables from another file as needed in order to link your data."

                 Database = DB, Table = T

                 To this point, I can do that and have seen this similar suggestions on this site (thank you for that, BTW, your responses are very helpful).  However, I have a problem seeing ALL tables from my other database.

                 DB1:T1 linked to DB1:T2

                 DB2:T1 referenced to DB1:T1

                 I want to be in DB2 and look at DB1.  Through the interdatabase reference, I am able to see DB1:T1 but NOT DB1:T2, even though those are linked in DB1.

                 My real database is much more complex than two or three tables, but the general idea being that I want to be able to access 100% of another database (DB1) from whatever database I am in (DB2).

            • 3. Re: Relating Two Files/Databases
              philmodjunk

                   And that would require pulling in the needed table occurrences for each of the tables.

                   But there is an alternative that may serve either to completely eliminate the need for this or to reduce the number of table occurrences that you have to add to DB2 in order to get the results that you need:

                   Set up scripts that take the user back and forth between files. The scripts can pass needed data in a script parameter so that you can examine the needed data from the context of the file where you defined the needed relationshps.

                   Another option might be to look into FMMIgrator (I haven't used this one personally) as it is supposed to facilitate merging two files into one and that might save you quite a bit of time here.

              • 4. Re: Relating Two Files/Databases
                LeahRiley

                     Thank you for your response.

                     From your description of the different methods, it sounds like designing a single database, rather than two or more different databases, if preferred to easily access all data.

                     Do you know the reason why two databases can't be easily linked through a single reference?  I'm curious if there is a logistical or programming reason.

                • 5. Re: Relating Two Files/Databases
                  philmodjunk

                       I didn't design the database application.

                       They CAN be easily linked, but it's one table at time. You get easy access to the tables, not the table occurrences and relationships. Whether you actually need all of that will depend on what you need to do with your database.