Some Thoughts about the Hard Deletion of Records

Partner Community

Partner Community
This Group is for Sage CRM Business Partners and members of the Sage CRM Developer Program. Accessto much of the material here is restricted according to your membership profile.

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Some Thoughts about the Hard Deletion of Records

  • Comments 3
  • Likes
I used to work for a Dutch software company. In their product there was a feature that would scour the application database and hard delete any data that failed certain referential integrity checks. I have never liked the idea of hard deleting data as it is so permanent. In the case of the software above my sense of unease was heightened by the message that popped up when the button was pressed. It takes a hard man to be unmoved by the question:

Immediately Eradicate All Widows and Orphans? OK|Cancel

Because physically deleting a record from the system is so final and there being no chance of recovery from "whoops!, I didn't mean to do that!" Sage CRM does not have a feature for the hard deletion of data. Instead a user that has deletion rights will have that data flagged as deleted. This is the purpose of the fields that follow the name pattern "xxxx_deleted". The core system views used implicitly by the CRM internal actions typically have a predicate to exclude 'deleted' rows.

e.g.

The view vcompany has the SQL: SELECT * FROM Company WHERE Comp_Deleted IS NULL
The view vopportunity has the SQL: SELECT * FROM Opportunity WHERE Oppo_Deleted IS NULL

Any 'deleted' records have a negligible burden on the system and provide the huge advantage that the CRM Administrator is able to retrieve any soft deleted records that were accidently deleted.

The existing interface prevents referential integrity problems occurring by preventing even the soft delete of a record where there are child records.



Note: This does not include a check for child custom entities.

But what if the customer does want to hard delete data?

If you want to find records that have been marked for deletion then you can open your SQL analyzer or Management Studio and run

either

select * from company where comp_deleted = '1' ;

or


select * from company where comp_deleted is not null;


To hard delete the records flagged as deleted you can run

either

delete from company where comp_deleted = '1';

or

delete from company where comp_deleted is not null;

But individual records exist in complexes. A company will have child records. For example removing the company records in this way will create orphaned person, opportunity, order, case, quote etc records that break referential integrity (albeit for only soft deleted records).





We can see a company record is directly referenced in the following 'Child' tables
  • Account (acc_companyid) - only certain Sage CRM 6.1 systems
  • Address_Link (adli_companyid)
  • Case (case_primarycompanyid)
  • Comm_link (cmli_comm_companyid)
  • Email (emai_companyid)
  • Lead (lead_primarycompanyid)
  • Library (libr_companyid)
  • Marketing (mrkt_companyid)
  • Opportunity (oppo_primarycompanyid)
  • Person_Link (peli_companyid)
  • Person (pers_companyid)
  • Phone (phon_companyid)
The link tables in turn link to other tables
  • Address_Link to the Address table
  • Comm_link to the Communication table which in turn may link to recurrences
  • Person_Link to the Person table
The case and opportunity tables exist in workflows
  • Cases link to CaseProgress
  • Cases link to SolutionLink which in turn link to Solutions
  • Opportunity links to OpportunityHistory
  • Opportunity links to OpportunityProgress
Depending on the features implemented
  • Opportunity links to OpportunityItems (e.g. in Accpac ERP integration)
  • Opportunity links to OrderQuotes (in Sage CRM 6.0 and earlier) which in turn links to LineItems
  • Opportunity links to Quotes (in Sage CRM 6.1) which in turn link to QuoteItems
  • Opportunity links to Orders (in Sage CRM 6.1) which in turn link to OrderItems

Static Groups, Key Attributes and Workflow Implications

Any of the tables above may have been referenced in a static group (target list) or have Key Attribute data (Dynamic Data) recorded against it. There may even be references to a company or person record in the calllist and calllisttracker tables.

There will also be references to the records in the workflowinstance and escalations tables which if orphaned may corrupt the workflow and cause problems across the system.

This again ignores any custom entities that may have been added to the system.

SOLO and Outlook considerations

Records that are hard deleted in CRM may create large errors when SOLO is synchronised. For example if a record is soft deleted on the server, that fact is synchronized happily to the SOLO client. If the record is hard deleted before the synchronization there will be a mismatch in the data.

Also hard deletion of Communications may cause problems with Outlook synchronization.

Note:

Hopefully the above explains why hard deletion of records is not recommended and why it is not a supported action to carry out. If you are going to do this then you must, must backup your database. Without extensively testing you run the risk of creating permanent irrevocable damage to your application data. You have been warned.
  • Nice article Jeff (as usual)

    When I first saw Sage CRM I really appreciated this feature :)

    The pity is that in some cases data is hard deleted (selection options) and you leave records in company for example that when are edited loose field values as they are no longer valid in selection fields...

    It would be great at least to get a message suggesting  to make a global edit for that field and value... you have XX records using this value in Company entity...