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?
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.
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.
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.
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.
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.
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?
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.