How to archive Work Object Table data to another table in the same database
Is there anyway to archive the work objects(and the related data) to another table in the same database. Of course we have the Purge/Archive wizard, but this cann't help us in copying the data to another table.
Please let me know the better approach to do this.
You can not archive the WO in the same application with same DB, as there are lots of tables that got affected by archiving and purging. You need another envirnment to store the archived WOs. Or you can write custom activity to store the table data to different table(By connect sql). In that case you need to know which are the tables affected by your WO. Work table, history table, Attachment table, index table to name a few.
This should be working but the only catch is if you expose a new property in work table, you need to update the same in the duplicate table and update the proc to set the value as well. But you will have blob which will contain the value though.
How are you handling the Search functionality in the application ? If i would like to search the work object which is older than one year , then where should the search query to look for the data ? Is it active transactional database or archive database ?