3 Replies Latest reply on Mar 31, 2015 9:13 AM by philmodjunk

    Incremental synchronisation from an External SQL Database

    ChrisC_3

      Title

      Incremental synchronisation from an External SQL Database

      Post

      Is Filemaker's import mechanism equipped for incremental synchronisation (only append new records without duplicating existing records) of data from external SQL data sources or will it require some fiddling? If so, what steps would I need to take to make this happen, while keeping in mind that I only have limited access to the SQL datasource and can't directly modify its structure.

        • 1. Re: Incremental synchronisation from an External SQL Database
          philmodjunk

          Sort of.

          There's an Import Matching option that can include an option to add new records that do not match, but this updates the existing records so if you've changed data after the import, that data would get "rolled back" by the next import.

          On the other hand, you can do a two stage import. Import the data into a "staging table" that uses a relationship to match an ID field in the staging table to your table that serves as the final destination of your import. You'd then import all data into the staging table, perform a find to create a found set of all records in the staging table that do not match to an existing record in your destination table and then import this found set of records into your destinations table. This would not be the most practical approach if your SQL data source has say 20 million records and you only need the 10,000 or so new records imported into your table wink

          But since this is an SQL data source, you might be able to set up some kind of ODBC import--which can be based on an SQL query of your data source to import only new records...

          • 2. Re: Incremental synchronisation from an External SQL Database
            ChrisC_3

            Thanks Phil.

            Hmm, I'm going to have to experiment with ODBC a bit.

            Assuming I'm going to be able to query the ODBC datasource with an import script from my Filemaker server based solution I should be able to work out the rest. The problem is that while I am a Filemaker developer, I'm not the administrator of either the SQL datasource or the FM Server and that ties my hands a little with what I can do. Therefore, the ideal solution to this problem would have to be based entirely inside my Filemaker Solution which I have full control over. Otherwise I'm looking at involving both server administrators in the development process which would slow me down and be a hassle for everyone.

            • 3. Re: Incremental synchronisation from an External SQL Database
              philmodjunk

              You might find this document on FileMaker SQL helpful: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

              There's also one on ODBC/JDBC that you might find useful. I don't have a link for it but it's accessible via the Product Documentation sub menu of your FileMaker help menu.