3 Replies Latest reply on May 21, 2014 1:00 PM by philmodjunk

    Portal - list unique values only?

    StylisticGambit

      Title

      Portal - list unique values only?

      Post

           Say I have a PRODUCTS table with 100,000 records.  One of the fields in this table is CATEGORY (validated to be non-empty).  So each record in the PRODUCTS table has a CATEGORY; there are currently about 40 total categories, but this number may change depending on the records in the PRODUCTS table.
            
           I’d like to create a portal from an INVOICES table to the PRODUCTS table which displays a list of all unique CATEGORY items.  So, I want a portal with 40 entries, not 100,000.  Also, to complicate matters, this portal will have to operate over a low-bandwidth internet connection.  Does anyone have a solution for this?
            
           Here’s a possible starting point.  I can create a value list of all items in the CATEGORY field and it behaves exactly how I want my portal to: modifications to the CATEGORY field for any record will dynamically update the value list, and this process requires almost no bandwidth.  However, I’d really like to find a similar solution using a portal because it would allow me to define a button for each portal row.
            
           Suggestions?

        • 1. Re: Portal - list unique values only?
          philmodjunk

               Set up a table of categories with one record for each category.

               Not only can this be used to produce your portal, this table can serve as the source of values for a value list of your categories.

          • 2. Re: Portal - list unique values only?
            StylisticGambit

                 Thanks for the response, PhilModJunk.  I considered that.  Except I have more than just one Category field; each Category has Subcategories, and some of those have SubSubCategories.  If I have a separate tables for Categories, Subcategories, etc., it now becomes a project to maintain them all.

                 Is this my only option?

            • 3. Re: Portal - list unique values only?
              philmodjunk

                   That seems like all the more reason not to try to manage this data in the products table

                   Depending on what you need to do with this data, there may be other options, but I don't have much to go on from what you have described thus far.

                   ExecuteSQL, for example, can list Distinct values in a single field that might be formatted to look and function much like a portal as long as you don't have to edit data in that portal.

                   And I've been able to gin up filtered portals that drop out all but the first instance of each value in a given field. But I'd not try that from the products table, I'd set up a table to manage the categories and the sub categories and use the filter to drop out duplicates in a portal to that much smaller number of records.

                   How will you use this portal of categories once you have it up on your layout?