4 Replies Latest reply on Jul 16, 2013 3:43 AM by teclo

    Only show records that fall within the next six months

    teclo

      Title

      Only show records that fall within the next six months

      Post

           I have a contact database of students where every student has a start date. I have then a simple calculated field of their estimated graduation which adds a number of days to the start date.

           Now I want to have a report that only displays the students whose graduation date is coming up within the next six months in order to plan ahead a bit. I had this all set up in Access but had to switch to FileMaker and I'm lost. When I try to use a find criteria in the script I saved from the report, it says that I have to enter a valid date (eg 2013/07/10), but I want to calculate it from the current date so that the script is always up to date. I can't get it to work with Get(CurrentDate) nor with the "//" function found under "insert operator".

           Should I even do this as a perform find in my script? or would it be better to use some sort of "if" function? I use FileMaker Pro. I guess my ideal script would be  something like GraduationDate < (Get(CurrentDate) + 180)

        • 1. Re: Only show records that fall within the next six months
          philmodjunk

               First, see this thread for examples of scripted finds. You'll find examples where a set field step is used to specify a calculated value as find criteria: Scripted Find Examples

               then you can use a set field step such as:

               Set Field [ YourTable::dateFIeld ; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 180 // approximately 6 months ]

               Set Field [ YourTable::DateFeild ; Get ( CurrentDate ) & "..." & Date ( Month ( Get ( CurrentDate ) + 6 ; Day ( Get ( CurrenDate ) ) ; Year ( Get ( CurrendDate ))) // exactly 6 months from today ]

               Note: there are a lot of parenthesis in the second example and I could easily of omitted one by accident when I typed this up.

          • 2. Re: Only show records that fall within the next six months
            teclo

                 thanks a lot for your answer. maybe I'm just not thinking straight here, but I now have the script according to this: Set Field [MyTable::MyField; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 180] and when I run it, I get an error message saying that this field is not modifiable.

                 I would assume is because it's a calculation field? a quick search had me suggesting that I should uncheck "Prohibit modification of value", but I can't find it. any help to point me in the right direction would be much helpful!

            • 3. Re: Only show records that fall within the next six months
              philmodjunk

                   Please take another look at the example scripts from the thread I suggested in my last post.

                   The set field step must be executed while in Find mode, not Browse mode.

              Enter FInd Mode []
                   Set FIeld[....
                   Set Error Capture [on]
                   Perform Find []

                   is the basic outline used for each of the scripted finds in that thread.

              • 4. Re: Only show records that fall within the next six months
                teclo

                     thanks a lot, that was it.