Archive Selected Record

  • 1
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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? 

    

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Can you list the relevant tables and their relationships?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. Stores 1 to M Projects, Projects 1 to M Cost Management, Cost Management 1 to M Trades.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
I am not understanding why you feel that the records in the Trades table needs to be duplicated into another table.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks.  We review the historical cost over a period of time and this archived data helps us.  Currently there are 28 trades in each project and there 500 projects.  So there is a lot of data. Will appreciate if you have any other insight on an alternative approach.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
But if you did want to make a process to export a range of records off to another table, I would do that using a saved table to table copy, and running it with an API like API_RunImport&ID=10

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.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks.  I am finding it all little complex.  Wish there was a straight forward way to archive records.
Photo of David Maskasky

David Maskasky

  • 100 Points 100 badge 2x thumb
Hello again Arshad,

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.

Sincerely,

David Maskasky

David@WorkableSolutionsLLC.com
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot David. Anything that requires help from our mates in IT is almost doomed. What is the other approach?  Also have you heard about webhooks?
Photo of David Maskasky

David Maskasky

  • 100 Points 100 badge 2x thumb
Haha.  I work in IT, needless to say, I understand.
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.

In the second approach, it would be simple to add javascript that to Quickbase to add these transformed records to the destination table on change.  That approach will not require any involvement with IT. :smile:
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks David.  I am absolutely dull with coding side.  Where could I get the script?
Photo of David Maskasky

David Maskasky

  • 100 Points 100 badge 2x thumb
Looks like it has been a busy day between the two of us.  Should we schedule a conference call to discuss implementing this?  I am on California time, when is a good time for you?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot David. It is very kind of you to offer this.  I am in Sydney. Perhaps your afternoon today will be a good time for me in the morning.  My email is akhwaja at woolworths.com.au.
Photo of David Maskasky

David Maskasky

  • 100 Points 100 badge 2x thumb
Hi Arshad, let's do a conference call at 5PM (PST) on Monday.  That will be 10 AM Sydney (NSW) time.  My number is 585-489-5400.  Please let me know if this time will work for you.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. If I am not wrong, that would be Tuesday, which is tomorrow as we have just started our week.
Photo of David Maskasky

David Maskasky

  • 100 Points 100 badge 2x thumb
That is correct.  It is still Sunday here.