1 2 Previous Next 25 Replies Latest reply on Sep 30, 2013 9:51 AM by ToddCarlson

    Find records with date range of "This Week" etc

    ToddCarlson

      Title

      Find records with date range of "This Week" etc

      Post

           I am trying to use the sample below to generate a lists of records that fall in This Week and Next Week using a value list for those two and another value list for the "action field" being Measure Start Date, Install Start Date, ETA Houston etc.

           Here is what I used as a resource:

      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 []

            

      And the attached picture is what I came up with.  It doesn't work.  The gRangeOption field has a value list of This Week and Next Week (initially wrote script just for This Week).  The gActionSearch field is a value list of fields that I want the date range for (not sure if I can do this)  Thank you.

            

      Screen_Shot_2013-09-24_at_7.57.27_AM.png

        • 1. Re: Find records with date range of "This Week" etc
          philmodjunk

               Your set field step is trying to enter a date range into the gActionSearch field. By the naming convention, in my systems that field would have global storage specified and thus search criteria could not be entered into it for a find.

               Is gActionSearch a global field?

               The set field step needs to enter the search criteria (the date range for the week) into a field of type date that is not a global field.

          • 2. Re: Find records with date range of "This Week" etc
            ToddCarlson

                 I'm pretty sure I understand what you're saying.

                 gActionSearch is a global field but the fields in the value list that are the directed to fields are regular date fields.  Does that seem to answer your question?  Thanks.

            • 3. Re: Find records with date range of "This Week" etc
              philmodjunk

                   The field in the first parameter is the wrong field. It cannot be a field with global storage specified. The first parameter of the set field must be your date field that stores the different dates in the table being searched. (A global field makes no sense for that as its data will be the same for every record in the table.

                   The script example that you are working with is patterned after the examples found in this thread: Scripted Find Examples

                   Make careful note of which fields are global and which are NOT global in those examples and the one that you have been working with.

              • 4. Re: Find records with date range of "This Week" etc
                ToddCarlson

                     Thanks.  I ended up creating a "This Week" box with separate buttons for the different actions with a script for each action date and they all work.  That should suffice.  I like the Pop Up look and feel but I can't seem to get it to work.  Thanks again.

                • 5. Re: Find records with date range of "This Week" etc
                  philmodjunk

                       For the type of script shown, the pop up field must have global storage specified. But it is NOT the field into which you enter search criteria. It is the field FROM which you get the criteria needed for your search.

                  • 6. Re: Find records with date range of "This Week" etc
                    ToddCarlson

                         I'll figure it out I'm sure.  It would look and operate much better.  What would be the script formula for Next Week?  Thanks.

                    • 7. Re: Find records with date range of "This Week" etc
                      philmodjunk

                      Else IF [ Globals::gRangeOption = "Next Week" ]
                          Set Field [YourTable::YourDateField ; Let ( [T = Get ( CurrentDate ) ; D = T - DayOfWeek ( T ) ] ; D + 8 & "..." & D + 14 ]

                      • 8. Re: Find records with date range of "This Week" etc
                        ToddCarlson

                             Thank you sir.

                        • 9. Re: Find records with date range of "This Week" etc
                          ToddCarlson

                               I'm still trying to figure this out with value lists for the RangeOption (gRangeOption) AND the action (gActionSearch).  I now have three fields relating to this.  gRangeOption (This Week etc), gActionSearch (Sale Date, Measure Date, Install Date etc) and Date Range field that is not global.

                               I also have the Date1 and Date2 fields as well and they are global.  What I can't figure out is how/where to insert the Action Search option.

                               Thanks.

                          • 10. Re: Find records with date range of "This Week" etc
                            philmodjunk

                                 You'll need to explain how all those fields are supposed to work. What is the purpose of each field that you have described here?

                            • 11. Re: Find records with date range of "This Week" etc
                              ToddCarlson

                                   Without this function in place I go to "Find" and then physically put a date range (Date...Date) into the field that I want a list of.  So, installs that week and next, sales this month, measures this and next etc.  My goal is to not have to do that anymore and use this script to automate is much more.

                                   I'd like to pick This Week, Next Week, and This Month or MTD, and then pick the action item (installs, measures etc) and have the script "Find" those records.

                                   This stems from the thought that scripts can be written to do everything in a quicker process that I can do by using the Find feature and so on like I am doing now.

                                   I hope I answered your question as to how I'd like this to work.  Yesterday, I inserted a button for Sales, Measures, Installs etc for each, a separate button (five of them).  If I do this for Next Week and This Month, this will start to look messy.  The goal is to clean this up.  Thank you for your time.

                                   Here is a pick of my Tab view.

                                    

                              • 12. Re: Find records with date range of "This Week" etc
                                philmodjunk

                                gRangeOption (I can tell what this one does)

                                gActionSearch this field would appear to be formatted with a value list with these values: Sale Date, Measure Date, Install Date etc

                                     But how does the value selected in this field affect how your find is supposed to work? Am I correct that these are the names of different date fields in your database. So selecting "Sale Date" means that your script finds records where the date in the Sale Date field falls in the specified date range? And does the user select a single value from this list or is it a checkbox list where they can select multiple values?

                                     

                                          Date Range field that is not global.

                                     Don't see a use for that field.

                                     

                                          I also have the Date1 and Date2 fields as well and they are global. 

                                     These, apparently, are what you use to specify your date range.

                                     If I am correct about gActionSearch and that you are only permitting the user to select a single value from this list, then you can set up the following script:

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

                                Else IF [ ProjectManager::gRangeOption = "Next Week" ]
                                    Set Field By Name [ "ProjectManager::" & ProjectManager::gActionSearch ; Let ( [T = Get ( CurrentDate ) ; D = T - DayOfWeek ( T ) ] ; D +8& "..." & D +14]

                                Else
                                      Set Field By Name [ "ProjectManager::" & ProjectManager::gActionSearch ; ;Year ( get ( CurrentDate ) ) ]
                                End IF
                                Set Error Capture [on]
                                Perform Find []

                                     But this assumes that gActionSearch has a value that is an exact match to a date field name in your ProjectManager field.

                                • 13. Re: Find records with date range of "This Week" etc
                                  ToddCarlson

                                       It would appear that all of your assumptions are correct.  I'll give that a try today.  Thanks much!

                                  • 14. Re: Find records with date range of "This Week" etc
                                    ToddCarlson

                                         Script is done.  Which one of the fields would I attach this to and with what option, OnObjectSave?

                                    1 2 Previous Next