14 Replies Latest reply on Dec 31, 2011 9:16 AM by MikeHaddon

    Filter on a Filter?

    MikeHaddon

      Title

      Filter on a Filter?

      Post

      I'm developing a system where I need different users to be able to see different subsets of data.  Then, I need the system to function based on that subset of data.  The functionality is the same regardless of the user, but the recordset would be filtered differently depending on the user.  This functionality would include filtering, so it would be like filtering on a filter.

      Basically, I am creating a legislative tracking system.  The functionality is the same regardless of the user.  They can search for legislators, legislative bills, etc.  Where this gets a bit more complex is that I will have different groups using the system, including myself.  This isn't a security issue.  It is more of a narrowed scope issue.  For example, I may need to and only want to track 50 bills.  Another user is likely to be tracking 300 bills.  I would like to be able to get in an view and work with only those bills I am tracking, and not get bogged down by the 300 bills another user is tracking.

      Is there a way to create and work in this narrower recordset, while maintaining the same functionality (which includes filters itself)?  Thanks for any advice.

        • 1. Re: Filter on a Filter?
          philmodjunk

          There are multiple approaches that can do what you require. The details depend on the design of your system, both the tables/relationships involved and also the user interface you choose to set up.

          Finds can be scripted and script triggers can perform those scripts. Thus, just navigating to a layout can trigger a find that finds all records meeting criteria specific to the current user. (User's can be identified as an individual via the get ( accountName ) function. User's can be identified as beeing part of a specific group via get (privilegesetname ). )

          Your scripts can also enter find mode, specify criteria and then use Constrain Found Set to filter the existing found set of records to show only records from the found set that meet your criteria. This script can be triggered by a mode change that exits Find mode, so users can perform their own searches, but a script triggered script then kicks in to filter the results of their script down to limit their view to just "their" records.

          You can also set up filtered portals to display records with portal filter expressions that combine user input with an account or privilege set name to display selected groups of records.

          A key factor in all of this is working out how to identify specific records as "belonging" to a specific user. Since you have multiple groups of people working with different groups of multiple records, it's very likely that you'll need a many to many relationship implemented with a join table to link many different users to many different records in your database tables. Thus, you will need a table with one record for each user or group of users and then a "join" table that links them to the records that "belong" to them.

          • 2. Re: Filter on a Filter?
            MikeHaddon

            I think what I am trying to do is provide the user with flexibility to narrow or constrain the bills listed in a portal based on criteria they select.  I need them to be able to select a variety of criteria, and then narrow the result set based on what they have selected.

            The approach I would like to use is to have a few check boxes or radio buttons the user can select.  Then, have a button that narrows based on what the user has selected.  There are three primary areas than could be used in conjunction to narrow:

            1.  House Bills or Senate Bills

            2.  Tracking group (e.g. Governor, Law Enforcement, Corrections)

            3.  Position by Tracking group (same groups as in #2, but by position, such as Support, Oppose, etc.)

            With these items to narrow by, the user would be able to select "House Bills" and "Corrections", then use the button to constrain the portal to just House Bills that Corrections is tracking.  Using item #3 from above, the user could be even more specific, such as House Bills that Corrections Supports.

            This is all based on a table I have called tblBills.  This table contains a House field (House or Senate), fields for [Corrections, Governor, Law Enforcement] each with a Yes or No - this tells me if the group is tracking a particular bill, then I have a position field for each group [CorrectionsPosition, GovPosition, etc] each with values such as Support, Oppose, No Position.  

            I have to have fields for each group because there may be one bill that Corrections is tracking, as well as the Governor's Office - so it isn't mutually exclusive.  Similarly, each group will take their own position on each bill.

            I'm trying to make this as simple as posible for the end user to narrow the list of bills being shown in the portal.  Does this make sense?  Thanks for any assistance.

            • 3. Re: Filter on a Filter?
              philmodjunk

              ...fields for [Corrections, Governor, Law Enforcement] each with a Yes or No - this tells me if the group is tracking a particular bill, then I have a position field for each group...

              That structure really complicates things for you. Not only would a filter expression need to check each pair of fields (tracking group and their position), but changes to the political landscape that add/remove such groups will require adding/removing fields and updating this portal filter to match.

              I suggest you treat the existance of tracking groups and their postion on a given bill just like you do committee membership as this is another many to many relationship.

              Bills-----<Bill_TrackingGroup>-----TrackingGroups

              Bills::BillID = Bill_TrackingGroup::BillID
              TrackingGroups::GroupID = Bill_TrackingGroup::GroupID

              The group's position would be stored in a field in Bill_Tracking.

              This will make it easier to filter down by tracking group and their position on the bill as well as enabling you to associate a completely flexible number of such groups with any given bill. This works well for filtering on Items 1 and 2 but filtering by position will still be a challenge. See if you can get that far first while I mull over possible approaches for filtering by a tracking group's position on the bills.

              • 4. Re: Filter on a Filter?
                MikeHaddon

                Phil......

                I know this solution isn't elegant, but it is working for what I need.  What I ended up doing was to create tabs on the left side where the portal was for listing the bills.  I created five tabs (All, Corrections, Sentencing, CCJJ, and Governor).  On each tab, I placed a portal, each tied to a new and different occurrance of my tblBills (where all the bills are located).  With each portal, on the individual tabs, in portal setup, I filtered by the group I was interested in.

                Now, there are tabs across the top.  The user can select Corrections and they get a portal only listing bills Corrections is interested in.  This part is working - although I know you could advise something much more elegant and efficient.

                What I would like to do now, on each tab, is allow the user to Constrain further what is shown in the particular portal.  The constraining would be based on that entity's position on the bill.  So, for example, I would have a button within the Corrections tab that says "Support".  The user would be looking at all bills being tracked by Corrections.  Selecting "Support" would narrow these to only bills Corrections supports.  It seems like I could just use a script with Constrain or multiple filters.  I did create a script each way, but when I use these as buttons on the Corrections tab, the portal doesn't do anything.  Do I need to refresh the portal?  Any ideas welcome as always.  Thanks so much.

                • 5. Re: Filter on a Filter?
                  philmodjunk

                  You can't script this. You'd need to use a portal filter expression instead. If you want help with that expression, please spell out the Table occurrence of the layout, the table occurrence of the portal and how they are related so that I can take that info into account when suggesting a calculation.

                  PS. Imagine what you will need to do with your current design if you determine that a new interest group's support for/against legislation needs to be tracked...

                  • 6. Re: Filter on a Filter?
                    MikeHaddon

                    Just focusing on the setup for Corrections bills (the rest are set up similarly, and if I get it working for Corrections, I can get the rest to work).

                    tblBills (contains all of the information about each bill:  number, sponsor, title, description, tracking [Corrections, Sentencing, etc] and positions for each of these groups.

                     

                    BillLookupCorrections [new occurrance of tblBills]

                    BillLookupCorrections::UDC X tblBills::gCorrections

                    This is the relationship between the tables that is allowing the Corrections portal to function correctly.

                    The portal on the Corrections tab is showing related records from BillLookupCorrections.  The filter in portal setup I have is BillLookupCorrections::UDC="Yes"

                    This is providing only Corrections' tracked bills in this particular portal.  I need to be able to filter one more level when a user selects a button - by position [Support, Oppose, Priority].  In the end, it should only show Corrections tracked bills.  Then if the user wants to only see Corrections bills that we oppose, they would select the "Oppose" button to provide that narrowed list.

                    I do understand what you are saying in your P.S..  For now I am comfortable, as I have been tracking bills for ten years, and these are the only groups that we need.  Thanks again.

                    • 7. Re: Filter on a Filter?
                      philmodjunk

                      BillLookupCorrections::UDC="Yes" And ( Isempty ( tblBills::gSupportOppose ) or tblBills::gSupportOppose = BillLookupCorrections::SupportOppose )

                      If gSupportOppose is left empty, you see all bills tracked by the Utah Department of Corrections. If you specify Support or Oppose in the gSupportOppose field, you'll see only those supported or opposed by that entity.

                      • 8. Re: Filter on a Filter?
                        MikeHaddon

                        So, do I need a script associated with, say a button called Support, that sets gSupportOppose to "Support"?

                        • 9. Re: Filter on a Filter?
                          philmodjunk

                          Either a script or format the field with a value list so that you select either value ( or no value ) by picking from the value list.

                          Then we'll need to tweak things a bit to get the filtered portal to refresh without using Refresh Window[flush cached join results].

                          • 10. Re: Filter on a Filter?
                            MikeHaddon

                            Ok.  I created the following script:

                            Set Field[tblBills::gCorrections; Get(ScriptParameter)]
                            Commit Records/Requests [No dialog]

                            I put a button on the layout that runs the script and feed it the parameter "Support".

                            When I tried selecting this button, the portal went empty - No bills.  I do have Support on a few.  Any ideas? 

                            • 11. Re: Filter on a Filter?
                              philmodjunk

                              Make sure that the data types for gCorrections and the field in BillLookUpCorrections to which it is compared in the filter expression are both text.

                              Make sure that the exactly correct value is entered by the script so that the values in the two fields match for records that have the specified support or opposition.

                              • 12. Re: Filter on a Filter?
                                MikeHaddon

                                I think I have messed this up.  Here is what I have for the portal filter:

                                BillLookupCorrections::UDC = "Yes" and (IsEmpty(BillLookupCorrectionsPositions::gCorrections) or BillLookupCorrectionsPositions::gCorrections=BillLookupCorrections::UDCPosition)

                                I ended up creating a new occurrance of BillLookUpCorrections and called it BillLookupCorrectionsPositions.  They are related as follows:

                                BillLookupCorrections::UDCPosition = BillLookupCorrectionsPositions::gCorrections

                                I did this because the fields on the layout I am populating based on the portal selection are tied to tblBills.  When I included tblBills in the script and portal filter, it changed what was being shown elsewhere on the layout.  So, I was trying to leave tblBills out of the equation.  If you recall, I have the portal set up as a button that, based on the bill the user selects, populates information about the bill selected from tblBills.

                                The script I have that is trying to set the filter to a specific position is as follows:

                                Set Field [BillLookupCorrectionsPositions::gCorrections; Get(ScriptParameter)]
                                Commit Records/Requests [No dialog]

                                I get lost a bit on setting the relationship, especially using a global field. 

                                • 13. Re: Filter on a Filter?
                                  philmodjunk

                                  Given the original relationship:

                                  BillLookupCorrections::UDC X tblBills::gCorrections

                                  was the layout based on tblBills or BillLookUpCorrections?

                                  I have assumed that the layout was based on tblBills and the portal on BillLookupCorrections.

                                  If so, then there is no need for an added table occurrence here. You should use:

                                  BillLookupCorrections::UDC="Yes" And ( Isempty ( tblBills::gCorrections ) or tblBills::gCorrections = BillLookupCorrections::UDCPosition )

                                  This should only affect the contents of the portal. Data from tblBills shown on the same layout should not change and the global field: gCorrections is the only field that need change. I am assuming that UDCPosition stores the text: "Support" if they support the bill and "Oppose" if they do not. With that relationship, setting gCorrections to "Support" should filter out all UDC tracked bills except those that have the text "support" in their UDCPoisition field.gCorrections would be a text field with global storage. Since it is global, you can actually use the same global field in all the portal filter expressions on all the tabs, you need not have a different field for each such portal.

                                  Please also remember that we are using a less than optimum structure here. Sure you still want to stick with that structure?

                                  • 14. Re: Filter on a Filter?
                                    MikeHaddon

                                    You are amazing!  It works!  Thanks....