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:

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 accidentally 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 integrated 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 Quotes which in turn link to QuoteItems
• Opportunity links to Orders 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.

Mobile App and Outlook/Exchange considerations

Records that are hard deleted in CRM may create large errors when communicating with Mobile Apps (Android or iPhone). For example if a record is soft deleted on the server, that fact is synchronized happily to the iPhone App. 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 and Exchange integration.

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.