Application Note for QuNect ODBC for QuickBase

Archiving Records from one QuickBase Application to a copy of the Application

The following method will allow you to create space in your QuickBase application while preserving your archived records in an identical QuickBase application.

  1. Create a checkbox field at the top level of your parent child relationships. Whether it's a regular checkbox field or a formula checkbox field is up to you.
  2. Create lookup fields for the checkbox field you created in step 1 at every level of the parent child hierarchy.
  3. Make a copy of your application without data.
  4. Identify every table you want to archive. This may be all of the tables.
  5. For each of the tables in the copy that will be archived that is the parent table in a relationship and whose key field is the built-in field called Record ID# you'll need to copy the Record ID# field. Then make this field the key field. This will create a new reference field in each of the child tables and turn the old reference field into a lookup field.
  6. Write a series of SQL INSERT statements that copies the records that you want to archive based on the checkbox fields you created in step 1 and 2 to their respective tables. In each case where there is a table that is a parent table in a relationship and whose key field is the built-in field called Record ID#, you'll want to copy the Record ID# field to the copy of the Record ID# field you created in the step 5. In each case where there is a table that is a child table in such a relationship, you'll want to copy the reference field to the new reference field you created in the step 5 when you changed the key field in the parent table.
  7. Write a series of SQL DELETE statements do delete the records that were archived based on the value of the checkbox fields created in step 1 and 2. The order of these SQL delete statements must proceed from the lowest level child table of the parent child hierarchy to the highest level parent.
  8. Configure your DSN to represent file attachment fields as a Text field with File URL to file downloaded to local disk.. This will copy the current revision of your file attachments as well as all your other data.