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.
- 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.
- Create lookup fields for the checkbox field you created in step 1 at every level of the parent child hierarchy.
- Make a copy of your application without data.
- Identify every table you want to archive. This may be all of the tables.
- 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.
- 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.
- 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.
- 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.