Salesforce Data Migration: Preserving Legacy Case Numbers

Salesforce case numbers being auto numbers makes it difficult for organizations that are going through org consolidation, migrating from legacy case management solutions or doing an organization split.

The options are to let go of the legacy case number or create a new custom field to manage the case number in salesforce. Both there solutions are far from ideal.

Well there is hope. Outlined here is a technical solution that will resolve the problem.

• Step 1. Call Salesforce support to enable the audit fields on all the objects you are planning to migrate.
• Step 2. Create a DummyCase custom object will a field for all the standard case fields. So you will have CaseNumber, ClosedDate, Subject and so on in a custom object.
• Step 3. To a data upload via data loader or your tool of choice into the DummyCase object. The source data and the dummycase custom object at this point should have same data.
• Step 4. Create a custom field for validation purpose on Case and call it External Case Number.
• Step 5. Create another custom field on the case a checkbox and call it To Be Deleted.
• Step 6. Find out what number to start the Case auto number based on the starting case number from the source system.
• Step 7. Implement a before insert trigger on Case that will query the DummyCase object based on the case numbers being assigned by salesforce and set the appropriate values on the case before the insert happens. The trigger will assign the appropriate values to the case record if a value is found in the dummy case object. If no value found that means the case number being inserted did not exist in the source system and is a candidate for deletion later. We will set that record with our To be Deleted flag.
• Step 8. Find out the max value of case number from the source system and load that many empty case records.
• Step 9. Delete the cases that have the To be Deleted flag set by the beforeinsert trigger

trigger CaseTriggerBeforeInsert on Case (before insert) {

Set caseNumbers = new Set();

// Find out the current case number. During our first batch the number will be null and going forward we will just increament by 1
AggregateResult result= [select max(casenumber) from case];

Integer currentCaseNumber = Integer.valueOf(result.get(‘expr0′));
if (currentCaseNumber ==null) currentCaseNumber = 0;
currentCaseNumber = currentCaseNumber +1;
// This is the number we can be assured salesforce will be using for the next case number

for(Case caseObj : trigger.new)
{
caseNumbers.add((currentCaseNumber ++) );
}

// Lets say a batch size of 1000 or 2000 we can now lookup all the dummycases we have already loaded for these case numbers
List<DummyCase__c > dummyCases = [select case_number__c, closeddate__C , id from dummycase__C where case_number__c in :caseNumbers];
Map<decimal, DummyCase__C> mapDummyCase = new Map<decimal, DummyCase__C>();
for(DummyCase__c dummyCase: dummyCases)
{
// lets create a map for the dummy cases to lookup later
mapDummyCase .put(dummyCase.Case_Number__C , dummyCase);
}

// we loop through our original cases being inserted again

currentCaseNumber = Integer.valueOf(result.get(‘expr0′));
if (currentCaseNumber ==null) currentCaseNumber = 0;
currentCaseNumber = currentCaseNumber +1;

for(Case caseObj : trigger.new)
{
// our cases being inserted have no real data we are going to do a
// lookup on the dummay map to get our real values

DummyCase__c dummyCase = mapDummyCase.get((currentCaseNumber ++) );
if( dummyCase != null)
{
caseObj.createddate= dummyCase.cLosedDate__c.addDays(-1);
caseObj.closeddate = dummyCase.cLosedDate__c;
caseObj.status = dummyCase.Status__c;
// now you can add all the other fields here using similar mapping
caseObj.To_Be_Deleted__c= false;
caseObj.External_Case_Number__c = dummyCase.case_number__c;
}
// if we do not find one then we had a missing case number on our source
// we will set a to be deleted flag to delete after migration
else
caseObj.To_Be_Deleted__c= true;
}

// we are done now let salesforce proceed with the case insert

// our case number matching has been taken care of

}

Once the trigger runs through all the batches and your migration of case is complete, delete or disable the trigger. You may delete the dummy case object or just the records as its job is now done.

There seem to be a lot of steps but will the trigger code included below this should be a breeze for any technical consultant on your team. If you feel you have doubts or need assistance don’t hesitate to contact us.

Let us know if you found this useful. Have a great day ahead..