4 Replies Latest reply on Oct 30, 2016 6:08 AM by fmpdude

    Lookups

    user27142

      Taken directly from the Filemaker support page at: help.filemaker.com/app/answers/detail/a_id/12068/~/how-to-move-data-from-one-database-to-another

      How to move data from one database to another

       

      Option 2 - Relationships

      Another way to move information stored in a field between databases is to create a relationship and use lookup fields.

      A lookup copies data from another table into a field in the current table. After data is copied, it becomes part of the current table (and remains in the table from which it was copied).

      To establish a connection between tables for a lookup, you create a relationship between the match field of the current table and the match field of the related table. Then you define a lookup to copy data from a field in the related table into a field in the current table (See “Defining lookups” in the FileMaker Online Help).

      When you type or change a value in the match field of the current table, FileMaker Pro uses the relationship to access the first record in the related table whose match field contains a matching value. Next, it copies the value from the lookup source field into the lookup destination field, where the value is stored.

      After a value is copied into the lookup destination field, you can edit, replace, or delete it like any other value (because the lookup value belongs to the current table). You can also update data in the current table to match data that changes in the related table.

       

      I've created basically a mirror table to a ESS/ODBC external MySQL data table online.  In each of the respective fields in the mirror table I've defined a lookup associated with the proper field in the external table (there are quite a few, approx. 350)

       

      Reading above, I'm lead to believe that creating a lookup mapping between the two table fields I can copy data from external table to the mirror table.  It's not working.  What am I doing wrong?

       

      My reason for copying the data from the external source is to prevent direct changes to the data.  It's an online hosted form that's collecting data.  I can attach to it directly, however I'd prefer it be mirrored inside my application to another table and use those fields on form view for browsing and updating the information.  This should prevent updates at the external table as I read the above.

       

      I also realize that I could import the data between tables and I'm trying to prevent having to do that.  Basically, I just want to monitor a copy of the data coming from the external table from another source without needed to reimport as the data grows.

       

      HELP!

        • 1. Re: Lookups
          philmodjunk

          "It's not working" gives anyone who wants to help very little to work with. The devil is in the details and exactly how you tried to set this up is what we need to know in order to provide advice.

           

          And since importing data and using lookups both produce copies of your data, please explain why importing is not desirable.  Both methods produce identical results.

          • 2. Re: Lookups
            user27142

            By not working, I mean I have a parent table with an indexed field.  That's related to the identical field in the mirror table which is related to the identical field in the external table.  What I'm expecting is the value from the parent points to the exact same unique value in the mirror records which then copies the record of that value from the external table to the mirrored table. 

             

            When I place fields on a form from the mirrored table for records I know exist it shows nothing.  I was assuming that the data for that record would be copied from the external table and the fields on the form from the mirrored table would show the data in the external table.

             

            If I link directly to the external source and relate the primary with the external it works fine.  If I introduce the mirrored table into that relationship and look at the data there's nothing there.

            • 3. Re: Lookups
              keywords

              "It's not working.  What am I doing wrong?"  My response to that is to ask what exactly is not working?

               

              Let us suppose you have your DataTable, in which each record has a recordID as its primary key, and your MirrorTable in which each record also has a dataTableRecordID field as a foreign key, and you have a relationship to join these two tables thus:  DataTable::recordID = MirrorTable::dataTableRecordID.

              Now, in the MirrorTable each of the 350 fields is defined to Lookup the corresponding field in the DataTable, via this relationship. If so, when a record is created, all 350 field should be populated with the corresponding data from the DataTable. If you already had records in the MirrorTable when the relationship was set up, these would not be updated unless you do one of two things: either (1) select content of the foreign key field, cut it and then paste it back—this will perform the Lookup again for that one record; or (2) click inside the foreign key field and perform a Relookup (Records menu)—this will update the looked up contents of all fields on all records in the current found set.

              • 4. Re: Lookups
                fmpdude

                I have written code lots of time using FM's JDBC driver to move data from a FM database to MySQL. That code even reads the FM metadata so it automates creating the table structure in MySQL too. The only issue I've faced is that the FM JDBC driver has a nasty bug with container fields (reported it months ago, but well, obviously a low/no priority at FMI). I have been able to work around this FM JDBC driver bug, but it takes a bit more code. If you don't have container fields the migration code is much easier.

                 

                The JDBC approach is also free and doesn't need any third party ($) ODBC drivers.

                 

                The high-level basic logic would be similar to this:

                 

                • Get a connection to FM database
                • Get a connection to MySQL database
                • Get a list of all tables in source database (loop through them)
                • For each table...
                  • Get metadata for that table
                  • Create table and fields in destination database, programmatically
                    • here you do any data type translations between FM and MySQL. You probably don't want to use "Text" fields in MySQL, for one example, since it has much finer-grained varchar type fields. Also be VERY careful using TIMESTAMP field types as in MySQL these fields only go from years 1970 to 2038. Better to use DATETIME fields if your dates are outside this range. Just write that mapping code in this step so when you create the field in MySQL it will be the right type.
                  • For each row in the source table INSERT that row to destination table.
                • Close connections, etc.

                 

                The JDBC code is EXTREMELY fast, too.

                 

                ---

                 

                Unless I missed it somehow, the FM JDBC driver doesn't give you a way to programmatically get all table names like you can do with MySQL in the code below. But FM does give you a way around that missing JDBC metadata functionality -- see below.

                 

                // MySQL Metadata code to programmatically get a list of all tables in the database and print them to the console:

                DatabaseMetaData md = conn.getMetaData();
                ResultSet rs = md.getTables(null, null, "%", null);
                while (rs.next()) {
                 
                System.out.println(rs.getString(3

                ));

                 

                So, the FM side of things is a bit more complicated, but not too much. In this case, just use the following FileMaker ExecuteSQL to get a list of all table names:

                 

                // How to get a list of all FM tables in the database:

                ExecuteSQL ( "SELECT * FROM FileMaker_Tables" ; "" ; ""  )

                 

                ----

                 

                So, the JDBC approach is a straightforward way to create GENERIC code that will copy any FM database to MySQL -- or to any destination database.  The only thing you would change from database to database is the Metadata field mapping logic to translate FM field types to the destination database's field types. Keep the database names in a properties file (name-value pairs text file) so that info is out of the main code. For each database type translation (say FM -> MySQL) the code is now generic. An OO enhancement would be to sub-class the database field mapping portion of the code for different destination databases like SQL Server or Oracle. Then, you would still run the "same code" but you would just create a different DB field mapping object polymorphically.

                 

                How long does it take? The first time I wrote this code, it took a few hours, but it was worth it. Imagining creating hundreds of fields manually in MySQL? That's what the DataBaseMetaData objects are for!

                 

                HOPE THIS HELPS.