Archiving table data

You can either copy the whole app and call it�s frozen AXY app and ensure that no roles can update, or else you can copy the big table to another table in your app. However, if you do the latter and you have relationships, then you will lose all the relationships and their lookup and summary fields

To Copy the table (which conveniently will also copy your forms and reports, use the more button.

Once you have a secure copy of your data, then give that big table a careful haircut by making a report of what is to be purged out, checking it thrice, and then More .... Delete these rdr s, .... and an Are you sure ? And then you will have room for more records In your live table.

Thanks for the reply.

The archive table will need to be accessed via the same report that is showing the original table.

For example if we are storing "employees" and "hired date" in a table called "personnel".  The table "personnel" has reached the 500mb limit.  The report "Date Hired" list employees hired during a date range.  We want to be able to archive off all employees hired in the years 2016 and 2017.  If we copy the table "personnel" to "personnelArchive" then delete all employees after 2017, we will have the correct data archived.  We then delete all employees in the "personnel" table hired before 2018.  At this point, how do we create a report that will allow us to search "Date Hired" and still capture the archive data as well as the new data?  If the employee is hired before 2018 look in the "personeelArchive" table and if the employee is hired after 2018 look in the "personnel" table.

A report can only search one table.  But a form may have multiple reports on it using Report Link fields.

I will describe a technique which is suitable for 1 user at a time doing a search, but then at the end of this post I will briefly describe a multi simultaneous user solution.

Create a single record in a new table called Search Employees.  It will be [Record ID#] = 1.

Then what you will need to do is to think of the typical ways that users need to search for employees.  Let's say that you simply want to search by name.

So make a field called [Name] on the Search record and make a relationship down to the main employees table based on a field called [Link to Search (=1)] with a formula of 1.  It will  calculate to a 1.

Then look up that Name field down to the employees table and call it say [Name Search].  Then make a formula checkbox field called [Search is a Match?] that calculates to true if the [Name]  Contains the value in the field [Name Search].  Use that field [Search is a Match?] as a filter on the report link field which displays employees on the Search record.

I suggest setting the report link field results to only show in View mode as the filter will only take effect after the search record is saved.

Repeat the Relationships to the Archive table and put the report link on the same Search record.

Depending on your search needs, you can create increasingly complex search criteria and hence increasingly comply formula checkbox field on the employee records to check if the employee meets the search criteria.

To make this work for a multiple simultaneous user situation, it's a similar setup, except the Search take will actually be a table of Users where the Key field is set to type User, and the reference field is a field called [Current User] with a formula of User().

There is also a way to auto create the User when a user does their first search so that they do not need ot know to create their user record to make a search.   

Post back if you get stuck or contact me via the information on my website if you want one on one assistance in setting this up.  QuickBaseCoach.com