3 Replies Latest reply on Dec 21, 2014 3:19 PM by smith7180

    Cartesian Joins (for mitigating window refreshes) not working on Hosted database

    smith7180

      Hi-

       

      I use the method outlined here and other places to avoid the costly refresh window flush cache step:

      Ditch those Flush Caches, Use Cartesian Join Instead!

       

      However, after uploading my database to a new web host, any relationship including a cartesian join of two globals breaks.  Portal are empty, gttr steps don't work.  In all cases, if I simply delete the global x global part of the relationship- everything works perfectly.  Furthermore, on my desktop in FMP advanced, everything works perfectly.

       

      Any ideas? 

       

      Thanks!

        • 1. Re: Cartesian Joins (for mitigating window refreshes) not working on Hosted database
          wimdecorte

          Can you elaborate a little more: when you say "web host" do you mean that the database is accessed through the web (html pages), or just that it is hosted in the cloud and people access it with FMP?

           

          If the latter, it is probably just an issue of setting the global.  If you have the database not hosted and just on your machine, the globals will survive between sessions (opening and closing the file).  But on FMS, globals are unique to the user.

          • 2. Re: Cartesian Joins (for mitigating window refreshes) not working on Hosted database
            smith7180

            Thanks Wim.  This database is hosted in the cloud and people access it with FMP.

             

            The globals are cleared (ie global = "") on first window open.  Let's say I have two tables- Table_A and Table_B.  In each table I have a global field called _Refresh.  These are purely utility fields for refreshing relationships that would otherwise require a Refresh Window [Flush cached join results] script step to update portals based on that relationship.  I discovered this method on the Weetbicks blog, and have since seen it mentioned other places.

             

            If I removed the Table_A x Table_B relationship, everything is fixed.  That's particularly surprised to me since I would think a cartesian join would yeild every product.  My first instinct was to wonder whether the globals being empty had anything to do with it.  But it works exactly as inteded on the desktop?  Mostly I'm wondering if I'm not understanding some aspect of Filemaker Server.

             

            Any help is much appreciated!

            • 3. Re: Cartesian Joins (for mitigating window refreshes) not working on Hosted database
              smith7180

              Finally found the answer to this.  Lo and behold- it was on the very page where I first discovered this method in a comment by Jason DeLooze:

               

              "I've been investigating how FMP resolves the various predicate-types as well as how multi-predicate joins are resolved from these predicate resolutions. In doing so, I have discovered what I think are some bugs (or at least bad or unexpected behavior). For example, a Cartesian predicate should always return all records in the target table. When a Cartesian predicate is used in a multi-predicate join, the RHS target match field is indexed, and the LHS target field contains any value, FMP walks the value index tree to form the set of matching records. However, if the LHS target field is [sic- there is a word missing here, and I believe Mr. Delooze meant "empty"], the Cartesian predicate returns an empty set, which, when intersected with the results from the other predicates, causes the Join to have no matching records. This is simply incorrect behavior."

              From: Ditch those Flush Caches, Use Cartesian Join Instead!

               

              That indeed is the problem.  I clear my globals on start up.  As soon I input a value for the LHS global- the relationship works as intended.  Though I'm not a filemaker expert, based on their definition of the 'cartesian product relationship' and the more common idea of 'cartesian product' I would agree with Mr. DeLooze's comment the this is 'incorrect behavior'.  Filemaker's defitnition:


              The Cartesian Product describes a relationship where any record in one table occurrence will match all the records in another table occurrence....Note: you are not limited to primary keys.  You can use any two fields (except Container and Summary field types) and use the X symbol.

               

               

              The only mystery to me is why this is not a problem on my local machine- only once I host it.