7 Replies Latest reply on Apr 17, 2017 1:43 PM by TKnTexas55

    ODBC Connection to Great Plains SQL Data

    TKnTexas55

      I have a number of solutions, working around the deficiencies of our accounting software-Microsoft Great Plains (v2010).  I have been using a number of GP-SmartLists to extract data and upload to my Filemaker files. After a presentation at a local FMPUG, I got authorization to use ODBC to connect. 

       

       

      I did so, with the assistance of our Great Plains Administrator.  He uses Crystal Reports to connect to the files.  When we connected, we did not see the expected data tables.  We saw "something" but they were not data tables.  Has anyone else used FileMaker Pro to connect to Great Plains?  I have the ODBC 64bit driver on the System Tab

       

      The GP Admin inserted his User ID and Password for the connection.  I am just sure where to go from here.  My Great Plains VAR says "I don't know FileMaker". 

        • 1. Re: ODBC Connection to Great Plains SQL Data
          TKnTexas55

          As a follow up, I am still fumbling with this.  I created a file, with NO FMP tables.  I just wanted the table occurrences of the Great Plains (MS-SQL) tables.  I have a list of what those tables are, do I need to create a "shell" of these to populate?

          • 2. Re: ODBC Connection to Great Plains SQL Data
            user19752

            If you use FMPro 64bit (or FMServer) DSN should be 64bit, otherwise 32bit.

             

            You need defining external data source in FM, then create table occurrences in relation graph with it.

            • 3. Re: ODBC Connection to Great Plains SQL Data
              TKnTexas55

              I have 64bit installed in my System DNS, as I was told FileMaker is 64bit (at least on my Win10).  When I open up the Relationship Tab, to create the table occurrences I need, I am not seeing data tables.  What I am seeing seem to be system usage tables.

              • 4. Re: ODBC Connection to Great Plains SQL Data
                eworbit

                You probably haven't been supplied the correct Username/Password combo.   You should use a independent tool such as Sql Exporer or Navicat and connect to the sql server using that username/password combo.  This way you're using another client software to list out the views/tables available to you.   This eliminates finger pointing when the GP admin doesn't give you the right credentials.  

                -eric

                BTW, you probably want to make sure you have a read-only credential for all your data except for the few writable tables the GP may allow you to write to.

                • 5. Re: ODBC Connection to Great Plains SQL Data
                  TKnTexas55

                  I am still in a testing "phase".  The GP Admin thought my regular credentials were sufficient for me.  That did not work at all.  So he entered the credentials he uses on Crystal Reports to do reporting from Great Plains.

                   

                  Currently in FileMaker I have about a dozen tables of Great Plains data that I am loading manually a couple times a week, this is just payable related transactions.  With an ODBC connection, the new records in Great Plains would just be in the table occurrences.  There is one update that would still need to be done manually, because GP does not store the needed info to make the relationship.  It is the reason I use FileMaker to supplement my work, the deficiencies of "Great Pains".

                   

                  I am appreciative of any assistance to trouble shoot this.

                  • 6. Re: ODBC Connection to Great Plains SQL Data
                    TKnTexas55

                    I have made progress.  With the GP Administrator, I asked if he had "pointed" me to the correct file location or if they were in a lower level folder.  He opened Crystal Reports, his go to program.  When he started a new report, what he was seeing was exactly what I was seeing.  So after a little discussion, we fixed FileMaker Pro to see the files correctly. 

                     

                    Now I am learning how to organize these various tables to get meaningful reporting.  In Accounts Payable, there is a table of OPEN Transactions.  These are posted invoices and credit memos pending the writing of the disbursement check. 

                     

                     

                    Today was my check-writing day.  I looked at the table (via FileMaker Pro 15). There were 246 transactions.  I wrote checks.  I am still seeing 246 transactions.  Is there a trick to updating or refreshing my view via ODBC? 

                    • 7. Re: ODBC Connection to Great Plains SQL Data
                      TKnTexas55

                      I found the SYNC button.  I was surprised that it is on the Manage Database screens.  When I have this ready for the average user, I am not sure I want them 'tinkering' in Manage Databases.

                       

                      Now I have to find the ways to make this replace my Old Ways.