10 Replies Latest reply on Nov 3, 2009 4:37 PM by StillLearning

    Using Current Date in a Script (Filemaker Pro 9)



      Using Current Date in a Script (Filemaker Pro 9)


      Hello everyone. I'm using Filemaker Pro 9 on a Mac (OS X 10.6.1) and I'm trying to create some kind of Reminder (To Do List) for myself. I'm pretty good at setting up a database, but I'm having a difficult time with a script.


      I want to create a script that looks at my list of tasks and shows me the ones that are overdue. Apparently I'm not understanding how to use Get(CurrentDate). Here is what I have:


      Go to Layout ["Overdue Tasks" (Weekly Tasks)]

      Go to Field [Weekly Tasks::Date Due]

      Enter Find Mode [Pause]

      Set Field [Weekly Tasks::Date Due < Get(CurrentDate)]

      Perform Find [] 


      Needless to say, it does not work. It takes me to the correct layout and puts the cursor in the correct field and stops. The only option then is to click "cancel" or the "continue" button for Scripts. And that gives me a box that says: "There are no valid criteria in this request. Type a valid request before clicking find."


      I've been trying to follow the steps I would use if I was entering all of this myself. (I even know that I can go into Date Due field and enter <// to get my overdue tasks.)


      I'm spending way too much time on this little script (my tasks are losing out to my frustration!).


      Thanks for any help. 

        • 1. Re: Using Current Date in a Script (Filemaker Pro 9)

          Howdy Still Learning ... aren't we all.


          Still Learning wrote:


          Go to Layout ["Overdue Tasks" (Weekly Tasks)]

          Go to Field [Weekly Tasks:: Date Due]

          Enter Find Mode [Pause]

          Set Field [Weekly Tasks:: Date Due; "<" & Get(CurrentDate)]

          Perform Find [] 


          See how that treats you.  You were telling it to put a date "less than today" into the field and then perform a find...it didn't know which day you meant.  What you wanted to put into the field is the less-than symbol...and then todays date.




          EDIT: Oh, if it's working OK now...you might consider taking out the [pause] part (if it makes sense in your task to do so).

          • 2. Re: Using Current Date in a Script (Filemaker Pro 9)
               Darn it--I was hoping this would work, but maybe you're getting me closer. When I tried modifying "Set Field" as you suggested I got this message: (An operator (e.g., +, -, * ...) is expected here.
            • 3. Re: Using Current Date in a Script (Filemaker Pro 9)

              Did you put under the calculated results section (not under repetition):


              "<" & Get(CurrentDate)




              • 4. Re: Using Current Date in a Script (Filemaker Pro 9)

                Yes, I'm putting it in Calculated result: Specify and that opens another window that shows my fields, Operators and functions. I even tried creating a field that holds the current date and I can't get that to work!



                • 5. Re: Using Current Date in a Script (Filemaker Pro 9)

                  How can this be so difficult??? Now I'm trying a different approach and not even that works for me. 


                  I figured out how to create a calculated field in my table that's called Overdue:


                  Unstored, = If ( Date Due < Get (CurrentDate ); "Overdue") 


                  All I'm trying to do is create a script that will Find and Show the overdue tasks. I've searched all over and found several "solutions" that seem to work fine for the person who submitted the question, but I cannot get it to work for me!!!! (I'm keeping this clean!)


                  Here's my script:

                  Enter Find Mode []

                  Set Field [Weekly Tasks::Overdue = "Overdue"]

                  Perform Find []


                  I get "No records match this set of find requests." But if I do the Find "manually" it works. 


                  • 6. Re: Using Current Date in a Script (Filemaker Pro 9)



                    Let's walk it through one step at a time.


                    On a given layout...keep the whole discussion on this one layout...you manually enter find mode, type in the word OVERDUE, hit the enter key and it works.  This is good.


                    This returns how many records in the found set?

                    Please verify that the field you typed "Overdue" (no quotes) in is WeeklyTasks:: Overdue.


                    Scripting that same process:


                    GoToLayout [the layout we're talking about]

                    Pause Script [indefinitely] ### verify that you're on the right layout

                    Enter Find Mode []

                    Pause Script [indefinitely] ### verify that you're now in find mode

                    Setfield [WeeklyTasks:: Overdue ; "Overdue"] ###with quotes,                      

                    Pause Script [indefinitely] ###verify that "Overdue" (no quotes) shows up in the right field.

                    Perform Find[] ### how many records show up


                    Yes, this is the script you have in your last post...and with proper syntax it will do exactly what you want.


                    Futher verify:

                    - you are running the script above (if linked to a button...did you update the button to point to the right script?)

                    - you actually have overdue things (how many records were found when done manually?)

                    - you selected the correct field in the SetField command in the script.


                    Please let us know how this troubleshooting progresses.

                    • 7. Re: Using Current Date in a Script (Filemaker Pro 9)

                      I think you are missing the snytax of the Set Field [] script step. Take a look at how it looks with the posts that Ninja and I made.


                      You wrote

                      Set Field [Weekly Tasks:: Overdue = "Overdue"]  ( space put in to get rid of emoticon )



                      No it should look like:  Set Field [Weekly Tasks:: Overdue; "Overdue"]


                      In the Set Field [] script step, you will see two options; "specify target" and "calculated result"


                      You specify the target field:  Choose -  Weekly Tasks:: Overdue

                      You specify the calculation:  Enter -   "Overdue"


                      If you get this working then you can go back and try the earlier method suggest as well.


                      BTW Ninja's directions will indeed help you troublshoot if need be.






                      • 8. Re: Using Current Date in a Script (Filemaker Pro 9)

                        Thank you!!! This has been driving me crazy. I did have to make one change, however. For whatever reason, I could not do "Set Field" the way you showed it. Whenever I did that I got this message:  (An operator (e.g., +, -, * ...) is expected here.


                        So I had to add one more step, which was "Go to Field" and select Overdue as the target field (I used the Overdue for both the field name and its content). Then I used "Set Field" and used the calculated result of "Overdue" in quotes.


                        I realize this is pretty minor in the world of database scripting, but I sure do appreciate the help from you and Mr. Vodka (love that name).


                        Thanks again. 

                        • 9. Re: Using Current Date in a Script (Filemaker Pro 9)
                             You dont need a Go to Field for Set Field []. Are you sure that you are selecting the target field in the Set Field [] options correctly?
                          • 10. Re: Using Current Date in a Script (Filemaker Pro 9)
                               Duh! I see what you were trying to tell me. I wasn't doing "Specify Target Field" and that makes complete sense. Thank you for clarifying that!