6 Replies Latest reply on Mar 22, 2014 8:18 AM by info@creativesltns.com

    Filemaker ODBC 'live connection' limitations

    meatymeats

      Hello,

      I am currently trying to integrate QuickBooks via an ODBC driver called QODBC to a FileMaker solution which is hosting data such as inventory, and hoping some of you can help me out.

       

      What I am actually looking to do is share basic information between the systems such as inventory counts from FM, and Products Names and Numbers from QB, to avoid the costs, errors and stress caused by the double entry currently taking place.

       

      I have successfully configured the QODBC driver and am able to see the DSN in FM, but I am unable to actually create a linked table in FM using the DSN given by the driver. FM shoots an error message each time I try and add the linked table saying 'the database is not supported'. I have read all of the FM literature back and front and am completely aware that there is no 'support' for other ODBC drivers outside of SQL, Oracle, etc, etc, the 5 mentioned in the literature which I assume is likely the source of the error.

       

      I know that they are other solutions that already offer plugin type of functionalilty to get where I am trying to go, FMBOOKS being one of them, but I am convinced that there must be an easier route to get this basic step accomplished, outside of installing plugin's and a piles of programming. I figure if Access can open and maintain a live connection to the data published by the DSN from QODBC, making simultaneous changes as easy as editing a field, FM should be able to do the same thing.

       

      I am able to execute basic SQL commands through the import steps to import data from the host database into FM, and I know that this is techinically a half way solution, however I am worried going the 'non live' route is going to spell disaster later down the road when it comes to maintaining data pairity, and hence would like the change to happen at the same time across both systems if possible. Also I am not sure how writing data back to QB would work, as although the driver supports this function, I would not be to write data back to QB, short of completing import steps.

       

      Any further experience or insights you could shed on this?

       

      Any and all help would be greatly appreciated.

      Kind Regards

        • 1. Re: Filemaker ODBC 'live connection' limitations
          wimdecorte

          Nope, looks like you have experimented with pretty much all the functionality that FM has to offer.  You can not make a live connection to Quickbooks.  Only to the supported databases.

          1 of 1 people found this helpful
          • 2. Re: Filemaker ODBC 'live connection' limitations
            LSNOVER

            You can also try using the "Execute SQL" Script step (not to be confused with the ExecuteSQL function).    It does not have much in the way of a mechanism for returning data.  You can use the Error mechansim to do some limited data retrieval. 

             

            ESS only supports Oracle, SQL Server and MySQL.  Everything else will have to run through the import export mechanism.

             

            One option that is more work, but may get you closer, is to use the free version of Oracle or SQL Server (I say those because they have excellent stored procedure support).   Use one of these DBs as a intermediary.  You can write stored procedure scripts to poll the data from your QBDC connection and then pull the data into one of these supported databases and use those tables within Filemaker.   Not quite live, but it's a possibility.

             

            Another solution would be to use a product called Pentaho Kettle which can connect to Filemaker and other ODBC datasources, and use scheduled scripts to poll and transfer data between Quickbooks and Filemaker.  This is also a free product and available on both Mac and Windows.  It's not hard to setup and learn and is very visual.  I've also found that it interacts with Filemaker ODBC pretty well.

             

            Regards,

            Lee Snover

            1 of 1 people found this helpful
            • 3. Re: Filemaker ODBC 'live connection' limitations
              PSI

              i have used fmbooks a number of times and it is pretty easy to setup and program.

               

              it sounds to me that if you used it you would be done already?

               

              John morina

              PSI, Inc

              • 4. Re: Filemaker ODBC 'live connection' limitations
                LSNOVER

                That too. lol.

                • 5. Re: Filemaker ODBC 'live connection' limitations
                  meatymeats

                  Hello All,

                  Firstly thank you all for your responses, they are much appreciated.

                   

                  @ PSI It is comments like yours that lead conversations astray in forums. Although I appreciate you taking the time to provide input, it is usually best to stick to the question posed rather than providing your 'opinion'.

                   

                  @ LeeSnover; wimdecorte, thank you very much for your prompt, detailed, and accurate responses.

                   

                  For the sake of closure of this topic in the forum, and for anyone searching or following, we just wanted to update everyone on the solution in place. Aside from the scripting, FMBOOKS was not an option in our environment due to the fact that the user executing any scripts, to read, modify, or write data, would have to have software packages on the same machine, and thus would be unusable in a multi user environment. To be clear, using FMBOOKS it is impossible to point a script at a QB instance on another networked machine, or QBfile, which completely negated any benefit the package offered outside of a single user on a single machine with both software packages installed. The way FMBOOKS gets around this limitation is by creating server side, end of day, or end of week, batch scripts which write any new data to the QB database in bulk using loops etc.Ths solution posed even more data pairity concerns and thus was abandonded

                   

                  As we were looking for bi directional, full access connections between the datastores, we explored various options such as using other database structures as middleware, swithcing accounting softwares, and migrating the database to MySQL, Access, or Oracle or other more external 'connection friendly' databases.

                   

                  After the dust settled, and options weighed, we decided that it was best to try and work with direct queries to the QB database using SQL and QODBC, while making QB the home source for any critical data. We also setup a structure to mitigate data parity issues by corresponding any data moved from QB to FM via uniqueID's provided in the QB lists, and not allowing duplicates or deletion of such entries, by using inactive flags only. Therefore, esentially any data that is created in QB, should technically never be deleted, and any data brought to FM via QB should never be deleted, only updated. As time moves forward we are exploring options that will allow users to execute scripts across the network that will refresh data on another machine on the network (have QB, FM, and Users all be on different machines, or networks), and given the product documentation there are good options for this.

                   

                  Thus far the solution seems to be working, however it will remain to be seen how it holds up moving forwards. To be clear we were unable to find an acceptable solution in a multi user environment that would allow us to create and modify content in both software systems.

                   

                  any further info or input where pertinent is much welcomed.

                   

                  Well Wishes,

                  meatymeats

                  • 6. Re: Filemaker ODBC 'live connection' limitations
                    info@creativesltns.com

                    When you say FMBOOKS, are you talking about the FMBooks Connector plug-in or something else?

                     

                    I work extensively with clients that want to integrate FileMaker and QuickBooks, and often use the plug-in in environments where QB is "hosted" on one computer for use in multi-user mode on that and/or other computers. While there are a couple limitations on what can be done in multi-user mode, none of them appear to be relevant to your use-case.

                     

                    It's true that you need a Windows OS computer running both FMP and QB, but the QB Company file can reside on that or any other networked Windows OS computer that has been configured to share that file. The hosted QB file can be launched on any Windows OS computer with QB installed via the normal UI, or in the background using the plug-in if the file is on a mapped volume. I normally recommend users launch the file manually so that the UI is available, and then connect to it from FMP as needed.

                     

                    The FMBooks Connector plug-in does not run server side, but it can be used in batch scripts to do all the data push/pull tasks necessary to keep the two systems in synch with each other. If, after initial synchronization, you wanted to keep the systems synched on a minute by minute basis, you could easily setup a "robot machine" that would run your synch scripts on a timer. the plug-in is very fast, and it would be difficult to imagine a scenario where users could be making so many changes that it would take longer than a minute to complete the process. While this is not "Live Synch", it's been close enough for the handful of clients that wanted that feature.

                     

                    HTH!

                     

                    Geoffrey Gerhard

                    Creative Solutions Incorporated