4 Replies Latest reply on Jun 28, 2011 6:12 AM by yuichim

    Portal Filtering on Self Join



      Portal Filtering on Self Join


      This is a similar question to what I've posted before on filtering a portal.
      I have a self join table and a layout displaying Accounts.  It has a self join relationship so that it shows all the accounts in a portal.

      I wanted to create a button to only show "my" assigned accounts which has a field called accountowner and it has a parameter from Get(Accountname).  I would like to keep it as dynamic so that I can change the script parameter (maybe) to be able to filter the portal.

      What would be the best approach for this?

        • 1. Re: Portal Filtering on Self Join

          The only way to make a portal filter "changeable" is to refer to a field or variable in your relationship and then design your layout so that it is possible to modify the value and then force the filtered portal to refresh using the new filter values.

          Say you add text field: YourTable::AccountName to your layout and format it with a value list of account names. You can use this filter expression:

          PortalTable::AccountName = YourTable::AccountName to only show the records in the portal with the current account name shown in the field.

          This script can then force the portal to re-filter whenever you change the value in this field:

          Commit Record
          Refresh Window [Flush cached join results]

          A script trigger can run this script whenever a new value is entere/selected in YourTable::AccountName.

          • 2. Re: Portal Filtering on Self Join

            Hi Phil!

            I have a global field in the Accounts table called FILTER to store any filtering data.  If I wanted to show all records without any filter, what would be the calculation I should use?  I tried to create a case filter but it didn't work....

            • 3. Re: Portal Filtering on Self Join

              A Case Filter can work, but an Or operator is all you really need here.

              ( Accounts::GlobalField = "All" ) OR ( Accounts::GlobalField = Accounts::AccountName )

              Put "All" in the global field and you'll see all the accounts. You could also replace the term to the left of Or with IsEmpty(Accounts::GlobalField) if you want to show all accounts when the global field is empty.

              • 4. Re: Portal Filtering on Self Join

                Thank you Phil!

                I think I didn't grasp the idea of using the "or" operator, but I am getting it now.  It's an ah-ha moment.   I did put the operator filter as you mentioned above and all worked great!  Thank you very much as always!!