8 Replies Latest reply on Aug 26, 2016 6:04 AM by jfletch

    Create drop down list from global variable made in ExecuteSQL

    Stu412

      Hi all

       

      I have quite a specific need at the moment.  I need to produce a drop down list of the most recent 5 year's worth of dates (which in turn acts as a filter on a portal).  I have older data in the table in question but don't want these dates appearing on the dropdown.  I just want the most recent 5.

       

      For example, I'd want to exclude 2010 below (and moving forward, when 2016 comes in, exclude 2010 and 2011 etc):

       

      • 2010 Period 6
      • 2011 Period 5
      • 2012 Period 4
      • 2013 Period 3
      • 2014 Period 2
      • 2015 Period 1

       

      I've done a little ExecuteSQL to pull this out as below for one unique customer and this produces a lovely list, especially when placed into a $$global variable via a small script:

       

      ExecuteSQL (

      "

      SELECT Year from DataTable_Home

      WHERE

      CustomerID_FK = ? and

      Period < ?

      GROUP BY YearEnd

      "

      ;"";"";

      2473;

      6

      )

       

      Question is, how do I use the list which I can see in the Data Viewer as a value list?

       

      Thanks