13 Replies Latest reply on Sep 19, 2015 4:53 PM by g4guitar

    How to filter portal records based on a global field?

    g4guitar

      I have a layout based on a list view. I was able to set up a filter based on a global field that allows me to select the category from a drop down list to show only those records. In the example I have chosen 'Marketing' on the filter so I only see those records. My question is this. How can I do the same in a portal?

       

      0099099.png

        • 1. Re: How to filter portal records based on a global field?
          nicolai

          It is unclear if you want to filter records in a portal (as in your header) or in the list. There are number of techniques for both.

           

          For portal, the simplest approach is to use portal filtering. This is on Portal Setup / Specify Filter portal records. Add calculation to match your global field and show all when the field is empty (or show nothing).

           

          Another option is to add another predicate to the relationship  your portal is based on, adding link between your global field and Category filed in portal. Category field will have to be indexed and you have to think if you need to display all records when the global selector is empty.

           

          For the list layout (as in your post) you can add a script trigger on the global field modification or exit, which will run a scripted search.

          • 2. Re: How to filter portal records based on a global field?
            g4guitar

            Thank you nicolai,

             

            I really appreciate your help. Sorry it was a little vague so to clarify in more detail I have it all working nicely in my layout (which is as you mentioned "For the list layout (as in your post) you can add a script trigger on the global field modification or exit, which will run a scripted search."). It's based on a list view from the table (which is called 'ToDo' btw) but just not sure how to get the same exact thing happening in a portal.

             

            Is there something different required? When I use the same script in the portal it doesn't work so I figure there is a different setup required. Here is what happens in my list layout. Any help getting the same setup in the portal would be great. Thank you again.

             

            Screen Shot 2015-09-17 at 8.58.44 pm.png

            • 3. Re: How to filter portal records based on a global field?
              erolst

              You cannot use that script to filter the portal; a Find is performed against the table of the current layout. You can include fields from the related table in the Find, but the result will always be a found set from the current layout's table. **

               

              To filter the portal display, you can either filter the underlying relationship via the predicates (match field pairs) in the relationship definition, or filter the portal using … well, a portal filter calculation.

               

              The former is (much) more performant for a larger number of records, the latter is more convenient to implement.

               

              Best if you can combine both: use the standard relationship to display just the related records in a portal, then filter that (presumably much smaller) related set on some sub-category.

               

              e.g. create a global text field gFilterCategory, create a value list of categories from the field in the portal table, format the global field as a checkbox with that value list, and filter the portal with

               

              not IsEmpty (

                FilterValues  (

                  PortalTable::category ;

                  LayoutTable::gFilterCategory

                )

              )

               

              which would allow you to filter on multiple categories. (Don't forget to add a refresh, e.g. a script OnObjectModify for the global filter.)

               

              ** In the interest of completeness: you could use a Find (or an SQL query) to search in the related table, collect the found IDs and use them to drive another relationship/portal …

              • 4. Re: How to filter portal records based on a global field?
                g4guitar

                Thank you erolst,

                 

                I knew portals must have been different so that has helped to clarify. I have gone through your steps as far as I can see but I must still be missing something as the records in the portal have now disappeared. Below is what I have done.  Can you see what I am missing?

                 

                Screen Shot 2015-09-18 at 11.32.33 am.png

                • 5. Re: How to filter portal records based on a global field?
                  g4guitar

                  erolst firstly just want to say thank you very much. I fiddled and it now seems to be working now. Not sure what I did but all good.

                   

                  My question now is how do I show all records with having to check every box? Is there a script I could use to show all portal records?

                   

                  Thank you.

                  • 6. Re: How to filter portal records based on a global field?
                    beverly

                    If I interpret your screen shot correctly, you are using "not empty" to SHOW what's checked. If you do not wish to change this filter, you can create button(s) to "check all" and "uncheck all"

                     

                    Check all = set field (gfieldcategory; <<whatever your valuelist is>>)// you can use the Design function "ValueListItems()" to get the list

                     

                    UnCheck all = set field (gfieldcategory; "" ) // empty the values

                     

                     

                    beverly

                    • 7. Re: How to filter portal records based on a global field?
                      nicolai

                      You can  add "Show all" button, similar to the one you had on the list layout, and attach a script to this button which will set the global field to the full list of values and refreshes the screen.

                      • 8. Re: How to filter portal records based on a global field?
                        erolst

                        g4guitar wrote:

                        My question now is how do I show all records with having to check every box? Is there a script I could use to show all portal records?

                         

                        You have several choices:

                         

                        1. Leave the portal filter calc as is and add a button to select all checkboxes, e.g. with

                         

                        Set Field [ field: Table::gYourFilter ; calculated value: ValueListItems ( "" ; "yourCategoryValueList" ) ]

                         

                        2. Amend the portal filter calc with


                        or IsEmpty ( Table::gYourFilter )


                        and use a script to clear the filter field and refresh.


                        • 9. Re: How to filter portal records based on a global field?
                          g4guitar

                          Thank you everyone. Fantastic and that worked perfectly for checking and unchecking the boxes but still one small problem.

                           

                          When I uncheck all the boxes (Hide All) everything still appears on the list. When I check a box just this items appear so it works but I want all the items to disappear when I uncheck all the boxes. Is there something missing in my script?

                           

                          Screen Shot 2015-09-19 at 1.56.54 pm.png

                          • 10. Re: How to filter portal records based on a global field?
                            erolst

                            If you want to see no records when the filter is empty, just leave the calculation as it was (with only the FilterValues() expression); only records that pass that muster will be displayed, and with an empty filter field that will be none.

                            • 11. Re: How to filter portal records based on a global field?
                              beverly

                              because you added the "or" to your original filter. This says to SHOW if the global is empty, thus everything shows. Take that out.

                               

                              beverly

                              • 12. Re: How to filter portal records based on a global field?
                                lijnbach

                                we have someone very active on the forum, PhilModJunk

                                 

                                Visit his website (caulkins consulting) and you will find a lot of downloads for advanced portal sorting en portal filtering, also based on checkbox values.

                                 

                                Hans Lijnbach

                                • 13. Re: How to filter portal records based on a global field?
                                  g4guitar

                                  Brilliant. All working exactly as I wanted. Thank you again everyone. This must be the world's best forum. You are all incredibly helpful. I am very grateful to you all.