1 2 Previous Next 21 Replies Latest reply on May 7, 2010 12:19 PM by mrvodka

    Search records for range of date

    lamp

      Title

      Search records for range of date

      Post

      Dear All,

       

      I am trying to search records  from x date untill y date. for example i want to search all record created form 13/1/2009 to 14/2/2009. 

       

      Can you please tell me how can I do that?

      I have created a script but it doesn't work my script is as follow:

       

      Enter Find Mode[]

      Show Custom Dialog ["Find Records"; "enter the date bellow; records::CreatedDate; records::ToDate]

      Set Variable [$DateFrom; Value:records::CreatedDate]

      Set Variable [$DateTo; Value:records::ToDate]

      Set Field [records::searchrang; $DateFrom & "..." & $DateTo

      If [Get (LastMessageChoice) = 1]

      Perform Find []

      End If

       

      records::CreatedDate   is a date field

      records::ToDate           is a date field it is empty field no date on it

      records::searchrang     is a text field

       

       

      I will apprecite if any one can help me why it doesn't bring the data or any other easy way

       

      Thanks

      Lamp

        • 1. Re: Search records for range of date
          mrvodka
            

           Why not just use two global date fields to ener in the date range?

           

           

          Show Custom Dialog ["Find Records"; "enter the date bellow; records::gDateFrom; records::gDateTo]

          If [Get (LastMessageChoice) = 1]

           Enter Find Mode[]

          Set Field [Records::CreatedDate; gDateFrom & "..." & gDateTo]

          Perform Find []

          End If

           

           

          *Edit: fixed typo

          • 2. Re: Search records for range of date
            philmodjunk
              

            Whether you use variables or fields, I think you need to enclose them in Getastext() function calls if you are going to use the range operator "...".

             

            Set Field [yourtable::yourdatefield, Getastext(yourtable::datefield1) & "..." & Getastext(yourtable::datefield2)]

             

            One way to debug these kind of scripted find issues is to run the script once and then choose "modify last find" from the records menu to check and see what values, operators, and exrpessions were actually entered into the fields.

             

             

            • 3. Re: Search records for range of date
              mrvodka
                 I'm not sure if I agree. Unless an evaluation is done then it should be okay.
              • 4. Re: Search records for range of date
                philmodjunk
                   I tried it out and don't seem to need the text conversion. I stand corrected.
                • 5. Re: Search records for range of date
                  lamp
                    

                  Thanks for reply

                  If i create a glebal field how can I search for (creation date)?

                  • 6. Re: Search records for range of date
                    lamp
                      

                    Thanks for reply

                     

                    I used my solution with GateAsText() function call it is still the same now I get error message "No record match this set of find request". there are lots of records with rang i put.

                     

                    • 7. Re: Search records for range of date
                      philmodjunk
                        

                      The getastext should not be the issue (my mistake in mentioning it.). Your original script should find the records but doesn't.

                       

                      Two Suggestions:

                       

                      When you get this message, click the modify find option and check to see what values were actually entered into your record fields. This may give you a clue as to why it isn't working.  

                       

                      Try doing this find by hand. If entering find mode and manually entering the date range finds the records you expect, then there is a difference between your script and the steps where you did this by hand. Try to figure out what's different.

                      • 8. Re: Search records for range of date
                        mrvodka
                          

                        Create two global date fields: gDateFrom and gDateTo

                         

                        Use the script steps that I posted above.

                        • 9. Re: Search records for range of date
                          philmodjunk
                            

                          Now I get it. Some times we miss the obvious. Change your script to:

                           

                          Show Custom Dialog ["Find Records"; "enter the date bellow; records::CreatedDate; records::ToDate]

                          Set Variable [$DateFrom; Value:records::CreatedDate]

                          Set Variable [$DateTo; Value:records::ToDate]

                          Enter Find Mode[]

                           

                          Once you enter find mode, the dates in your two date fields are no longer available. Put the dates in variables first, then enter find mode and do the find. Mr Vodka's solution will also work, because the contents of global fields will remain available after you enter find mode.

                          • 10. Re: Search records for range of date
                            lamp
                              

                            Thanks for all

                             

                            Dear mr_vodka

                            I created two global field as you suggested and it worked ok. now my script is working fine.

                             

                             

                            Thanks

                            Lamp

                            • 11. Re: Search records for range of date
                              truckerclock
                                

                               

                              Mr. Vodka,

                               

                              I am trying to use the steps you specified in my script below but it keeps returning all dates not just the date range I enter. Any ideas?

                               

                              Show Custom Dialog [ Title: "Find Call Reports"; Message: "Enter the date range below"; Buttons: “OK”, “Cancel”; Input #1: Call Reports

                              History::g_StartDate, "Start Date"; Input #2: Call Reports History::g_EndDate, "End Date" ]

                              If [ Get ( LastMessageChoice ) = 1 ]

                              Enter Find Mode [ ]

                              Set Field [ Call Reports History::CR_Date; Call Reports History::g_StartDate & "..." & Call Reports History::g_EndDate ]

                              Perform Find [ ]

                              End If

                              • 12. Re: Search records for range of date
                                mrvodka
                                   They are both global date fields correct? Also try putting in a pause after the set field to make sure that your Call Reports History::CR_Date field is properly being set.


                                • 13. Re: Search records for range of date
                                  truckerclock
                                     The Call Reports History::CR_Date field was not global so I have changed that and added the pause. Now when I run the script and enter the date range I get a message saying "There are no valid criteria in this request.". If i click continue it finds all records with the date that I entered for the start date and that is it.
                                  • 14. Re: Search records for range of date
                                    mrvodka
                                       No Call Reports History::CR_Date should NOT be a global. The start and end date fields should be.
                                    1 2 Previous Next