14 Replies Latest reply on Jul 11, 2017 9:33 AM by TSGal

    ODBC issue with FMPA16/Mac

    neufere

      On the first day of using FMPA16.0.1.162/Mac with a file hosted on FMS 15.0.3.308/Windows with ODBC connections to an MS SQL database, I experienced erratic behavior with a relationship (which I have never experienced with FMPA 14 or 15).

       

      An initial investigation of the script step Set Variable ($countRel; Value: Count(EA7_RELATIONSHIPS::PARENTID) whose correct value, in a particular instance, should have been 3 (i.e. 3 related records), was 0. However, running the script again in the same exact situation, did render 3. In other words, it gave different values at different times. The step is part of a script that obtains (i.e. updates) record IDs from the MS SQL data and the erratic behavior left many of them blank/undefined.

        • 1. Re: ODBC issue with FMPA16/Mac
          TSGal

          neufere:

           

          Thank you for your post.

           

          Please post the five script steps that execute prior to the Set Variable script step.  Since the correct count is captured the second time, this issue could be related to timing.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: ODBC issue with FMPA16/Mac
            neufere

            All the prior (14) script steps are simple Set Field commands to clear fields in the FM database that will store the MS SQL record IDs. The 5 prior steps:

            Set Field[ IDP::Address_P2b; ""]

            Set Field[ IDP::Address_P1a_Business; ""]

            Set Field[ IDP::Address_P1b_Business; ""]

            Set Field[ IDP::Address_Pa_Business; ""]

            Set Field[ IDP::Address_P2b_Business; ""]

            • 3. Re: ODBC issue with FMPA16/Mac
              TSGal

              neufere:

               

              Is the COUNT of the field the first reference to the Microsoft SQL database?

               

              Have two instances of the COUNT.  First, add the count prior to the 14 Set Variable script steps, and then the COUNT again.  This may give FileMaker Pro time to initially access the database.  Does this work?

               

              TSGal

              FileMaker, Inc.

              • 4. Re: ODBC issue with FMPA16/Mac
                neufere

                I am now unable to reproduce the issue with COUNT.  I have made a duplicate copy of the database to use solely with version 16. The next time I need to use it (within the next week), I will do the operations in both copies to see if there are any discrepancies. I'll let you know how it turns out.

                • 5. Re: ODBC issue with FMPA16/Mac
                  neufere

                  Did some more work with it this morning and can reproduce the problem.  I tried your suggestion and added the additional COUNT before the 14 steps.  Both return 0 rather than the correct amount. I had the original running in v15 and the copy running in 1v6 and repeatedly ran them getting the correct value in v15 and 0 in v16.

                  • 6. Re: ODBC issue with FMPA16/Mac
                    neufere

                    More... I tried the process with a different record where the COUNT should be 5 (i.e. 5 related records) but it consistently comes up as 2 (rather than 0 like the others). As I go through various records, some return 0 and some return 2 (in all cases, these are incorrect values).

                    • 7. Re: ODBC issue with FMPA16/Mac
                      neufere

                      This may have something to do with it. In the relationship graph, I see some flat bar connections (from FM documentation: Indicates that the join cannot be made. For example, a match field is missing or is an unindexed field (such as an unstored calculated field or a global field), or the table closest to the indicator is missing or unavailable.) Could it be that v14 and v15 are more forgiving of these connections than v16? The script has always worked prior to v16.

                      • 8. Re: ODBC issue with FMPA16/Mac
                        TSGal

                        neufere:

                         

                        Thank you for the additional information.

                         

                        Does the Relationship graph appear the same in FileMaker Pro 16 with FileMaker Pro 15?  Compare the two, as this may provide a clue.

                         

                        It's interesting that you mention the second time it works in FileMaker Pro 16.  Perhaps look at the Relationship graph immediately upon opening, and then look at it again after running the script a second time when it works.  This may also provide a clue.

                         

                        I would also try having an OnFirstWindowOpen script trigger that saves the Count to a variable.  This may give the database time to connect to Microsoft SQL Server.  Then, when you run the script the first time, the count may have the correct value.

                         

                        TSGal

                        FileMaker, Inc.

                        • 9. Re: ODBC issue with FMPA16/Mac
                          neufere

                          The relationship graphs appear the same in both.

                           

                          As I dig into this more, it gets stranger. (Unfortunately, I don't have a lot of time for this.) The issue appears record dependent and is consistently not working on some records (0 or 2 being returned time and again) and consistently working correctly on other records. But what's truly bizarre is that local field values from the main database displayed in the layout disappear and then come back when I close and re-open the file.  At this point, I think it's best for us to wait on v16 until some updates are made. And to top it off, I did a simple find that should have returned no records but it returned one record with data showing from two different records.  (Closing the file and doing the find again worked correctly.) Many thanks for your time and assistance.

                          • 10. Re: ODBC issue with FMPA16/Mac
                            TSGal

                            neufere:

                             

                            What is the driver being used to connect to SQL Server?

                             

                            If the COUNT is correct in 15 but not in 16, this won't be fixed unless there is more information.

                             

                            Do you have a file that we can use to determine why it is failing?  If so, send it in so our Development and Testing teams can determine the cause.  I have sent you a private message with instructions where to send the file.

                             

                            TSGal

                            FileMaker, Inc.

                            • 11. Re: ODBC issue with FMPA16/Mac
                              neufere

                              For the driver, we are using Microsoft's SQL Server (version 6.01.7601.17514).

                               

                              Unfortunately, the file only works within our firewall (since it connects to our student information system).

                              • 12. Re: ODBC issue with FMPA16/Mac
                                TSGal

                                neufere:

                                 

                                This issue has been addressed in FileMaker Pro 16.0.2.

                                 

                                TSGal

                                FileMaker, Inc.

                                • 13. Re: ODBC issue with FMPA16/Mac
                                  igattengo

                                  TSGal,

                                   

                                  Can you provide more details on what specifically the issue was in 16.0.1 and what was addressed in 16.0.2? We've experienced a similar situation running FMPA 16.0.1 on Windows, where searching records on an ESS table/layout was not producing the correct results compared to v15.0.3.

                                  • 14. Re: ODBC issue with FMPA16/Mac
                                    TSGal

                                    igattengo:

                                     

                                    There was a caching issue of ESS data in FileMaker Pro 16.0.1, so the wrong information may have been displayed.

                                     

                                    TSGal

                                    FileMaker, Inc.

                                    2 of 2 people found this helpful