4 Replies Latest reply on Jun 14, 2013 6:35 AM by ToddCarlson

    Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc

    ToddCarlson

      Title

      Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc

      Post

           All,

           I have a list of records where sales people manage a list of prospects.  Before sales meetings, I like to look at what they did in the 7 days prior to meeting day (relating to modified records).  I can do this in Find Mode range... etc but I would love to be able to either have a drop down where I can pick the above from a list and it finds the records automatically based on what I chose, or I simply have a "Date Range" area where I can drop down a calendar and pick a start date and then the same for end date.

           I am a fairly novice user...  Thanks,  Todd

        • 1. Re: Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc
          philmodjunk

               To start, see this thread with many examples of scripted finds--including some that use calculated date ranges.

               If that's not enough to get things working, feel free to use Post A Answer to post back to this thread and we'll take a closer look at what you are trying to do.

          • 2. Re: Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc
            ToddCarlson

                 I've looked at all those and they aren't close to what I need, AND I can't seem to connect the dots using those methods to get to something that does work.  Thanks.

            • 3. Re: Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc
              philmodjunk

                   I think that they are exactly what you need, but you need to add in additional details to get from the menu selection to the specified date range criteria. The examples show how to produce the needed criteria.

                   Partial script example:

                   Say you define a global text field, gRangeOption, with a value list of your date range options: "This Week", "This Month", "Last Month" and a pair of global date fields, gDate1, gDate2,  for specifying an arbitrary date range:

                   Enter Find Mode []
                   If [IsEmpty ( Globals::gRangeOption ) // user didn't select a range option, check for arbitrary date range ]
                      If [ Not IsEmpty ( Globals::gDate1 )  and Globals::gDate2 > Globals::gDate1 //valid date range was entered ]
                         Set Field [ YourTable::YourDateField ; Globals::gDate1 & "..." & Globals::gDate2 ]
                      Else
                         Enter Browse Mode []
                         Show Custom Dialog ["ERROR: No valid search criteria was entered"]
                      End If
                   Else IF [ Globals::gRangeOption = "This Week" ]
                       Set Field [YourTable::YourDateField ; Let ( [T = Get ( CurrentDate ) ; D = T - DayOfWeek ( T ) ] ; D + 1 & "..." & D + 7 ]
                   Else If [ Globals::gRangeOption = "This Month" ]
                        Set Field [YourTable::YourDateField ; Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) ) ]
                   Else
                         Set Field [YourTable::YourDateField ;Year ( get ( CurrentDate ) ) ]
                   End IF
                   Set Error Capture [on]
                   Perform Find []

                   About global fields:

                   Fields with global storage specified in field options may be defined in any table in your database file and they will still be accessible to any script and on any layout in your file. I prefer to define all such global fields in their own globals table unless they serve as a match field in a relationship.

                   Unlike non-global fields, the values in global fields remain accessible when the current window enters find mode. Thus, the above script will not work as written unless the fields referenced in the IF steps and the second parameter of the first Set field step are defined to use global storage.

              • 4. Re: Find Date Range in Modified Date column - ex. "This Week", "This Month", "Last Month" etc
                ToddCarlson

                     Plil, you are correct.  Now let me see if I can figure out how to get that done.  Thanks so much!!