8 Replies Latest reply on Oct 23, 2015 7:35 AM by LynetteLejeune

    Date Search Script Help

    LynetteLejeune

      I have a database with several fields Two of the fields are "Appraiser" (A text field) and "Paid" (a date field).  I set up a layout called AppraiserPaid.  I set up two separate fields called AppraiserInvoiceBEGINDate and AppraiserInvoiceEndDate.  How do I write the script that I can then assign to a button that will pull the AppraiserInvoicebEGINDate and the AppraiserInvoiceEndDate from the paid field where the data is actually located?  I have it where it pulls the correct appraiser but not the dates.  Thank You for any help and guidance.

        • 1. Re: Date Search Script Help
          indiguy

          Here is my script, which search between start date and end date..hope it will help you..

          ------------------------------------

          Enter Find Mode [ ]

          Set Field [ Orders::Date ; Orders::Start_Date & "..." & Orders::End_Date ]

          Perform Find [ ]

          If [ IsEmpty ( Orders::Start_Date ) ]

          Show Custom Dialog [ Title: "Start date"; Message: "Start Date must have a value. Please reenter"; Default Button: “OK”,

          Exit Script [ ]

          If [ IsEmpty ( Orders::End_Date ) ]

          Show Custom Dialog [ Title: "End Date"; Message: "End Date must have a value. Please reenter"; Default Button: “OK”,

          Exit Script [ ]

          If [ Orders::Start_Date > Orders::End_Date ]

          Show Custom Dialog [ Title: "Invalid Date Range"; Message: "End Date cannot occur before Start Date. Please reenter";

          Exit Script [ ]

          -------------------------------------------

           

          • 2. Re: Date Search Script Help
            erolst

            Wouldn't it be a good idea to check the input on plausibility before you use it?

             

            It should also be pointed out that this only works if the two input fields are global fields because otherwise, they will be empty in Find mode; but then they usually should be globals.

             

            Here's another version:

             

            Set Variable [ $begin ; Orders::gSearchBegin ]

            Set Variable [ $end ; Orders::gSearchEnd ]

            #

            Set Variable [ $errorMessage ; Case ( not Count ( $begin ; $end ) ; "Please fill in both date fields." ; IsEmpty ( $begin ) ; "Please fill in the start date." ; IsEmpty ( $end ) ; "Please fill in the end date." ; $begin > $end ; "Start date must be before end date." )

            #

            If [ not IsEmpty ( $errorMessage ) ]

              Show Custom Dialog [ Title: "Problem with the search dates"; Message: $errorMessage ]

              Exit Script [ ]

            End If

            #

            Set Error Capture

            Enter Find Mode [ no pause ]

            Set Field [ Orders::Date ; $begin & "..." & $end ]

            # [ if required, add a Set Field for an appraiser type ]

            Perform Find [ ]

            #

            If [ not Get ( FoundCount ) ]

            # Show Dialog etc.

            End If

            • 3. Re: Date Search Script Help

              There is not a field for Start Date and then a separate field for end date.  All the dates are entered in the same field Paid.  Would this change the script any?

              • 4. Re: Date Search Script Help
                erolst

                Guest wrote:

                There is not a field for Start Date and then a separate field for end date.

                 

                Didn't you say:

                Guest wrote:

                I set up two separate fields called AppraiserInvoiceBEGINDate and AppraiserInvoiceEndDate.

                ?

                 

                Look at the script; you enter the date range using these two fields (which really should be of type date, and set to global storage); if they pass the checks, a date range is generated from them, used to query the single field Paid.

                • 5. Re: Date Search Script Help

                  I am going to print both of the scripts and enter in.  I will let you know how they work.  Thank you so much for your help and patience

                  • 6. Re: Date Search Script Help
                    LynetteLejeune

                    The picture is what I ended up with after copying from an existing script on a different database that the script works on.  The only problem I have now is dates are going back for all records not the dates I enter.

                    • 7. Re: Date Search Script Help
                      erolst

                      LynetteLejeune wrote:

                       

                      The picture is what I ended up with after copying from an existing script on a different database that the script works on.

                       

                      Well, that script seems to haven't been looked at for over a decade …

                       

                      Try

                       

                      Set Variable [ $dateRange ; Appraisal Database Converted::Begin & ".." & Appraisal Database Converted::End ]

                      Set Variable [ $appraiserLookup ; Appraisal Database Converted::AppraiserLookup ]

                      Enter Find Mode

                      Go to Layout [ Columnar ]

                      Set Field [ Appraisal Database Converted::Paid ; $dateRange ]

                      Set Field [ Appraisal Database Converted::Appraiser ; $appraiserLookup ]

                      Set Error Capture [ on ]

                      Perform Find [ ]

                      If [ not Get ( FoundCount ) ]

                        # [ etc. ]

                      • 8. Re: Date Search Script Help
                        LynetteLejeune

                        It feels like I have been working on this forever.:) I am a complete newbie when it comes to scripts, so thanks for all the help.  I can't figure out how to make the # [ etc.].  Also would I just delete the "End If"