In my previous post I have discussed the methodology for data archival process in CRM. The part of the process also includes connecting an External Database. Suppose we are archiving communication table. That means we need a table similar to that of communication table to archive data in the similar fashion as that of the main table. This table being used for storing archived data may reside on different database on different server.
In Standard CRM we can connect an external database as below.
1. Go to Administration –> Advanced Customization.
2. Click on the button named “Tables and databases”.
3. Click “New database connection”.
4. Enter the database connection parameters as displayed. (User credentials being used here for connecting to database must have proper rights to read or write data to database tables.).
5. Click on the Save button to Save the settings.
After connecting External database, we can connect to table in that database as follows.
1. Go to Administration –>Advanced Customization.
2. Click on the button named “Tables and databases”.
3. Click “New table connection”.
4. Enter the details of the table from Archive database. Select Archive database under Database field.
5. Click on the Save button to save table connection details.
What’s the hitch?
Before moving ahead with connecting external database and tables, we must first ensure the structural resemblance in both the tables. Now what do we mean by structural resemblance? Is it like having similar tables with the same field names in both the databases? No, they are quite not required. We just need to ensure that the table structure remains same like every field in source table must have same type and sized equivalent field in destination table. Name constraints are not required.
What will be the preferable approach?
The preferable option will always be having different table and field names in archive database for the clone table. This needs to be done in order to avoid the discrepancies in CRM. What kind of discrepancies we can face in CRM if we do not follow this approach?
Suppose we have created a table for archiving purpose in external database for communication with the same name. Now suppose we are attaching this table in CRM through the external database connectivity provided by standard CRM as explained above. This creates an extra translation entry for external table in CRM.
Now when we go to the lists and screen objects designed with standard communication entity we will see the new translation entry over there also which is logically wrong and it may even lead to screen crashing and report generation errors in CRM.
If we do not wish to move forward with this approach, we may have to end up using column name aliases for all the queries being written on external tables no matter how long they are!!….:-)…