4 Replies Latest reply on Mar 1, 2012 4:34 AM by cdurham

    How do I delete all records when a certain date arises?

    cdurham

      Hello all,

       

      I am pretty much new to FileMaker. I currently run a Sales Import on a daily basis. The tables that I pull in are appended on a daily basis, which makes the Sales Import database larger and take more time to run. I need only keep about a months worth of data in the sales database. The company that I work for has 5 years of information in it! I only need to keep 30 days of information from each of the tables in the sales database. I need to know how to write something like this. IF a certain month is reached THEN delete all of those records. END IF. I also need this to be dynamic in some way, shape, or form. For instance, March 1st is tommorow, so I want to delete all of February 2012's information. When I reach April 1st 2012, I want to delete all of March's data. When May 1st, 2012 arrives, I want to delete all of April 2012's data and so forth. If possible, I would like to find the easiest way to do this. Once again, I am a beginner when it comes to using FileMaker so please bear with me. I am using FileMaker Pro Advanced 11.2 if this helps. Any help would be appreciated.

        • 1. Re: How do I delete all records when a certain date arises?
          comment

          You could run something like this as your opening script:

           

          Show All Records

          Sort Records [ by SaleDate, ascending ]

          Go to Record { Last ]

          If [ SaleDate ≤ Get (CurrentDate)  - Day ( Get (CurrentDate) ) ]

          Delete All Records

          End If

           

          Message was edited by: Michael Horak

          • 2. Re: How do I delete all records when a certain date arises?
            Stephen Huston

            I am curious about why all historic data should be removed, and also whether or not it needs to e archived, maybe in another table or outside the FM system.

             

            FM performance tests have shown that properly stored and properly displayed data responds fast (within a second or two) even with millions of records.

             

            I wonder if you could optimize your file so that keeping the data did not cause significant slowdowns.

             

            Of course, if this is essentially a reporting table and the source data still exists in the system elsewhere, then cleaning out obsolete reports is probably good.

             

            You could even have a Server-side script run every night to clear out all data which is over 31 days old, which would perpetually update your system to an optimal size.

             

            You could run script something like the following pseudo-code:

             

            Enter Find Mode

            Set Field (date) to: "<"&( Get(CurrentDate) - 31 )

            Set error capture On

            Perform Find

            If found count > 0

                 Delete found records

            End If

             

            This would assure that your records were all from within the last 31 days all the time.

             

            The advantage of having this run on a Server Schedule every night is that nobody ever has to remember to do it. The server just updates things every night.

            • 3. Re: How do I delete all records when a certain date arises?
              cdurham

              @Stephen Huston

               

              The company that I now work for used the Sales Database as a Data Warehouse. We are in the process of migrating from FileMaker to SQL Server 2008 R2.

              We do not need data as far back as 2007 anymore. We just need about a months data.

              • 4. Re: How do I delete all records when a certain date arises?
                cdurham

                @Stephen Huston,

                 

                I will implement the code that you have suggested. Thank you very much!