7 Replies Latest reply on Jun 22, 2017 1:31 AM by ben2017

    Showing certain records based on a date

    ben2017

      Hi,

       

      I have a list layout displaying all records. One field on this layout is a date field called PSV Date, how can I check if any record(s) has the PSV date field set so it falls within the next 30 days? 

       

      I have the PSV field set as a date. I also have another field not on the list layout,(PSV30) which is a calculation get(current date) + 30 to test against.

      Here is the attempted script

      I would like the find to return only the records with a PSV date in the next 30 days in a found set, however I'm getting no results. I've checked and I do have a couple of records with dates within the next 30 days.

       

      Another thing is that the find seemly moves to a different layout (which is based on the same table), than the list layout I start on which is why I have explicitly go to it at the start of the script. I'm not sure why it does that.

       

      Any help greatly appreciated, thanks!

        • 1. Re: Showing certain records based on a date
          Mike_Mitchell

          Is your calculation field for Get ( CurrentDate ) + 30 set to unstored?

           

          If you create a calculation based on one of the Get functions (unless you do some additional things), it will store the value that was present when the Manage Database dialog was closed. It won't ever update, which is why it needs to be unstored.

           

          As far as the layout changing, the only reason I can think of offhand for that (assuming the script you have above is the entire script) would be a Script Trigger on the layout that's activating another script.

          1 of 1 people found this helpful
          • 2. Re: Showing certain records based on a date
            philmodjunk

            When you enter find mode, every field except those with global storage specified go blank.

             

            THus, the set field step in your script doesn't work as it creates this expression as find criteria:

             

            ...

             

            There will be no date before or after the three dots.

             

            Do it this way if you want to find all records where PSV Date is in the interval from today to 30 days in the future:

             

            Go To Layout ["Vehicles" ; (Vehicles) ]

            Enter Find Mode [Pause:off]

            Set Field [ Vehicles::PSV Date ; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 30 ]

            Set Error Capture [on]

            Perform Find [  ]
            Sort

             

            There is no need for Show All records, it has no effect on what records are found in the script. Set Error Capture keeps the standard error dialog from interrupting the script should no records be found in the 30 day interval. YOu can use either Get ( LastError ) or Get ( FoundCount ) to test for cases where no records were found and you can then display your own custom dialog if you want when that happens.

            1 of 1 people found this helpful
            • 3. Re: Showing certain records based on a date
              SteveMartino

              Your first step, go to layout, is it going to the correct layout?

              Secondly, you don't need show all records.

              A simple script which may work

              Enter Find Mode [ ]

              Set Field [Vehicles::PSV Date; Let( [

                                                                      @start=Get(CurrentDate);

                                                                      @end=Get(CurrentDate)+30;

                                                                      @date=@end & "..." & @start

                                                                    ];

                                                                      @date

                                                                    ) ]

              Perform Find [ ]

              Sort Records

              • 4. Re: Showing certain records based on a date
                SteveMartino

                philmodjunk  Can you put this expression  Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 30

                into a date field in find mode?  When I do it I get an error message.  Just wondering

                Thanks

                • 5. Re: Showing certain records based on a date
                  philmodjunk

                  I use it all the time. From what I see, it looks like you have your get functions enclosed in quotes and thus the expression is inserted as text instead of being evaluated and the results entered into the field.

                  • 6. Re: Showing certain records based on a date
                    ben2017

                    Thanks for the reply, I did have it as unstored and you were right, there was a script step changing layouts in a different script, so thanks!

                    • 7. Re: Showing certain records based on a date
                      ben2017

                      Thanks all for the replies, I'm sorted now.