2 Replies Latest reply on Jun 15, 2011 4:54 PM by david583

    Finding complete records older than 'X' months



      Finding complete records older than 'X' months


      FMPro10 & FMPro11, FM Server11. Windows Network (XP & Vista). db built on Pro10.

      I want to start archiving records where the dispatch date is older than a set amount of months (3~12)

      I have a field [ADMIN::record_archive_threshold] that has a value list selection of 3~12

      I want to use this value in a calculation for the find, but am getting inconsistant results. I currently have:

      Enter Find Mode [ ]

      Insert Calculated Result [ Date ( Month ( Get ( CurrentDate ) ) - ADMIN::record_archive_threshold ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) ) & "..." & Get ( CurrentDate ) ]

      Perform Find [ ]

      Show Omitted Only


      I think I have overcomplicated it a little, does anyone have any better ways of doing this?

        • 1. Re: Finding complete records older than 'X' months

          Hi David,

          Honestly, if you mean 'archive' as in find and delete or find and import into a new table and delete from this table, I would advise against it.  In all my years in working with databases, it has never been a good idea.  I would suggest instead that you instead use that record_archive_threshold to periodically find those records which should be 'archived' and flag them as archived but leave them in the table.  This script could be ran every night using server-side scheduling when Users are not in the system.  If you flag the records when Users might be in the system, you can run into record lock and it won't flag the field.  Leaving Archived records in the table is recommended because you will not be able to generate statistical reports (easily) unless they reside in the same table.

          Anyway, to find all records which should be deleted, you don't need a range.  Instead you can use 'less than'.  Also, you should use Set Field[] instead of Insert Calculated Result[] whenever possible (which is almost always).  Insert Calculated Result requires that the field be on the layout - no such limitation with Set Field[].  So try this, which will find all archived (you can use the Show Omitted as end step within Else if records found and you want to only shows those records NOT archived):

          Enter Find Mode [ uncheck pause ]
          Set Field [ yourCreationDate ; Let ( d = Get ( CurrentDate ) ; "<" & Date ( Month ( d ) - record_archive_threshold  ; Day ( d ) ; Year ( d ) ) ) ]
          Set Error Capture [ On ]
          Perform Find [ ]
          If [ not Get ( FoundCount ) ]
          ... do whatever you wish (if anything) if no records are found such as display a message
          ... do whatever you wish with the records which should be archived
          End If

          The other archive option is to simply include a calculation (result is number) with:

          Case ( creation_date ;
           Let (  d = Get ( CurrentDate ) ;  creation_date < Date ( Month ( d ) - record_archive_threshold  ; Day ( d ) ; Year ( d ) ) )

          Set the Storage Options as checked 'Do not store calculation results ...' so it will automatically update.  Then leave the records there are just 'automatically' flag them when their time comes.  From layout level, you can format the calculation as number Boolean with words "Active" only on the no option (0).  The word "Archived" is too long for the Boolean text (which only allows 7 characters).  If you use the calculation, it will require the lowest footprint; however, if you wish to find these records (or omit them from reports etc) then it will take a bit longer because the calculation must be unstored.

          It would all depend upon your business rules, the frequency of omitting Archived records from your displays and what you had planned to do with the archived records.

          • 2. Re: Finding complete records older than 'X' months

            Thanks LaRetta for your detailed reply, set field does work much better.

            To explain what I'm doing further; I am rebuilding a database that has been running about two years. It controls order through put and builds quickly on a daily level. I agree, and have resisted for two years, the whole concept of removing data from the tables, (and records are currently flagged as 'Active' or 'Complete') but there are now over 45,000 records on each of two main order tables of which only 200ish are active at any one time. To load/switch between list layouts and filter out the complete records now takes a long time (and the annoying Windows screen flashing is worse).

            The current reporting system is focused on a weekly dispatch schedule. Reports from this are appended to other reports to create a log. I did build functionality to report on history, but as yet has not been used at all! The current reporting system used will work well without the need to look at complete records.

            The Archive is a seperate file, accessed as an external table occurance in the main db.  There is a seperate reporting system for the archived records as well, in case they ever do need to (just cause they haven't doesn't mean they won't)

            So yes, I agree archiving is not a good idea, but in this case I'm going to give it a go.

            Thank you again for your help and advice Smile