This is in continuation with my previous post regarding data archival on this blog site. You can go through the below link to follow the same.
http://sagecrmaddons.blogspot.com/2009/05/greytrix-sage-crm-archive.html
The above post was more about the problem definition and it also summarizes the proposed solution for the same. Here we will discuss how the actual flow or process can be implemented in CRM. The archival process mainly consists of two basic actions as mentioned below.
1. A routine to archive the data
2. Interface in CRM to view the archived data
A routine to archive the data
The primary task in archival process is to archive the actual data in database. For this we can either go for complete table copying or copying the part of data over some period based on the logic that we need to implement through archival process.
There are two ways you can implement this. One is to develop an external routine like executables or js files to connect to CRM externally and copy the data over to the dummy table on archive database.
Another method you can use is to write the routines in SQL server itself. For this you can make use of the powerful method provided by latest SQL Server versions called SQL Server Integration Services (SSIS). Using this we can design packages and incorporate the flow of data within the tables having similar structure even if they reside on different server on different database. As these packages are capable of handling the data in bulk, the actual data transfer will save lots of time for you than that of the external routine. You can get lots of examples to understand this data transfer task over the web all explaining the extreme tricks and techniques to do it more efficiently and effectively.
To avoid the conflicts in the structure, we may also need to keep the table and field names different as mapping task is no more complicated through SSIS package and we don’t have to take risk to create exact similar named tables and fields.
Screens in CRM to view the archived data
After archiving data, you may also need to display the data in CRM for the reference. For e.g. if CRM is being used in call center operations, sometimes for customer support we may also need to look into the old data. To achieve this we must provide a user interface in CRM to see an archived data. Though designing the interface for all the functionalities is a bit tedious task, if you are just planning to view the data and not edit it you can go about it very easily as we need to design only the read-only screens.
Using the database and table connectivity options provided by standard Sage CRM installation, we can connect to external databases very easily. Then we can either connect to the table from external database or either we can create dynamic views in external database and then connect them from CRM. This table or view will be considered as a secondary entity in CRM and will be available in CRM to create standard objects like screens, lists etc.
Through this standard mechanism and some custom behavior we can easily make the external table data available in CRM for viewing purpose. We can provide separate tabs to view the archived data. For e.g. In My CRM we can see the tab named Communications. We can add new tab named Archived Communications under which we can display all the archived communications from the table that we have connected externally.