More Fun with Force.com Transactions: Create a Unique Number

I’ve been having fun with Salesforce transactions lately – more accurately, fun with starting and then rolling back transactions. I wonder if that means I have issues with commitment….

Maybe you’ve seen my earlier posts that demonstrate how you can accomplish things by rolling back transactions:

• Create a Task Assigned to Lead Owner of a New Web Lead
• Sending Emails in Salesforce to Non-Contacts Using Apex

Here’s another example of what you can do with transactions. Imagine your boss wants to assign a unique number to each new Lead and Contact that’s created in your org, as follows:

• The numbers must be integer numbers – no alphabetic characters or decimal points allowed.
• The numbers must be unique across both Leads and Contacts unless a Lead is converted to a Contact, in which case he wants to copy the Lead’s number into the new Contact.
• The numbers don’t have to be sequential – they’re not going to be used to count anything, so gaps are OK.

You might be tempted to define Auto Number fields on both Contact and Lead, but that won’t work, for a few reasons:

• If you have separate Auto Number fields on both Contact and Lead, the numbers won’t be unique across both objects.
• If you have separate Auto Number fields on both Contact and Lead, when a Lead is converted, the new Contact will have its own Auto Number, and the requirements say that it should retain its originating Lead’s number

You might then be tempted to write simple triggers that fire whenever a Contact or Lead is created, counts up the number of Contacts and Leads, adds 1, and then assigns that number to the new record. However, if you have large numbers of Contacts or Leads, you could hit governor limits pretty easily. Besides, if the triggers fire multiple times concurrently, you could wind up with duplicate numbers.

What to do, what to do?

The solution is to use Auto Numbers, in conjunction with transactions.

Start by creating Unique Number (Integer) fields for both Contact and Lead, writable only by System Administrators. Then create a new custom object named Unique_Number__c. Set its Name field to be an Auto Number with a display format of “{0}”. Then create a static method like this:

 1  // Returns a unique number
 2  public static Long getNextUniqueNumber() {
 3     
 4      // Begin a transaction
 5      Savepoint sp = Database.setSavepoint();
 6 
 7      // Create a Unique Number record. Its Name field is an auto-number, so it
 8      // will be assigned the next sequential number that hasn't been used yet.
 9      Unique_Number__c un = new Unique_Number__c();
10      insert un;
11 
12      // Query to get the Name of the record we just created.
13      un = [select Name from Unique_Number__c where id = :un.id];
14 
15      // Convert the name to a number
16      Long num = Long.valueOf(un.Name);
17 
18      // We don't need to save the Unique Number record. Roll back the
19      // transaction, and the record will disappear forever.
20      Database.rollback(sp);
21 
22      // Return our number
23      return num;
24  }

As the comments indicate, when the method creates the Unique Number record, Salesforce sets the new record’s Name to the next available Auto Number value. Rolling back the transaction doesn’t free up that number to be used again: with Auto Numbers, once a value has been used, it’s never available to be used again.

Why roll back the transaction instead of just deleting the Unique_Number__c record? Deleting the record would leave it in the recycle bin, and there’s no need to clutter up the recycle bin with these records.

Given the method above, you can imagine how triggers on Lead and Contact would call it, and then store the result in the Lead’s or Contact’s new custom Unique Number field. (Of course, you’d want to bulkify the method, allowing it to be called with an Integer number that indicates how many Unique Number records to create, roll back, and return. But I’ll leave that as an exercise for you – feel free to post your solution!) You can also imagine how you could write an update trigger on Lead that fires on Lead conversion, detecting when the Lead’s ConvertedContactId field is set, and updating the corresponding Contact with the Lead’s Unique Number value.

So, rolling back transactions proves useful once again! Have you found any other uses for Database.rollback()? Post them here!