2 Replies Latest reply on Dec 5, 2011 11:07 AM by MediaSmith

    Creating Drop Down Date Range Lists from calculations based on the Current Date

    MediaSmith

      Title

      Creating Drop Down Date Range Lists from calculations based on the Current Date

      Post

      Sorry about the long title.  

      I have created a calculation that allows me to set a Date Range (MM/DD/YYYY...MM/DD/YYYY) based on the Current Date.  It will allow you to choose Yesterday, Today, Tomorrow, Last Week, This Week, Next Week, Last Month, This Month, Next Month, Quarter 1, Quarter 2, Quarter 3, Quarter 4, Last Year, This Year, Next Year (of course, you can also enter any date range you wish).  Via a trigger script it displays the correct Date Range.  It is very useful in Finds, Reports, etc.  I genreally use it as a global field and make it available throughout the solution. 

      What I would like to do now is provide a Drop Down Menu with these values all calculated out so that my users could select them directly without having to click around.  The only way I can think to do it is to create a little table that has these values as records that populate a Value List.  That seems a little clunky.  

      Surely there is a better way!

        • 1. Re: Creating Drop Down Date Range Lists from calculations based on the Current Date
          philmodjunk

          Try this approach:

          Define a value list with the text: "Today" ; "Tomorrow" ; "Last Week"; etc. listed as values. Use a case function in a calculation field or a script to use that selected text to determine which calculation to enter as your search criteria.

          To do exactly what you describe, you might use this approach:

          In a calculation field that returns text:

          List ( daterange1 expression here ; date range 2 expression here ; date range 3 expression here ; and so forth )

          Define a conditional value list such that this text field (must be stored and indexed) in the current record is the only sorce of values.

          If you have a serial number field, __pk_PrimaryKey, defined, you can use this in a self join relationship:

          YourTable::__pk_PrimaryKey = YourTable 2::__pk_PrimaryKey

          Create "YourTable 2" by selecitng YourTable in Manage | database | relationships and then clicking the duplicate button (two green plus signs).

          Now use the specify field option in Manage value lists and specify that the field be this calculation, taken from YourTable 2. Then select "include only related values starting from YourTable."

          Since this field must be indexed, you'll need a local storage date field that is loaded with today's date by an OnRecordLoad script trigger controlled script so that is current, but stored.

          • 2. Re: Creating Drop Down Date Range Lists from calculations based on the Current Date
            MediaSmith

             

            Thanks PhilModJunk, this works really well.