4 Replies Latest reply on Mar 30, 2016 2:23 PM by chivalry

    Why is FileMaker crashing when executing a script that searches for related external data?

    chivalry

      I'm trying to build a script that will find certain records and FileMaker is crashing as soon as it tries to execute the script, i.e., before it even gets to the first script step.

       

      First of all, Jobs is a shadow table that exists in an MSSQL database that's being accessed using Actual Tech's ODBC plugin. CachedJobs is a local FileMaker table. There's a relationship from Jobs to CachedJobs using an unstirred calculated `hash` field in Jobs matched to an indexed `original_hash` field in CachedJobs. I want to find Jobs records that have no related CachedJobs records (indicating either that the job is new or that it's changed and needs to be updated).

       

      Here's the script:

       

      Go to Layout [ “JOB” (JOB) ]

      Enter Find Mode []

      Set Field [ Jobs_CachedJobs::uuid ; "=" ]

      Perform Find []

       

      Executing the above gives me the spinning beachball and I have for force FileMaker to quit. This happens even when the debugger is open. The spinning beachball happens before it even gets to the first script step. Note that I can perform this search manually without trouble (at the moment it returns all the Jobs records because they all need updating).

       

      If I change line three above to search for something in Jobs directly, such as Jobs::UniqueID, that works (in this case showing no records, or if I change the "=" to "*", it finds all records). But running the script crashes FileMaker every time.

       

      Is this a known bug of some sort with either FileMaker or Actual Tech's driver? Searching through a relationship from a shadow table to a local table in a script won't work? Am I doing something incorrectly, or should I just not expect this to work and I should find a different algorithm to solve the problem?

       

      Thanks,
      Chuck

        • 1. Re: Why is FileMaker crashing when executing a script that searches for related external data?
          Vaughan

          Try changing into find mode before going to the Job layout. This will stop FMP from trying to bring data down from the server to display the current record (or current found set in list view) while in browse mode.

          • 2. Re: Why is FileMaker crashing when executing a script that searches for related external data?
            chivalry

            Vaughan,

             

            I tried it and found no change in behavior.

             

            What I find really strange is that the app hang happens before it even gets to the first script step. While the debugger is open, the script steps never even display. There's no chance to even step over the `Enter Find Mode` or `Go to Layout` script steps. It seems to have a problem just loading the script for execution. I've never seen this, but this may be the first time I'm scripting a find across a shadow table into a relationship in a local table. At this point, I'm working around it with other techniques, but I'm wondering if this is a known problem, or if something else about what I'm doing here might be.

             

            Thanks,

            Chuck

            • 3. Re: Why is FileMaker crashing when executing a script that searches for related external data?
              ch0c0halic

              A hanging FMP is not necessarily a crashed FMP.

               

              If I may explain. FMP may become non-responsive because the ODBC driver is attempting to retrieve data. If FMP is trying to retrieve a large amount of ODBC data it may take a vey long time. That isn't a crash. Eventually it will probably respond. Looking at processor usage may show some minimal usage giving an indication that FMP isn't crashed.

               

              Our problem is not knowing if the processor is churning away with the ODBC driver and not allow FMP any access or if FMP is doing stuff but it can never finish it.

              If you were to wait a few minutes/hours/days FMP might actually continue. So that's just a very long response time.

               

              Look at the TOG for the FMP <-> ESS relationship. I think you'll see the connector show as  --|  on the ESS side. That indicates the ESS side is treated as an un-indexable field. If your using the ESS TO as a join table between for two FMP files it won't work and will cause the FMP requests to the ODBC driver to be undefinable. The gathering data process in FMP may be getting every record of the ESS for every record of the FMP table and then trying to get every record of the second FMP table for every record of the ESS table. Ugh, that's even hard to say let alone wrap my head around it.

               

              If the relational model results in this kind of recycling retrieval of the data it may be getting the same sets of data over and over. That is not a crash either (FMP or the ODBC driver is very busy). However it is an unrecoverable circular reference. If you can check the ODBC data source log for the FMP ODBC requests then that might also help indicate what is going on.

               

              I've seen ESS tables that cause FMP to become non-responsive. I recommend doing away with the FMP <-> ESS Table <-> FMP table use in the script. My experiences say that's where the issue is coming from.

              • 4. Re: Why is FileMaker crashing when executing a script that searches for related external data?
                chivalry

                I gave FileMaker about two hours at one point. At most it was retrieving 4,000 MSSQL records, so I would expect that would be sufficient time.

                 

                The relationship was from an ESS to a local table. The JOB layout above is looking at the ESS table. Jobs_Cached is a TO pointing to a local table.

                 

                At this point I'm working on a workaround that just does the search with local tables and imports (actually, creates records) from the external tables. I'm far enough along that route to continue and I think it'll work. FileMaker is sometimes annoying because it just works in so many cases that one often expects it to do so in edge cases and it doesn't, but it's pretty great because I've always been able to find some sort of workaround, as is the case this time.