2 Replies Latest reply on Sep 16, 2011 12:37 PM by iwrc

    Filtering Portal Based on Value in a Field

    iwrc

      Title

      Filtering Portal Based on Value in a Field

      Post

      Hello helpful helpers.

      I have a portal that shows people registered for a course that I'd like to filter to show only some people registered for the course dependent on their value in one particular field ("Reg_status" that is filled from a value list).

      A little about my simple database:

      Table Relationships (Three tables, People, Classes and Class Registrations) (All IDs are unique serial numbers)

      People    <-------------------------->      Class Registrations                 <--------------------->            Classes

      (Primary Key : People ID)                (Primary Key: Class Reg ID)                                        (Primary Key: Class ID)

                                                  (Foreign Keys of People ID + Classes ID)   

      The portal I am working with is on a layout of the Classes table so that you are looking at portal to "Class Registrations." The portal is of every record in the "Class Registration" table that matches the particular Class ID of the record in "Classes" that is being viewed.

      I'd like to change it in the following way:

      -1. Change the portal to only show "Class Registration" records who have either "Paid", "Unpaid," "Complimentary," or "Payment pending-Check in mail," in as value in the field of "Class Registration:Reg Status."

      -2. Then do a count of only the records that a appear in this filtered portal, is that possible?

       

        • 1. Re: Filtering Portal Based on Value in a Field
          philmodjunk

          What value list format have you set up for the Reg Status field? I'll assume you permit selection of only one such value and don't have multiple values selected in this table. If this is permitted, then we'd need to change the expression that I'm about to post...

          You could set up a filter expression with a bunch of Or operators: Class Registration:Reg Status = "Paid" or Class Registration:Reg Status = "complimentary" or ...

          You could use a case function to make it a bit cleaner: case ( Class Registration:Reg Status = "Paid" ; True ;
                                                                                                 Class Registration:Reg Status = "complimentary" ; True ;
                                                                                                 // and so forth...
                                                                                               )

          Or you can use this expression:

          ValueCount ( FilterValues ( List ( "Paid" ; "Unpaid"; "Complimentary" ; "Payment pending-Check in mail" ) ; Class Registration:Reg Status ) ) )

          All should filter the portal the same way.

          To get a count of the records in a filtered portal, best bet is to define a summary field in the Class Registration as the "count of" your primary key. (Any field that's never empty will work the same.) Then put this field on your layout, inside it's own one row portal that uses the same filter expression as your current portal. you can make the portal boundaries invisible to make this look like it is not inside a portal.

          Note: if you set up a filtered portal with an expression that can filter out different records depending on a value the user selects/enters in a field, you need to use a script with Commit Record and Refresh Window [flush cached join results] to get the portal to update consistent with the new value produced by the filter expression.

          • 2. Re: Filtering Portal Based on Value in a Field
            iwrc

            Thanks PhilModJunk! All of the above worked perfectly. Thank you!