2 Replies Latest reply on Apr 14, 2011 8:34 AM by margotjacqz

    best practices ? filter portal v subsidiary table



      best practices ? filter portal v subsidiary table


      What are the pros and cons of using filtered portals? as compared to using a dependent table? 

      Let's simple terms two tables with Large data sets, People and Companies. For various reasons you want to keep all records, but there are differing levels of Active. (For example, if a company is closed or has been acquired, you may want to keep the record, but do not need to have in an everyday list to choose from; similalry if a Person is lost.) Say you have ToDo records referencing People. In creating a new Todo, it's really best to have only the active ones on the drop-down.

      My inclination is to create a filtered table occurance keyed through a status field (or something) and then use that as the basis for active layouts, current value lists, new records etc. That is create once, use many ways. Is this going to be more complicated than I think?

      However, filtered portals also seem very useful. What are the thoughts here on if that would be a better way to go?

        • 1. Re: best practices ? filter portal v subsidiary table

          There are trade off's with either approach. I often find the best solution is a mix of the two.

          Trade off example 1

          Aggregate functions that refrence the related table evaluate at the data level, not the presentation level. Thus, Sum ( RelatedTable::field ) will sum all related values as defined by the relationship, but any filtering defined in the portal will not affect this total. This may or may not be desirable in a given situation.

          You can place a one row copy of your portal on the layout and place a summary field defined in the portal's table to compute a total that is limited to the filtered records. However, this approach often has screen refresh issues if you need to edit values being summarized in the portal records which can require extra scripting and an unsightly "screen flash" when you force the update.

          Trade off example 2

          Trying to define a relationship that duplicates a portal filter expression that uses pattern matching can be difficult to impossible to set up. Try to think of a way to define a relationship that replicates PatternCount ( portaltable::Description ; Globals::gSearchField ). That's a simple filter that can show all records where text such as "app" entered into gSearchField limits the displayed rows to Apple, application, Snapple and so forth.

          Also, portal filters can greatly simplify your relationship graph as one relationship can now be used for a whole series of different portals. A demo file I just posted a download link for, uses one relationship in a calendar demo where each portal filters for dates for a different day of the week. Without the portal filter, I'd have needed 7 different relationships and 6 more calculation fields to use as keys in them.

          • 2. Re: best practices ? filter portal v subsidiary table

            Thanks Phil, as always for thoughts on how to think about it ...

            I noted that pattern matching calculation in the file you referred me to earlier. this makes it even clearer how it works. I am starting to get a little over caffinated on the possibilitlies <s>.