3 Replies Latest reply on Mar 24, 2016 7:19 AM by Stu412

    Short value list from many similar records

    Stu412

      Hi there,

       

      I need to create a value list for portal filtering.  The data in the portal is of the format:

       

       

         

                Cust IDAreaValue Date
      1010North1000031/10/2014
      1010South1000031/10/2014
      1010North1000031/10/2013
      1010South1000031/10/2013
      1010North1000031/10/2012
      1010South1000031/10/2012
      1015North1000030/09/2015
      1015South1000030/09/2015
      1015North1000030/09/2014
      1015South1000030/09/2014
      1015North1000030/09/2013
      1015South1000030/09/2013

       

      As you can see, there are multiple customers on the data table, so if I create a value list based on the values in 'Year', it'll be massive as it's picking up each record.  Instead I just want the value list to look at each distinct instance for the customer who's record I'm viewing at the moment (and therefore it's only their records in the portal) and return, in this case above, only 3 year's worth:

       

      1010  31/10/2014

      1010  31/10/2013

      1010  31/10/2012

       

       

      I've tried an ExecuteSQL Select Distinct and this is waaaaay slow, so it's not viable in my case.

       

      Any other help though is greatly appreciated.

      Thanks in advance

        • 1. Re: Short value list from many similar records
          Mike_Mitchell

          Set a global field equal to the current customer ID. Create a relationship between that global and the target table. Your value list will then be the related records.

           

          As an aside, you mentioned using this for portal filtering. If ExecuteSQL is slow, then portal filtering likely will be as well. You might need to reconfigure to use a dedicated relationship instead.

           

          HTH

           

          Mike

          • 2. Re: Short value list from many similar records
            beverly

            I will have a field (auto-enter calculation that results in YEAR only, as text or number). Then use this field for the value list and for the search field. It will be unique and not include all the months and days as you have.

             

            I also will have a field (auto-enter calculation that results in YEAR_MONTH = yyyy_mm or yyyymm as text), so that I can use it for charts, finds, filters, etc.

             

            Year(mydateField) & "_" & Right("00" & Month(mydateField); 2) // use the "_" or not

             

            This is alpha-sortable, and convenient for sub-summaries where records need to be grouped by months (within years).

             

            beverly

            • 3. Re: Short value list from many similar records
              Stu412

              Mike, Beverly,

               

              Thanks for your input, always appreciated.

               

              Have got this working now using a second TO and a value list based on this, relating back to the main parent record.

               

              (Why didn't I think of that!)

               

              Thanks