7 Replies Latest reply on Feb 19, 2009 12:28 PM by Orlando

    Using date field to search

    deliciousapple

      Title

      Using date field to search

      Post

      I would like to have two drop down calendar fields on my form.  I would like to use one for a start date, and the other for the end date in a search.

       

      Any idea on how to do this? 

        • 1. Re: Using date field to search
          Orlando
            

          Hi DeliciousApple

           

          Assuming you have the fields setup and the are called StartDate and EndDate and you are performing your search with a script, then your script will need to do the following:

           

          #
          Set Error Capture [ On ] 
          Enter Find Mode [] // Deselect Pause
          Set Field [ TABLE:: Date ; TABLE::StartDate & "..." & TABLE::EndDate ] 
          Perform Find []
          #
          If [ Get ( FoundCount ) = 0 ]
          Show Custom Dialog [ "No record found" ] 
          End If
          #  

           

          I hope this helps





          • 2. Re: Using date field to search
            deliciousapple
              

            Hmm no luck so far,

             

            Is there a find request in the Perform Find?

             

             

            • 3. Re: Using date field to search
              Orlando
                

              What result are you getting?

               

              The find request is manually set in the two steps before the Perform Find step.

               

              One thing to check is if the Start and End date fields are globals or not. If they are not then modify your script slightly to set the search date before the Enter Find Mode step

               

              #
              Set Error Capture [ On ] 
              Set Variable [ $SearchDate ; TABLE::StartDate & "..." & TABLE::EndDate ]
              Enter Find Mode [] // Deselect Pause
              Set Field [ TABLE:: Date ; $SearchDate ] 
              Perform Find []
              #
              If [ Get ( FoundCount ) = 0 ]
              Show Custom Dialog [ "No record found" ] 
              End If
              #  

               

              Let me know how you get on with this. 






              • 4. Re: Using date field to search
                deliciousapple
                  

                Voila, I set the "date_start" and "date_end" fields to global and it worked like a charm.

                 

                Ok Orlando, now do you think there could be a problem with setting these to global??

                 

                 

                Now the next step, I have one other problem that I asked for help on and a gentleman named David helped me.  Its another search script based on a value list.  I would now like to combine these to searches. 

                 

                Anyway a huge thanks.

                • 5. Re: Using date field to search
                  Orlando
                    

                  Regarding the two fields being globals, no problems if they are only being used for searching, not storing data in relation to a record.

                   

                  For your next step,looking through your previous posts I assume its the "Value list and Script trigger" post. If so are you trying to tie this into the same script or the same process of being triggered by a Script Trigger?

                  • 6. Re: Using date field to search
                    deliciousapple
                      

                    Thank you with regards to the global field reply, and yes it's just used in a find, does not store any data, so I should be fine.

                     

                    Yes  "Value list and Script trigger" is the post I was referring to.

                     

                    It could be with the script trigger or a button, just want it to be clean and easy. 

                     

                    My goal is to have a report that has starting and ending date fields on it.  so I can search by date.  But I also want to be able to search by product at the same time. 

                     

                    Not quite sure how to accomplish this, but with the help from you and others I think I am close.

                    • 7. Re: Using date field to search
                      Orlando
                        

                      Simple approach will be add in an additional Set Field script step, Set Field [ TABLE:: ProductType ; TABLE:: x-pop ] above the perform find step.

                       

                      This will include the productType criteria into the search, and if its empty then it wont affect the date search.

                       

                      Now the question is how you want to hand the search if any date field is empty. as is if the start date is empty then it will find all records before the EndDate, and if the end date is empty then will find all records dated after the StartDate. 

                       

                      You can be as simple or clever as you want it to be, so for example if you only input a start date it will search on that date, if you only input an end date it will search all records before that date and both is as you have it running now. If this is how you want to do it then change the Set Field [ TABLE:: Date ] Calculation to:

                       

                      Let ( [
                      StartDate = TABLE::StartDate ;
                      EndDate = If ( IsEmpty ( TABLE::EndDate ) ; "" ; "..." & TABLE::EndDate )
                      ] ; // Start of calculation
                      StartDate & EndDate

                       

                      If there is any other variation you want help in achieving with this then let me know. 

                       

                      I would also opt for a button instead of trigger, since the user may only search on one field or all, so how will the trigger know when to run the search script and when not to.