10 Replies Latest reply on Nov 15, 2014 8:59 AM by DanielWheelon

    Filtered Portals that update without Refresh Window

    philmodjunk

      Title

      Filtered Portals that update without Refresh Window

      Post

      I've been sharing a demo file that illustrates auto-complete enabled drop down value lists that enter ID numbers and filtered search portals that update keystroke by keystroke.

      The portals have had a key flaw that limited their usefulness as first pointed out to me by LaRetta: Refresh Window [Flush Cached Join Results] can greatly slow down system performance--especially with Go clients and WANs as it does far more than just refresh the current window. Unfortunately, I didn't know of a way around that limitation that still maintained the keystroke by keystroke responsiveness.

      Just today, a post by Mark Gores generously shared a method that enables a filtered portal to update when a field referenced in the filter is modified without needing to use the Refresh/Flush. The method uses a cartesion self join separate from the portal relationship to force a the portal to update. Not only does this work, it eliminates the annoying window "flash" produced by the Refresh Window step.

      I have now implemented his suggestion in the demo file and updated the shared copy.

      Here's the link to the updated demo file for those who are interested: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

        • 1. Re: Filtered Portals that update without Refresh Window
          mgores

          I can't take complete credit.  The basis method is described here

          http://www.digitalfusion.co.nz/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/

          I implemented it on a "filter as you type" portal and fine tuned it with some suggestions from Phil in that post over in the GO forum.  With the new tweaks to the script there is no screen flicker at all and now bouncing keyboard on the iPad.

          • 2. Re: Filtered Portals that update without Refresh Window
            krancmm_1

            Thanks so much Phil... I've used your Enhanced Value Selection demo, both for script templates and picking apart the logic behind the different methods, but always shied away from any method requiring a refresh.  The "flash" is more like a "goose" on my PC.

            For anyone interested in a really detailed explanation of using Cartesian joins to avoid refreshes, there was an excellent article from Daniel Wood on his Filemaker Weetbicks blog site.  Especially interesting in the comments section of the blog was Jason L. Delooze's research on how to speed up Cartesian joins by using globals on "both" sides of the relationship:

            http://www.digitalfusion.co.nz/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/ 

            Monica

            • 3. Re: Filtered Portals that update without Refresh Window
              mgores

              Monica,

              I hadn't noticed the comment section, but by dumb luck I did set up that cartesan join between global fields.  While trying to eliminate the onscreen keyboard issue, we dicovered that using Set Fields to self for each of the fields worked to refresh the relationships with no flickering at all and only requires 2 script steps for the OnModify trigger of the search field.

              Set Field [source::refresh ; source::refresh]

              Set Field [main::search ; main::search]

               

              main::search is the global field where you type to filter the portal

              source::refresh is a global set up in the source file (like a contacts DB) for this purpose.  I added cartesan join between these two fields to the relationship for the portal

              • 4. Re: Filtered Portals that update without Refresh Window
                philmodjunk

                I'm familiar with the article, but since the filtered portal already used a cartesian join, I hadn't figured out how this specfic use of Refresh/Flush could be eliminated. The cartesian self join does the trick. And I'll likely upload one more update to the share site when all is said and done...

                • 5. Re: Filtered Portals that update without Refresh Window
                  philmodjunk

                  Did some more testing, updated scripts based on what I found and also added more comments to some of these scripts so that they are easier to understand. Updated file has now ben uploaded with same download linked posted at the start of this thread.

                  I'm using this script to update the filtered portals via onObjectModify Script triggers:

                  #Layout is based on Invoice
                  Set Field[Invoice::Refresh ; Invoice::Refresh]
                  Commit Record
                  Set Selection [Invoice::gSearchField ; Start: Length ( Invoice::gSearchField ) + 1 ; End: 0 ]

                  It wasn't necessary to refer to the TableOccurrence on the other side of the cartesian self join. It seems that any local filed defined in the layout's table could be used in the first set field step. I'm not using gSearchField in this step as my tests show that using it with global storage specified does not work.

                  This suggests that Mark's update script might be further simplified to a single line as long as the field used has local storage defined.

                  • 6. Re: Filtered Portals that update without Refresh Window
                    mgores

                    Phil, if you had that refresh field in the product table you don't need the self join.  You can just use the invoice::gSearchField1 to Products::refresh for your cartesian to list all in the portal and to refresh.

                    I did another test with your file and if you make Products::refreshprod a global, autoentered calculation Invoice::gSearchField1

                    You can just use a Set Field [Invoice::gSearchField1 ; Invoice::gSearchField1]  onObjectModify triggered

                    That way every character entered commits the invoice recorrd and changes the products::refresh field in one step to update the portal.

                    • 7. Re: Filtered Portals that update without Refresh Window
                      philmodjunk

                      All Right! that both eliminates the extra occurrence in the relationships graph and also the need for commit record/set selection--producing a one line script for updating the portal. I suspect that this method will also update summary fields placed in filtered portals.

                      (The set selection was to put the cursor back at the end of the text entered by the user after the commit takes the focus away from the search field.)

                      Now to play with all three selection portals to see what happens if all use the same gSearchField. Looks rather cool to see all three portals update from input in the same search field...

                      Updated File now uploaded yet again...

                      This has been a great collaboration!

                      • 8. Re: Filtered Portals that update without Refresh Window
                        philmodjunk

                        I've made one last update to the shared demo file. It does not change any scripts or other design elements. I've just added and updated the comments and layout text included to better document the technique.

                        Key details to keep in mind in order to make this work:

                        1. The Cartesian Join must match the global search field to the refresh field--unlike other uses of cartesian join where any pair of fields may be used.
                        2. The search field should have global storate specified.
                        3. The refresh window needs an auto-enter calc that refers back to the global search field. (What's of minor interest is that selecting or clearing the "do not replace existing value option does not make any difference here.)

                         

                        From what my tests indicate, changing any one of these three details keeps the one line script from successfully updating the filtered portal.

                        • 9. Re: Filtered Portals that update without Refresh Window
                          TobiasRoth

                          Hi everyone.

                          This Demo has helped me a lot and in return I'd like to share an idea to enhance the portal filter to an AND-connected, order independent, partial word search:

                          My portal is being filtered by the following custom function

                          myGoogle ( needles ; haystack )
                          
                          If ( WordCount ( needles ) < 1 ; 1 ;
                          Let ( needle = LeftWords ( needles ; 1 ) ;
                          If ( Position ( haystack ; needle ; 1 ; 1 ) ;
                           myGoogle ( RightWords ( needles ; WordCount ( needles ) - 1 ) ; haystack ) ;
                           0 )
                           ) )

                          I have been inspired by a Post by Danny on Filemakerinspirations.com

                          For me it works very well but I have no Idea how this performs on a large number of records.

                          • 10. Re: Filtered Portals that update without Refresh Window
                            DanielWheelon

                            Thank you so much for sharing this technique.

                            I have it implemented, dynamically updating a portal as the user types in a global search field.  My one question is a simple and not very important one.  It seems the one script step is working for you, but I have had to use a Set Selection script step following Set Field.  Else, the field contents are selected after each character (OnModify).  It's been easily solved with the Set Selection; I'm just a little curious as to why it might be happening on my end.

                            Thanks again for sharing!

                            Dan W