We have a projects table and a child cost management table. In each cost table, we record trade level costs for a nominated date. Unfortunately, this is a requirement for us to archive the trade level cost for historical comparison over a period of time. Ideally, I would like to archive or copy record to another app or table. What is the best way to achieve this?
You would need to have a process to mass flag records to be copied, and then copy them, and then purge records that have been copied. maybe copy them to another table and record the record ID# in the other table and set that to be the Key field of the target table. Then have the real records be children of the archive table. If any child has a parent, then you know for sure that it did get copied and it is now safe to purge out those records. You just want to be very sure that you have a bullet proof process so as to not accidentally purge records before they are copied.
I'm sure you want to automate this. I recommend using a script to pull the fresh data and push it to a history table. Depending on what you need tracked, 500 projects may not be that much data. Quickbase says it can handle 500MB of data, although really 250MB is pushing it). Still, I have set up something very similar for 4 million records.
Following the no-code philosophy of Quickbase, I have a simple script that will pull data from Quickbase to CSV, and I have another script that pushes CSV data to Quickbase. These scripts can be daisy-chained, so it is pretty easy to configure a job to track data history. These scripts are configurable by the user and therefore can be used for future needs as well.
To run them on a nightly basis, you can use Windows Task Scheduler. I recommend putting this script in a virtual machine so that the machine is always running.
If you are looking for a more real-time approach, I have another solution which can process records on change.
Both of these solutions are pretty simple to implement and are flexible to future needs. I hope this helps.
You just need a computer somewhere, anywhere that can be left on. Not kidding, I've used a laptop as a server before. Anything with an OS will suffice.
Another option is I can handle this for you off-site. I can host the box to run this routine code, all I will need from you is access to view the source table data and access to view and add to the destination table.
Quickbase does not implement a scheduler of any kind, which is why having this code on machine will be important.