12 Replies Latest reply on Sep 1, 2009 1:44 PM by etripoli

    How do I import Many to Many relationships from MySQL via ODBC?

    SwissMac

      Title

      How do I import Many to Many relationships from MySQL via ODBC?

      Post

      I am using the Actual ODBC commercial driver to connect to a MySQL 5.05 database so I can read the MySQL db in Filemaker Pro 10 Advanced on my Intel Mac mini running OS X Leopard 10.5.7. My goal is to have the whole MySQL database reflected in my Filemaker file so I can more easily query and generate reports from the information we store on the server. 

       

      While the Filemaker help file is generally useful in some areas, I can't find instructions for how to import/connect to a multi-table MySQL database that has many "Many to Many" joins in it and over 50 tables (including about 20 look ups). Should each table be imported separately and then joined through relationships or code (and then one table at a time or all tables together), or some other way? The External SQL Source doesn't seem to allow for this, but Many to Many joins are so fundamental to databases I can't believe it is impossible to do... so, how?

       

      One example of such a relationship is:

       

      Table: dClient

      Fields:

      - ClientID

      - ClientName

      etc

       

      Table: dAddress

      Fields:

      - AddressID

      - Address

      etc 

       

      Relational Table: rClientAddress

      Fields:

      - ClientID

      - AddressID 

       

      This arrangement allows one client to have many addresses, and many people to have the same address (eg each member of a family, people living in multi-occupancy buildings etc). We have many other instances of similar setups in the database, and with some 500 clients and 300 addresses Value Lists are out of the question. Can I use the External SQL Source feature, or do I need to use some sort of script?

       

      All suggestions welcomed.

       

      TIA

       

      Mac 

        • 1. Re: How do I import Many to Many relationships from MySQL via ODBC?
          etripoli
            

          Each mySQL table should have a primary key, so you could have one table instance in FM for each mySQL table, and have access to the live data.

           

          The second option, is to Import...ODBC, and periodically refresh the data through an import.  I use both methods, depending on the situation.

          • 2. Re: How do I import Many to Many relationships from MySQL via ODBC?
            SwissMac
              

            Many thanks for your quick reply.

             

            I prefer the sound of your first solution, the one with the live data access but am not sure exactly what you mean about "every table needs a Primary Key". A long time ago I had to use Primary Keys in MS Access which needed relationships to be defined as fixed entities, but when we switched five years ago to MySQL with PHP as the "front end" we linked tables only at a query level using an ID field - in my example above, ClientID and AddressID linked to each other through the rClientAddress table. If for instance we had a lookup table for "Country" we might for instance use the following:

             

            Table: dAddress

            Fields:

            - AddressID

            - CountryID

             

            Table: kCountry

            Fields:

            - CountryID

            - CountryName

             

            I have read this look up functionality can be achieved in Filemaker using Value Lists, but I am not sure how that could link to our live MySQL data. However I use this second example to ask if we both think of the same thing as the "Primary Key" ie the index field such as AddressID in dAddress and CountryID in kCountry? Perhaps using a Look Up link is not a good example.

             

            Am I right in thinking that the Primary Key in dClient is ClientID and in AddressID is AddressID? Or are you referring to something different as the Primary Key? I am not a database expert, and sometimes a little knowledge is a dangerous thing!

             

            (It would be really handy if I could import the MySQL data structure into Filemaker to make the tables!)

             

            Mac

            • 3. Re: How do I import Many to Many relationships from MySQL via ODBC?
              etripoli
                

              Your primary key in each table, is the field that always contains unique values.  It's preferably to have a primary key that is only 1 field, per mySQL table, even if you have to create one (You can have other fields with unique data, just assign 1 when linking to the tables).  It's explained the the ESS Techbrief.

               

              As for duplicating the table structure, if you 'add' the mySQL tables through the Relationship Graph, there is no need to re-create the structure - it's already there.  If you want to work with static data from the tables, i.e. through imports, your first import gives you to let FileMaker create the table for you.  Again, no need to re-create the structure.

              • 4. Re: How do I import Many to Many relationships from MySQL via ODBC?
                SwissMac
                  

                Many thanks for your help, I am making progress but am still unsure if I fully understand what I should do. Am I to add each MySQL Table separately into my Filemaker Relationships Graph? Is there no way of adding them "en masse"? There are something over 40 tables in the MySQL database to add to the Filemaker db and there must be a quicker way than adding the MySQL Tables one at a time, and still keep live access to their data... I have tried Apple+A and Apple+Shift to try to "Select All" but this seems not to work. Am I doing something wrong? 

                 

                Are you saying I can only copy all the tables into the Filemaker db by Importing them in which case Filemaker will create all the tables for me? And are you also saying that I can only get live access to the data by adding tables (one at a time) via the Relationship Graph?

                 

                Sorry to be so slow to "get it" but Filemaker is very powerful yet quite different to what I am used to and I don't want to make a wrong move...

                 

                TIA 

                • 5. Re: How do I import Many to Many relationships from MySQL via ODBC?
                  SwissMac
                    

                  OK, I am half-way there... I can now read the data, edit it, and have it update on the server as well as (via a refresh, not instantly) read changes to the database that were made from another application.

                   

                  I still don't know how to add tables from ESS except one at a time though. Perhaps it isn't possible? I can't highlight more than one field at a time in Layout view either, so maybe Filemaker doesn't allow multiple actions at once?

                   

                  TIA 

                  • 6. Re: How do I import Many to Many relationships from MySQL via ODBC?
                    mrvodka
                       You can duplicate the ESS shadow table occurrence a bunch of times. Then you can click each one and change it to the table that you want. It is much faster than adding each one individually.
                    • 7. Re: How do I import Many to Many relationships from MySQL via ODBC?
                      SwissMac
                        

                      OK, that sounds good, but how? You lost me a bit there... what is the "ESS shadow table occurrence" and how do I duplicate it? Then, how do I change it?

                       

                      TIA 

                      • 8. Re: How do I import Many to Many relationships from MySQL via ODBC?
                        mrvodka
                          

                        The items in the relationships graph that represent tables are called table occurrences.

                         

                        Just select one of them that represents your external source. Then duplicate it a few times. Select one of the duplicated ones and double click. Point it to the new table that you want to reference. Repeat.

                         

                        Keep in mind that this will not create the layouts automatically though. If you want to have layouts based on them, you will have to do it manually.

                        • 9. Re: How do I import Many to Many relationships from MySQL via ODBC?
                          SwissMac
                            

                          Excellent! I just did one - easy as pie. Many thanks for your great time-saving tip - sorry I haven't quite got the terminology sorted out yet, I'm learning fast though. 

                           

                          Can I add two "Solved" ticks in this thread? 

                           

                           

                          • 10. Re: How do I import Many to Many relationships from MySQL via ODBC?
                            etripoli
                               No, but feel free to pass out Kudos!
                            • 11. Re: How do I import Many to Many relationships from MySQL via ODBC?
                              SwissMac
                                

                              What is that on this forum?

                               

                              • 12. Re: How do I import Many to Many relationships from MySQL via ODBC?
                                etripoli
                                   The big yellow/gold box with a star to the right of each post.  It's a ranking system for 'helpful' posters.