1 Reply Latest reply on Oct 21, 2011 10:39 AM by philmodjunk

    Filtering Lists Without Flush Cached Joins or "Portal Filtering"

    Vinny

      Title

      Filtering Lists Without Flush Cached Joins or "Portal Filtering"

      Post

       Hi,

      I'm trying to tackle filtering (list layouts and portals) without the use of using the built in portal filter option.

      I have read and heard that using the Refresh Window [Flush Cached Join Results] can really slow down a file with large amounts of records, and I want to avoid using it.

      I have created a sample setup with a Projects Table and a Parts Table.  The idea is that I want to create a list for the user to search to find a part to add to a project.

      I want the user to be able to filter by category, type, and then a text filter (which will show any parts *containing* the text in the box).

      I understand how to create the relationships that link category and type to the list.  I still have two challenges:

      1) if the user leaves the category field blank, how do I show all parts with all categories?

      2) How do I do a filter where the part number *contains* what is in the search box?

      My objectives would be to minimize the amount of globals in all tables, and as mentioned before, avoid using the built in portal filter technique.

      See attached screenprints.

      Thanks!

      Picture1.png

        • 1. Re: Filtering Lists Without Flush Cached Joins or "Portal Filtering"
          philmodjunk

          Hmm, well that "contains" requirement is something that's really only practical (would love to be shown to be wrong about this), with a filtered portal where you can use the patterncount function in the filter expression. "starts with" can be done, though even that best works with a recursive custom function that parses the text in category into a return delimitted list of values such as:

          A
          AP
          APP
          APPL
          APPLE

          To use in the relationship. (Values separated by Return characters match a related record if any one value matches the value in the other field.)

          if the user leaves the category field blank, how do I show all parts with all categories?

          Set up some data on both sides of the relationship that match only if the category field on the parent side is empty.

          On the parts table, define a calculation field such as:  List ( Category ; "XXALLXX" )

          You can also format Category as a check box field so that one part can be a member of more than one category. With this approach, Add "XXALLXX" and select this value for all existing part records. (This value can be auto-entered to select it for new records.)

          On the parent record, define a calculation field such as IF ( IsEmpty ( Category ) ; "XXALLXX" ; Category ) and clear the "do not evaluate if all referenced fields are empty" check box inside the specify calculation dialog.