6 Replies Latest reply on Jun 9, 2013 4:17 AM by kaiviti

    Script for filtering records by specific fields and within one year

    kaiviti

      Hi All,

       

      I am sure this has already been answered so, if you know where it is please point me in the right direction.

       

      I am trying to write a script that finds records based on three criteria:

      The first criteria searches for "Airplane" in the "Transport" field and "Start of Swing" in the "Reason" Field.

      The second criteria also searches for "Airplane" in the "Transport" field but "End of Swing" in the "Reason" Field.

      The third criteria searches for all records from the found set within one year from todays date.

       

      I can easily get a Perform Find script to return the first two criteria. I am having trouble getting the third to work.

      I have tried a find specifying the first two criteria and then tried to constrain the found set using an "insert calculated result" but it doesn't work.

      The calculated result formula is ">=" & Get(CurrentDate) - 365.

      I also tried to add a "Modify Last Find" using the same formulae but, it doesn't work.

      If I do a find with the formula only, it works.

       

      I need to be able to use all three criteria at once. Can anyone help please?

        • 1. Re: Script for filtering records by specific fields and within one year
          taylorsharpe

          I'm not sure I understand, but why not do the first find and then do an extend with the 2nd find.  Then do a constrain with the date field being a formula of:

           

          Let ( [

          F1 = Get ( CurrentDate ) ;

          F2 = Date ( Month ( F1 ) ; Day ( F1 ) ; Year ( F1 ) - 1 )

          ] ;

          ">=" & F2

          )

          • 2. Re: Script for filtering records by specific fields and within one year
            kaiviti

            Thanks for your input.  The formula you have described does the same as the one I used:

             

            ">=" & Get(CurrentDate) - 365

             

            Maybe I didn't explain what I was trying to achieve well enough.  I have a database, which records all the travel information for an employee while on a tour of duty.  The travel items are not limited to airplanes and I have a filed with a drop down menu for assigning thetype of transport. Each travel item is for a different reason and again a drop down menu is used to assign the reason  I want to filter these records so that the found set only contains airplane travel for the start and end of the tour and within the previous year.


            The way I had it set up was:

             

            Enter Find Mode [Restore]

            Constrain Found Set

            Insert Calculated Result [Select; Travel::travel_start_date ; ">=" & Get(CurrentDate) - 365]

            Perform Find[]

            Sort Records [Restore; No dialog]

             

            The "Enter Find Mode [Restore]" had the "pause" unchecked and the "Specify find requests" checked.

             

            The find requests were:

             

            Travel::Transport [Airplane] ; AND Travel::Reason [Start of Swing]

            Travel::Transport [Airplane] ; AND Travel::Reason [End of Swing]

             

            This set up didn’t seem to work.  I tried several other combinations and finally came up with the correct one.  This is how it is now set up:

             

            Enter Find Mode []

            Insert Calculated Result [Select; Travel::travel_start_date ; ">=" & Get(CurrentDate) - 365]

            Perform Find []

            Constrain Found Set [Restore]

            Sort Records [Restore; No dialog]

             

            The find requests are the same but now stored in the “Constrain Found Set”  I guess the whole problem was just getting the sequence right.  Anyway, it all working now.  Thanks for your help.

            • 3. Re: Script for filtering records by specific fields and within one year
              BruceRobertson

              Explicit scripted finds are easier to understand.

              With them, you do not use "restore"


              #

              Enter Find Mode [ ]
               
              Set Field [ Travel::Transport; "Airplane" ]
               
              Set Field [ Travel::Reason; "Start of swing" ]
              New Record/Request
                Set Field
              [ Travel::Transport; "Airplane" ]
               
              Set Field [ Travel::Reason; "End of swing" ]
              Perform Find [ ]
              # Now constrain the found set
              Enter Find Mode [ ]
              Set Field [ Travel::travel_start_date; ">=" & Get(CurrentDate) - 365 ]

              Constrain Found Set [ ]

              • 4. Re: Script for filtering records by specific fields and within one year
                Malcolm

                This is how it should be:

                 

                Enter Find Mode

                Insert Calculated Result [Select; Travel::travel_start_date ; ">=" & Get(CurrentDate) - 365]

                Constrain Found Set      []

                Sort Records

                 

                Malcolm

                • 5. Re: Script for filtering records by specific fields and within one year
                  LyndsayHowarth

                  You can do this by a Find or by using a Relationship.

                  Using a Relationship, there is no script to fire... unless you want to go to related records where the records can be already sorted.

                  If this is a permanent value for Transport; 'Airplane', then either Find or Relationship will do, but Relationships are superior when you want to be able choose 'Car' to get a different report.

                  A self Relationship in the Travel table can achieve a dynamic report without going anywhere else.

                   

                  - Lyndsay

                  • 6. Re: Script for filtering records by specific fields and within one year
                    kaiviti

                    Wow, this is all good stuff.  I am still fairly new with all this so still finding my way around.  I haven't got into explicit scripts yet.  It certainly looks easier to understand what is going on.  Another thing I am looking into learning is how to get user input to select the fields for filtering.  Lynsay hinted on with by saying we may want to choose "Car" as the selection rather than "Airplane".  I may also want to change the reporting period from "yearly" to "quarterly" etc.  That may come in another thread.  Thanks to all of you for your help.