1 2 Previous Next 17 Replies Latest reply on Jun 21, 2016 6:49 PM by bigtom

    Simplest solution filtering on portal on one Layout

    daniel.vareika

      Hello all,

       

      I an unable to find a solution through the web (too tired I think).

      I would like to filter through a self joint in a portal the record I would like to go to.

       

      I have to problems to solve:

       

      1. click on portal row (self joint) and go to record, all on same layout

      2. Fitering portal (I am planing on implementing this solution).

       

      Apreciate help for point 1.

       

      Best,


      Daniel

        • 1. Re: Simplest solution filtering on portal on one Layout
          SteveMartino

          The link you posted is a good one, I use it my db for filtering the portal as I type.  I assume for example with Products:

          You have a layout with a record of one product (Detail).  You also have a self join portal with the list of all products(Master).

          You type in the search, find the product in the list.  Then you want to click on that product in the portal row and have the Detail record change.

          If all of that is correct, then a simple way would be to make a button on the portal row, or an invisible button over the entire portal row, and have it go to related record, original layout.

          On my DB, I do this with a script that also clears the search box so all portal (master) records show.

          • 2. Re: Simplest solution filtering on portal on one Layout
            daniel.vareika

            Steve,

             

            This is what I want to make.

            The question is how to do the script and or relationship.

             

            I made another instance of the table with a self joint with X as a relation (so as to show everyting).

            But when I click on the button that trigers the script, I am not sure how to resolve it eficiently.

            If it by a perform find, or something easier, simpler in fact.

             

            Any help will be greatly appreciated.

             

            Best,

             

            Daniel

            • 3. Re: Simplest solution filtering on portal on one Layout
              SteveMartino

              See if this helps. Play around with it and watch it on the script debugger.  Typing at the top of the portal does the type ahead, and clicking on a portal row shows that records detail.  This may not be the finished version but it does what you want

              u/n: admin

              p/w 1234

              • 4. Re: Simplest solution filtering on portal on one Layout
                daniel.vareika

                Thank you Steve,

                 

                Your db helped me a lot.

                For some reason I always have problems with find requests on scripts.

                 

                Best,


                Daniel

                • 5. Re: Simplest solution filtering on portal on one Layout
                  IanRenwick

                  Daniel,

                  The example provided is something that I have used over a LAN to great benefit. It didn't work so well when there were a significant number of records (15,000+ from memory) and/or working over a WAN.

                  So if that is an issue, there might be a few other things you want to consider such as "Building a Timed-Delay Search Filter for WAN Performance" based on an technique offered by Daniel Wood and found at found at: https://www.teamdf.com/blogs/building-a-timed-delay-search-filter-for-wan-performance/

                  This enables the user to type one or more letters and only triggers the script when they pause for .25 of a second. I found this helps a lot when there are a significant number of records and it certainly improves the user experience.

                  There's been some recent discussion ( after a gap of a few years ) on Sara's original blog:  Dynamically Filtering Filtered Portals - Soliant Consulting that also suggests the new Refresh Portal step can replace the Set Field step. It could also replace the Refresh Window [ Flush Cached Join results ] step in the example file which creates more of a problem with a many records/WAN situation. There's also other descriptions of setting a few extra steps to set global variable to show the "found" count of portal records.

                  Ian

                  • 6. Re: Simplest solution filtering on portal on one Layout
                    daniel.vareika

                    Thank you Ian,

                     

                    I will go through it on the course of the day.

                    I as able to re do (hardcoded) Sara's post.

                    Since I have few records (less than 50) doesn't seem to be a performance issue.

                     

                    Later on I will try to globalize the solution (from hardcoded) to other dbs.

                    What I have included is a logicar errase (not erasing the record) and filtering them also dinamically (a matter of 1 or 0).

                    Recomend it for criticl solutions.

                     

                    best,


                    Daniel

                    • 7. Re: Simplest solution filtering on portal on one Layout
                      patricia

                      Thanks for mentioning this solution that Sara developed. I had not seen it and found it something that I could use periodically.

                      • 9. Re: Simplest solution filtering on portal on one Layout
                        siplus

                        I am enclosing my take to the "find as you type portal filtering".

                         

                        It covers both your 1. and 2. needs.

                         

                        It is - a bit simplified - what we use in our solution. Very good on LAN.

                         

                        (In our solution we have a "Patient" table and a "PatientSmall" table, this is the small one, normally the regular fields are autoentering values from the "Patient" table).

                         

                        It has 3 Surname-based fields because in italian you can easily find people called De Martini-Longhi Susanna.

                         

                        The enclosed example has 50k records, with randomized data.

                         

                        It might not be the simplest implementation, but it's fast and easily extendable.

                        2 of 2 people found this helpful
                        • 11. Re: Simplest solution filtering on portal on one Layout
                          siplus

                          You're welcome.

                           

                          Never had the time to adjust this implementation and share it with the community although I was looking forward to - I just found the time today.

                          • 12. Re: Simplest solution filtering on portal on one Layout
                            IanRenwick

                            WOW! Thank you for sharing this Siplus,

                            Using your file and sample data of 50,000 records, I set up a filtered portal using the technique first described by Sara and discussed earlier.

                             

                            I then compared the results of several searches using the 2 techniques:

                            Search 1: Letter string "susie"

                            SIPLUS:   5 found in 6 ms

                            SARA:      5 found in 2387 ms

                             

                            Search 2: Letter string "dre"

                            SIPLUS:   214 found in 7 ms

                            SARA:      607 in 2477 ms

                             

                            So, it needs to be noted that the Siplus' search found just those records with names starting with the search string whilst Sara's found records starting with the search string as well as those names with the string anywhere in the name, e.g. "Deirdre", "Childres" , "Andrew" etc.

                             

                            Obviously the ExecuteSQL( ) function will continue to challenge our existing thoughts and techniques.

                            I'm going to test this over the WAN and rethink a current project.

                             

                            many thanks,

                            Ian

                            • 13. Re: Simplest solution filtering on portal on one Layout
                              siplus

                              My implementation is meant for people at the reception desk who are looking for a patient. They know the patient's name so there's no need to type "dre" and come up with Andrew - it's even unwanted. They are fast typists, too.

                               

                              It must be said that the example posted is just a part of the finding script. The complete one switches to a different search as soon as they type a space, allowing them to enter "Smi Ja 89" to get Smith Jane born in 1989. To achieve this I use quickFind on another layout, containing just the relevant fields and grab the id's using a List Of summary field.

                               

                               

                              If [ PatternCount($par;" ") ]

                                Perform Script [ “QF [searchString]” ; Parameter: $Par ]

                                Set Variable [ $gFoundList ; Value: Get(ScriptResult) ]

                                Refresh Window []

                                Set Field [ Patient::gFoundList ; $gFoundList ]

                                Set Field [ Patient::gSQLFoundSet ; $gFoundList ]

                                Set Variable [ $$Found ; Value: ValueCount($gFoundList) ]

                                Perform Script [ “CommitAndGo” ]

                              Else

                                Set Variable [ $Par ; Value: Upper($Par) ]

                                If [ $Len > 0 ]

                                Set Variable [ $query1 ; Value: "SELECT FK_PatientNumber FROM PatientSmall WHERE ClientFirstSurnameArray[" & $Len &"] = ?" ]

                               

                              ....

                               

                              Script QF [searchString]:

                               

                              Allow User Abort [ Off ]

                              Freeze Window

                              Go to Layout [ “Patient_QuickFindNEW” (Patient) ]

                              Perform Quick Find [ Get(ScriptParameter) ]

                              If [ Get(LastError) ]

                                Set Variable [ $result ; Value: "" ]

                              Else

                                Set Variable [ $result ; Value: Patient::PKList ]

                              End If

                              Go to Layout [ “Patient | Selector” (Patient) ]

                              Exit Script [ Text Result: $result ]

                              • 14. Re: Simplest solution filtering on portal on one Layout
                                IanRenwick

                                Thanks Siplus,

                                Yes I would agree that 'there's no need to type "dre" and come up with Andrew - it's even unwanted in many circumstances' and on the basis of your calcs an expected outcome.

                                I only mentioned it to indicate that there was a different outcome and may be what someone else needed.

                                 

                                The search times for the two techniques was really the important issue and I found it worthwhile to quantify the difference. It certainly confirmed my user experience with other databases with large numbers and/or accessed over a WAN. Thanks for your additional scripts and how they work with the original example. Will have a play with them as well.

                                 

                                Really appreciate you taking the time to share this.

                                ian

                                1 2 Previous Next