1 Reply Latest reply on Mar 20, 2012 8:38 AM by philmodjunk

    Rolling 12 months



      Rolling 12 months



      I'm wanting to put a field on a report type layout that shows a rolling 12 months drop down menu for someone to choose from. Is there a way to set up a table so that it auto completes rolling 12 months? So right now, it would show March, Feb, Jan, Dec, and so on....but then on April 1st, it would show April, March, Feb, and so on?


      I'm hoping to use this field box as a start date (well, start month i guess) for a report.

        • 1. Re: Rolling 12 months

          Given that there are only twelve values in your value list and assuming that you only need a value from one field, not two (such as when you select  an ID number from field 1 and a name field for field two), this can be done with a bit of slight of hand.

          Define a table with 4 fields:

          MonthName, Order ; cOrderKey ; MonthNumber

          Order is a number field where you'd enter a value from 1 to 12 to specify the order in the value list. cOrderKey is a calculation that produces an invisible value we can use to sort our value list:

          Substitute ( 10^Order - 1 ; 9 ; " " )

          Define your value list to use MonthName as Field 1 and cOrderKey as field 2. Specify that the value list be sorted on field 2. Every month, you'd use a script to update the value of Order (has to be an indexed field so this can't be an unstored calculation using get ( currentDate )), so that the correct month is listed first.