10 Replies Latest reply on Oct 5, 2015 8:37 AM by KathleenBrown

    ODBC connections to multiple files

    KathleenBrown

      We have a FMP solution with a table built just for an ODBC connection.  It has a primary key field and all other fields are calculations pulling data from various tables and in the case of a couple fields, from another table that is in another physical file.  Do we need to create a DNS to the other file if we're not accessing it directly, only 'indirectly' thru the calculated fields?  We're having trouble with 2 fields returning NULL values.  The priv. set is defined as View only in all tables.  Thx in advance for any addl. info.

        • 1. Re: ODBC connections to multiple files
          Mike_Mitchell

          Hello, Kathleen.

           

          Your setup is a little confusing, and we don't really have enough information to give you a good answer. When you say your calculations are pulling data from other tables, are these FileMaker tables or ESS (external ODBC) tables? I'm going to assume, based on the nature of your question, that they're ESS tables. With that assumption, a couple of things to check:

           

          1) Make sure the privileges in both files are correct (not just the current file).

          2) Make sure the account you're using to access the external source has a proper role to access all the columns (fields) in the external table. (This one is more likely, given you're seeing only a couple of columns apparently missing.)

           

          I doubt the DNS is the issue. If the DNS were the issue, you'd be getting a prompt for connection and you'd be denied permission to log in.

           

          If these couple of items don't solve the problem, let us know and provide some more detail. We might be able to drill down a bit more.

           

          Mike

          • 2. Re: ODBC connections to multiple files
            KathleenBrown

            Sorry for not providing more detail in my original post.

            The table I mentioned is a FMP table that the SQL server can reach into and grab the fields they want with the names they'd like without knowing the correct Table Occurrence & field names.

            So the calculated fields are all FMP fields and they are visible when in FMP (they are populated).

            We are making the connection to FMP data from the ODBC side.

             

            I will check all privileges carefully today in all the FMP tables in both files.

            We (should) have the account setup as Read only in all tables with the ODBC extended priv. checked in both files.

            Good info that if DNS was the issue it would most likely give us a connection message.

            I will report back.

             

            We are running FMP v14 server and the Database server is running Windows 2012. SQL Server is 2012.

            thank you for the reply.

            -k

            • 3. Re: ODBC connections to multiple files
              Mike_Mitchell

              Okay, so you're connecting to FileMaker from an external source. In that case, yes, you will need a DNS for each FileMaker file. (Thought you were coming the other direction.)

              • 4. Re: ODBC connections to multiple files
                beverly

                EVERY table that is used by FMP/FMS (including external to the file/db being accessed), MUST have an alias/table occurance (T.O.) ON THE RELATIONSHIP GRAPH (RG). No exceptions! This include using ESS, ODBC with Import or Execute SQL script steps, with ExecuteSQL() function or any functions calling the T.O. in a calculation. In order for the T.O, to appear on the RG (for External SQL tables especially), an ODBC DNS (must be set up with necessary permissions to at least read only) for the table.

                 

                The T,O., of course, can be renamed on the RG, as it really is an alias to the  Base Table being used. But that's your option only and not required. To avoid confusion I try not to rename External SQL T.O.s unless the name conflicts with a FMP table name.

                 

                Beverly

                • 5. Re: ODBC connections to multiple files
                  KathleenBrown

                  Making a little progress in trouble-shooting, but no resolution.  This is

                  what is happening.  The 2 fields that were not being extracted from SQL

                  (only as NULL) were fields in a specialized FMP TO that uses a global field

                  to flag which row in the related data (one to many) is the primary row.

                  Once I changed the field's calculation definition (for testing only) NOT to

                  look for the flagged row, and just reach in and grab the first related

                  record, the data was extracted/seen from SQL properly.

                   

                  NOT WORKING:  the calculation set = Facility_Multiples_Primary::fk_LOC

                   

                  WORKING:  the calc set =  Facility_Multiples::fk_LOC

                   

                  So is this perhaps a bug that a TO built in FMP testing 2 fields for a

                  valid relationship is not supported?

                   

                  The 2nd field that isn't working also uses a specialized

                  TO,Facility_Multiples_notPrimary::fk_LOC.  But once I get the answer the

                  above, that will probably also answer the 2nd.

                   

                  As always, thx in advance for additional insights.  Utilizing ODBC

                  connections is all new to me and I'm working with others on the SQL side of

                  things.

                  -kathy

                  • 6. Re: ODBC connections to multiple files
                    Mike_Mitchell

                    KathleenBrown wrote:

                     

                    So is this perhaps a bug that a TO built in FMP testing 2 fields for a

                    valid relationship is not supported?

                     

                     

                    No.

                     

                    KathleenBrown wrote:

                     

                    The 2 fields that were not being extracted from SQL

                    (only as NULL) were fields in a specialized FMP TO that uses a global field

                    to flag which row in the related data (one to many) is the primary row.

                     

                    This.

                     

                    The ODBC connection has no basis or context for evaluating a global field. So can't evaluate the relationship.

                     

                    Multi-predicate joins are fine. But you'll have to come up with another solution for the global join for this to work. How is the global normally set? By the user? By a script?

                    • 7. Re: ODBC connections to multiple files
                      KathleenBrown

                      In the primary table the global field is a calculation = "yes", result = text.

                      In the related table the record is joined by pk_id and a text field that is either blank (not primary) or = "yes" indicating it is the primary related record.

                       

                      So perhaps in the related table I should create an additional calculated field, e.g. if primary flag = "yes", populate another field and we extract on that field.

                      Or what's another, solution without the overhead of building additional fields replicating values?

                      -kathy

                      • 8. Re: ODBC connections to multiple files
                        Mike_Mitchell

                        So it’s a global calculation field, then?

                         

                        No, you don’t need to create a new field in the child table. Instead, change the calculation field in the parent table to a regular (non-global) calculation field. This will cause each record to have its own key, which should resolve the relationship properly.

                         

                        Global calculations have some strange behavior when used as relational keys. I wouldn’t rely on them for that purpose.

                        • 9. Re: ODBC connections to multiple files
                          KathleenBrown

                          understood.

                          I'm glad I included 'my plan' for input on that too.

                          I won't be in to the office again until next week, but will try it then and report back.

                          again thx,

                          -kathy

                          • 10. Re: ODBC connections to multiple files
                            KathleenBrown

                            Changing the 2nd match field in the relationship from a global to a 'straight' text calculation fixed the problem of NULL values being returned in the SQL query to Filemaker data.

                             

                            we are all set now and sincerely appreciate the help here in this discussion group.

                            I would have been looking on the SQL side for the issue since all other fields from the FMP table were visible.

                             

                            regards, kathy