3 Replies Latest reply on Feb 12, 2013 3:04 PM by philmodjunk

    Finding dates based on start date



      Finding dates based on start date


           Hello all,

              I use my database for inventory management. I have a field for purchace date on all of the records. This is a date feild. I know that these item each come with a 3 year warranty. I would like to be able to do a search or run a script that will find all records that are 3 years or more past the purchase date so that I can see what has run out of warranty. Can anyone explain how I would go about this. I would greatly appreciate it. 

           Thank you.

        • 1. Re: Finding dates based on start date

               You can find all records with dates before the year 2012 if you use <2012 as your criteria.

               So this expression computes the "3 years earlier than today" criteria:

               "<" & Year ( get ( CurrentDate ) ) - 3

               You can use this criteria in a scripted find such as those illustrated in this thread: Scripted Find Examples

          • 2. Re: Finding dates based on start date

                 Well, what I am actually trying to get is FileMaker to look at the "Purchase Date" field of each record then then find any records that are equal to or greater than 3 years beyond that purchase date. To make things a little simpler I just create two more fields for my records. One is a "Warranty Period", where the warranty period, in days can be entered, then another field titled "End Date", which is a calculation of the "Purchase Date" plus the "Warranty Period" days. This works great and gives me the warranty end date. Now I would like to run a script that will bring up a list of records that would basically looks like this. End Date ≥ (Purchase Date + Warranty Period).

                 So for example

                 Purchase Date = 01/01/2010

                 Warranty Period = 1096

                 End Date = 01/01/2013

                 So I would want to run a report with the equation I stated above and the above example should come up as a found record. Let me know if you have any tips to make that work.

            • 3. Re: Finding dates based on start date

                   Then you would use this script:

                   Enter Find Mode[]
                   Set field [YourTable::EndDate ; "<" & Get ( CurrentDate ) ]
                   Set Error capture [on]
                   Perform Find []