3 Replies Latest reply on Aug 16, 2013 1:05 PM by philmodjunk

    Filtering Portal Records in Real Time

    Atkins

      Title

      Filtering Portal Records in Real Time

      Post

           Hello,

           I'm attaching a screenshot of my layout because it will help you get a feel for what I'm trying to achieve.  At the top of this layout I have two drop-downs.  Both are global fields that are essentially variables, and contain the entire alphabet and all the numbers (per a defined value list).  Below this there is a portal linked to the table of records I'd like to view.

           Here's what I'm trying to achieve.  I'd love for the user to select the first letter of the model number (in the first drop-down) and the portal to filter and show only the records who's "Model" field starts with that letter.  The same for Manufacturer, although right now I'm just trying to get Model to work.

           Here's the script I'm using in the "Filter" specification box for the Portal:

           

                If ( ( Left ( Equipment Lookups::Model ; 1 ) = Temp::g_Alpha ) ; 1 ; 0 )

           I've tried essentially this same scipt using the Case command as well.  I've gotten some different results but nothing that's completely working or correct.  What am I doing wrong?  Is it possible for the portal to filter records in real-time, so that each time an option is selected it updates itself?  If it can't be done in the Portal, is it possible to do just using a script triggered 'On Modify'?

           I guess I envisioned this being a quick lookup function.  You select the first letter of the model name and the portal shows only the records that start with that letter.  You could then select the first letter of the manufacturer's name and the portal would constrain that filtered "found" set even further showing only the records that match both criteria.  If that second part isn't possible then I'd be ok with just the Model part working.

           Let me know what advice you have.  Thanks for the help!

            

      sc-1.jpg

        • 1. Re: Filtering Portal Records in Real Time
          philmodjunk

               After selecting the value in the value list. Try running this one line script:

               Refresh Window [Flush Cached Join Results]

               If that successfully updates your portal, then you have most everything set up correctly and you just need to address the update problem.

               You could set up the drop down list with a script trigger to perform this script, but this step can produce major screen update delays in some circumstances and there's an approach that eliminates the need for a script and script trigger:

               I would guess that you have a relationship similar to this:

               Temp::SomeFIeld x Equipment Lookups::SomeFIeld

               That will list all records from Equipment Lookups until you add a portal filter to reduce the list shown.

               If you modify your relationship to be:

               Temp::g_Alpha x Equipment Lookups::SomeField

               The portal should automatically update when you select or enter a different value in g_Alpha. You may need to click the layout background after selecting a letter in order to commit the layout's record before you see the update. And you can include both global drop down fields in this same relationship:

               Temp::g_Alpha x Equipment Lookups::SomeField AND
               Temp::g_Alpha2 x Equipment Lookups::SomeFIeld

               You may find the search portals in this demo file of interest. They allow you to type text into a search field and the portals update with each keystroke to list fewer and fewer matching records. The portal rows themselves have been set up as buttons so that clicking a field in the portal row performs a script that uses the data in that portal row: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               If you are using FileMaker 12, open this file from the File menu to get a copy converted to the FMP12 file format.

          • 2. Re: Filtering Portal Records in Real Time
            Atkins

                 Ok, I achieved what I was looking for with the relationship you specified and the following filter code for the portal:

                  

                      
                           ( If ( ( Left ( Equipment Lookups::Model ; 1 ) = Temp::g_Alpha ) ; 1 ; 0 ) and If ( ( Left ( Equipment Lookups::Manufacturer ; 1 ) = Temp::g_Alpha2 ) ; 1 ; 0 ) ) 
                      
                            
                      
                           or 
                      
                            
                      
                           ( If ( Temp::g_Alpha = "" ; 1 ; 0 ) and If ( ( Left ( Equipment Lookups::Manufacturer ; 1 ) = Temp::g_Alpha2 ) ; 1 ; 0 ) )
                      
                            
                      
                           or
                      
                            
                      
                           ( If ( ( Left ( Equipment Lookups::Model ; 1 ) = Temp::g_Alpha ) ; 1 ; 0 ) and If ( Temp::g_Alpha2 = "" ; 1 ; 0 ) )
                 
                      Thanks for the help!  Just curious, what's the difference here in using 'x' in the relationship vs. using '='?
                       

                  

            • 3. Re: Filtering Portal Records in Real Time
              philmodjunk

                   X is the cartesian join operator. It matches any value in the match field in one table to all values in the other table. Thus, it enables you to display all records in your portal no matter what values are specified for the match fields. In fact, you can delete the match fields from your table and the relationship will still function.

                   But what we are exploiting here is some fairly undocumented behavior that the cartesian join automatically forces FileMaker to update the layout when a value in one of the match fields is changed. Thus, the value in the field does not affect what records match via the relationship, but the "save" event (as in OnObjectSave...) tripped by the editing of a match field in this relationship kicks off the needed update without scripting.