11 Replies Latest reply on Jun 6, 2012 6:40 PM by greengk2fi

    Getting the Last 90 Days in a Simple Script

    greengk2fi

      I can't believe I'm having trouble with somethin so simple. I've written a script to open a window, go to a layout, find a date range, and sort records. It all seems to work fine except for the date range. To find the date range I have the simple script below.

      Perform Quick Find [tblService::ServiceDate ≥ (Get ( CurrentDate ) - 90)]

       

      Yet for some reason, I'm not getting the appropriate records in the results.

       

      When I access the layout itself and find based on a date greater than say 2/24/2012, it all works fine as well. It must be something in my script; but I thought days could be subtracted from a date without any conversion.

       

      Any help is appreciated.

        • 1. Re: Getting the Last 90 Days in a Simple Script
          ch0c0halic

          greengk2fi may have found Quick Find isn't simple,

           

          I suggest you Use the "Perform Find" step instead. You can see exactly what was entered for the criteria by using the "Modify Last Find" menu item.

           

          For example:

          Enter Find Mode

          Set field ["tblService::ServiceDate" ; "≥" & Get ( CurrentDate ) - 90 ]

          Perform Find

           

           

          Uncheck the "Specify ..." (both Find ... steps) and "Pause" checkboxes.

          • 2. Re: Getting the Last 90 Days in a Simple Script
            greengk2fi

            Thanks for your help.  I seem to have an issue entering anything other than a date as a find on a date field.  If I run the find without a calculation of 90 days previous; but simply the actual date 90 days ago, I have no problems.  When enter find mode or write a script that requires current date - 90, I get the error saying I must use a valid date.  In my script that is why I went to quick find instead of find.  In quick find, I could ask it to look for a calculation in the date field.  I didn't get the right results; but I wasn't getting the date format error.  When I use the find mode, calculations do not seem to be available.  I can easily find a range based on a specific date; but not today minus 90.  Clearly FMpro must do what I'm trying; but I must be doing something wrong.  Below is the error I get when I simply try to do things via find either in the script or in find mode.

            http://www.gregknollmeyer.com/files/FMproError.png

            • 3. Re: Getting the Last 90 Days in a Simple Script
              jbante

              The Perform Find script step doesn't use the calculation engine, and so it wont resolve ( Get ( CurrentDate ) - 90 ) to a date. That's why it helps to use a separate Set Field script step.

              • 4. Re: Getting the Last 90 Days in a Simple Script
                MikeRauch

                Review ch0c0halic's post again.  The steps are...

                 

                Enter Find Mode (no pause, no criteria)

                Set Field [...]

                Perform Find (no criteria)

                 

                This construction is much more flexible than what can be done by specifying criteria within the find commands themselves.  I build all scripted finds using this construction.  It is easy to understand if you think of it as doing a find like you would without a script.  Anything you can do with a manual find can be done with this method.  Manual typing = Set Field, manual new request = New Record, manual Omit = Omit Record, constrain, extend, etc.   Everything between Enter Find Mode and Perform Find happens in Find Mode.

                 

                Hope this helps.

                 

                Mike Rauch

                • 5. Re: Getting the Last 90 Days in a Simple Script
                  Stephen Huston

                  You can set a Variable to the correct date string using the calculation engine, then you can set the the date field in Find Mode (middle step in Mike,s script sample) to the value of the Variable. This will allow the script to dynamically set a date range based on the current date each time the script is run.

                  • 6. Re: Getting the Last 90 Days in a Simple Script
                    jbante

                    Another advantage to this approach to scripted finds is that the resulting script is more readable, since the next developer doesn't have to open the Perform Find [] step to see what it's doing.

                    • 7. Re: Getting the Last 90 Days in a Simple Script
                      greengk2fi

                      Thank you all for taking pity on me.  I think I've got it.  I truly appreciate the help.  Even with a bit of training I still feel like I have Swiss Cheese knowledge of FMpro.  Generally I love FMpro (particularly compared to Access).  But I find myslef getting hung up on some little thing like this from time to time.

                       

                      Cheers,

                      Greg

                      • 8. Re: Getting the Last 90 Days in a Simple Script
                        Stephen Huston

                        Ain't this Forum great? Free tech support for us all from whoever can help! And we all learn in the process.

                        • 9. Re: Getting the Last 90 Days in a Simple Script
                          greengk2fi

                          I've just gotten back to this particular project and found I'm still having an issue around finding the date range within this particular script. I'm wondering if there is some issue about date field vs. calculation result defaulting to text.  When I do the calculation within the script to find the right date range, I don't seem to have a way of choosing the type of result.  It simply says "result must be text".  I'm wondering if that is causing the issue and if so how to resolve text/date in this.  Here are the pertinent details.  Any help is greatly appreciated.

                           

                           

                          The script:

                          http://gregknollmeyer.com/files/Script.jpg

                          The calculation within the script showing result must be text

                          http://gregknollmeyer.com/files/BadDate.jpg

                           

                          The field type of tblService::ServiceDate as a Date

                          http://gregknollmeyer.com/files/FieldType.jpg

                           

                          The faulty results from this script showing out of range dates

                          http://gregknollmeyer.com/files/Screen%20Shot%202012-06-06%20at%207.42.40%20PM.png

                           

                          The correct results when I simply navigate to the layout and do a find on the service date field as ">3/6/2012"

                          http://gregknollmeyer.com/files/Screen%20Shot%202012-06-06%20at%207.43.26%20PM.png

                          • 10. Re: Getting the Last 90 Days in a Simple Script
                            erolst

                            The main error is in the syntax of your Set Field step; instead of specifying the target field with the Specify Target Field button, you've included the name of the target field as text literal in the calculation (and the remaining expression isn't correct, either). See attached screenshot.

                             

                            Use

                             

                            Set Field [ tblService::ServiceDate ; ">" & Get ( CurrentDate ) - 90 ], or, as variation on a theme:

                             

                            Set Field [ tblService::ServiceDate ; Get ( CurrentDate ) - 90 & "…" & Get ( CurrentDate ) ]; this format finds ranges with arbitrary start and end dates.

                            • 11. Re: Getting the Last 90 Days in a Simple Script
                              greengk2fi

                              Yep, you're right of course.  I didn't realize I had to separate the target and the calc.  Again, thanks for the help.

                               

                              Greg