1 Reply Latest reply on Oct 2, 2012 6:25 AM by mikebeargie

    Purge date/notification


      I am building a database and have the need to purge a record after a set number of years. I am looking for a calculation that would alert the user that the set time period has expired and the record needs to be purged from the database. The purge date needs to be calculated off the date of entry or the date of the last modification of the record, which ever expires first. I'm new to FM and trying to learn as I go with a lot of assistance from more experienced people. Any help would be greatly appreciated.

        • 1. Re: Purge date/notification

          Assumably your creation date will always be the date that expires first.


          You will need the following fields:

          -creationDate - date type of field with auto-enter creation date

          -expirationDate - calculation type of field, the equation for the calc is:

          Month (creationDate) & "/" & Day (creationDate) & "/" & ( Year (creationDate) + numberofyearstoexpiration )


          Then you will want the following script, which I would suggest to run on open of the file

          -set error capture ON

          -goTo records table

          -enter find mode (no pause)

          -set field expirationDate - "=<" & get(currentDate)

          -perform find

          -if (get (lastError) =/= 401 )

             -Show custom dialog "purge these records"


          -continue to regular start of database


          Hope this makes sense and puts you in the right direction!