3 Replies Latest reply on Jul 16, 2015 2:39 PM by GwRodriguez

    Filtering an unrelated table



      Filtering an unrelated table


      Here's the cartesian relationship in question:


      What I would like to do is have a portal in Shows that displays all records of the table Mixers.  This part is simple, I created a second table occurrence (Mixers2), and made a cartesian relationship.  The portal shows all the records no problem.  

      The tricky part is I now want to filter it dynamically.  I tried creating a new field in Shows called gShowFilter and adding to the cartesian relationship gShowsFilter=Manufacture.  But the filter doesn't work, meaning that the portal becomes empty.


        • 1. Re: Filtering an unrelated table

          I just figured it out!  I'll post here for others that might need this info.

          I left the cartesian relationship alone (Shows>---Mixers2).  In the portal I chose Filter Portal Records I added the calculation: Mixers 2::Manufacturer = Shows::gShowFilterMixer.  Then in the field on the layout, I changed it to a popup that included all the different manufacturers, and added a script trigger to Refresh Window via the OnObjectModify.


          • 2. Re: Filtering an unrelated table

            I suspect that your Refresh Window requires the "flush cached join results" option. This can, as numbers of records increase, result in long delays waiting for the window to fully redraw. This is an option that can easily be avoided.

            With the method that you are using, it would be simpler to just use gShowFilter as a match field in the relationship using the = operator and not use any filter at all. The script then would either not be needed or would only need a commit records. Also, portal filter expressions that refer to nonglobal fields update more readily than those that refer to global fields--all that's needed is a commit records. There are also ways to "bump" a global field via a script in such a set up provided that you set it up as the match field in your Cartesian Join relationship.

            You might be interested in the selection portal methods demonstrated in the following file. They demonstrate such options as setting up partial text matching so that you can enter search text and see the list of matching records update with each keystroke:

            "Adventures in FileMaking #2 - Enhanced Value Selection"

            • 3. Re: Filtering an unrelated table


              Oh wow!  That's so much easier.  I assumed that I had to include the cartesian relationship first.  Thanks so much!