I am looking at a solution to providing a guaranteed contiguous sequence of numbers. This cannot be achieved via autonumber fields as numbers in the series are consumed by failed validations etc. - not to mention the problem with tests burning the sequence as well (though this can be mitigated by asking SFDC to activate a beta feature to enable rollbacks to roll back the sequence, at the expense of some performance).

One idea is to use a custom setting. Custom settings values are held in an application cache to minimise DB access. If a custom setting is updated via DML, is the value replicated into all instances of the cache immediately?

My concern is that where there is multiple concurrent access to this number, at the point of update the cache could be stale for at least some requests.

Has anyone solved this problem satisfactorily?

Attribution to: AlexF

Possible Suggestion/Solution #1

I have dealt with code that gets a value from a custom setting, uses it, increments it and then stores it back from a trigger. The issue is that it is not an atomic operation and all that it takes is two users accessing it about the same time to fail.

trigger MyObjectTrigger on My_Object__c (before insert) {
    MySettings__c mySettings = MySettings__c.getOrgDefaults();
    Double myAutoNum = mySettings.My_Custom_Auto_Number__c;
    for (My_Object__c myObj : Trigger.new) {

        myRec.My_Auto_Number_Field__c = myAutoNum;

        myAutoNum = myAutoNum + 1;
    }
    mySettings.My_Custom_Auto_Number__c = myAutoNum
    update mySettings;
}

1. You can get the UNABLE_TO_LOCK_ROW error if two users are trying to update the custom setting at the same time.

2. You can get a situation where two users get the same auto-number.

One way to protect against duplicates is to mark the object's field as unique. This will prevent the insertion when two values are the same, because the second user's record will fail to insert. That may be a "good enough" solution depending on what it is that you are trying to accomplish.

If it is good enough, make sure that your unit tests do not share the same custom setting. That is, make sure that each one of them is API version 24+, don't use @SeeAllData=true, and that they all create and use their own custom setting. Salesforce runs the unit tests on deployment in parallel, so if any of them share a custom setting they can fail nondeterministically.

Attribution to: Peter Knolle

Possible Suggestion/Solution #2

As has been stated auto numbers won't give this and implementing it in the cloud where locks and such resources are rightfully less available. So here is another suggestion you might want to put to your business analyst...

"Secondary Numbering System". While I'm not sure of your root requirement here, if your client wants to be sure to see this information for governmental or audit reasons. Why not consider adding a custom field that represents a second numbering sequence that can be updated by a scheduled job every night, week or month. You can put controls in place to prevent this being tampered with and ensure the reports only include records with this applied. Further more you also get the flexibility of implementing your own system, with your own starting point, reset as required and sub-sequences if needed.

Hope this helps give some food for thought! Enjoy!

Attribution to: Andrew Fawcett
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4289