5 Replies Latest reply on Dec 29, 2009 1:57 PM by neilly1

    Script to display records before an input date

    neilly1

      Title

      Script to display records before an input date

      Post

      I'm stuck.  Brand new user.  I want to create a script that will allow the user to input a date and then it will find and display all the records on the database where the lastpaydate is earlier than the input date.

       

      I can see how to do it in Find Mode but I can't see how it works with a script.

        • 1. Re: Script to display records before an input date
          philmodjunk
            

          There is more than one way to do this and what kind of field you use for the input date can make a difference.

           

          If you use a global date field for the input date, your script should look something like this:

           

          Enter Find Mode[]

          Set Field [YourTable:: PaydateField ; " < " & YourTable::YourGlobalInputField]

          Set Error Capture [on]//keeps the records not found dialog from interrupting your script if no records are found

          Perform find[]

           

          If you don't use a global field, you should use Set Variable to copy the date into a variable before entering find mode and then refer to the variable instead of the input field in the second step of the above script.

          • 2. Re: Script to display records before an input date
            ninja
              

            Howdy neilly,

            Welcome to the forum.

             

            In the script, you'll actually script the steps you used manually in find mode.

             

            Set Variable [ $date ; Datefield]              ***traps the date entered by the user

            Enter Find Mode                                  ***obvious function

            SetField [ DatetoSearch ; "<" & $date ]

            Set ErrorCapture [on]                          ****** blacks the "No records match..." dialog box

            Perform Find []                                  *** obvious function

            If [ Get(FoundCount) = 0 ]

               Show Custom Dialog                        ****What do you want it do say if there are no matching records?

            Endif

             

            Is this what you're after?

             

            Note that the If ...EndIf and ErrorCapture steps are bells and whistles, not mandatory.

            • 3. Re: Script to display records before an input date
              neilly1
                

              I'm doing something stupid.  I've spent two days trying to make this work and nothing I've tried does the job.  Let me give you all the details.  I've got a field on my database called TickitEnglish::Last Pay Date.  It is defined as a date field.  What I want is a script that will display all the records found where the date in TickitEnglish::Last Pay Date is blank or less than a date to be input by the user.

               

              Reading your script it seems to me that you haven't included anything to input the user's date.  I am correct?

               

              Do I use Show Custom Dialog or a parameter or what to get the input date?

               

              I've tried following your script but when I try to input the SetField command it objects to my format, expecting a calculator instead of ; or " or anything else I try to put there.

               

              Also, do I need to specify anything in the Perform find [] command?

              • 4. Re: Script to display records before an input date
                ninja
                  

                Howdy neilly,

                 


                neilly wrote:

                Reading your script it seems to me that you haven't included anything to input the user's date.  I am correct?


                On mine, it's the "Datefield" reference in the SetVariable step.  I've assumed that that field would be set prior to launching the script.

                 

                On Phils, its the "YourTable::YourGlobalInputField" reference.  He assumed the same from the looks of it.


                neilly wrote:

                Do I use Show Custom Dialog or a parameter or what to get the input date?


                Custom Dialog is certainly an option.  Either type the date into the above mentioned field then launch the script, or type nothing in, and have a ShowCustomDialog step which prompts the user to enter a date.  If you go this route, simply reference the field mentioned above in the ShowCustomDialog settings.

                 

                What was assumed is that you would have a global field that would be on the layout and have a date typed into it prior to launching the script.  If you'd rather do it another way, please state your desired approach.


                neilly wrote:

                 

                I've tried following your script but when I try to input the SetField command it objects to my format, expecting a calculator instead of ; or " or anything else I try to put there.


                The ; is put there by FMP, you don't have to type it.  In the Setfield setup, click "Specify..." Target field and choose TickitEnglich::LastPayDate, then click "Specify..." Calculated result and type in:

                 

                "<" & YourTable::YourGlobalInputField      {The field reference shoud be chosen from a list in the top left pane}

                 


                neilly wrote:

                 

                Also, do I need to specify anything in the Perform find [] command?


                Nope.  You could build a find function a different way which would add info here, but that's basically what we're doing with the EnterFindeMode and Setfield steps...with this script setup you just need the "do it" command...i.e. Perform Find[].

                 

                Let us know how you progress!





                • 5. Re: Script to display records before an input date
                  neilly1
                     Thanks for your help