3

I have a requirement where I need a sequential unique autonumber field on opportunity. To me that seemed simple to just the standard Salesforce Autonumber field. This seems to work fine, when we have a normal usage of manually creating Opportunities.

Then the salesforce creates sequentially generated autonumbers:

  • Opportunity 1: 1 (manually)
  • Opportunity 2: 2 (manually)
  • ...

However, next to normal usage on this org, we have a batch job, which syncs opportunities overnight, created in a mainframe system. And now the problems begin, my sequentially generated autonumbers seem to have large gaps every time the batch job ran. After the first night, my autonumber was 437 all of a sudden even though there was only 1 opportunity created from the batch job.

  • Opportunity 1: 1 (manually)
  • Opportunity 2: 2 (manually)
  • Opportunity 3: 437 (batch)
  • Opportunity 4: 438 (manually)

After using this for 2 weeks in production, my autonumber is at 18000 while there are only 350 opportunities created from the mainframe.

This got me thinking, I could generate the autonumbers myself in a trigger, as I can safely assume at the time the batch job is running, there isn't anyone working in the system.

Unfortunately, I have a third requirement, which stopped me from implementing this solution, as I'm not sure if it would work.

Namely: We have 8 peak moments every year, when we gets a lot of clients wanting to do business, for that we have a public visualforce page, which is displayed instead of the website (as our webserver crashes because of the heavy load). Where clients can fill in a simple form, which in its turn generates, you guessed it, an Opportunity.

From all the requirements, the uniqueness is the most important (not indefinitely unique, but for at least a year, read on if this doesn't make any sense.), as from the autonumber, a part of a structured message for payments is generated, that structured message is limited in length, so I only have 6 numbers in my autonumber. I take the last 6 digits from the autonumber, and use them in the structured message of the payment.

Those last 6 digits must be unique for at least a year (during that year, we wouldn't want a duplicate structured message for payments). The 6 digits would give me 999999 different combinations before there would be a duplicate structured message again, and this gave me plenty of room to maneuver with, as we are well below 999999 different opportunities per year.

I could live with the autonumber not being entirely sequential (with a few gaps, as I know that running tests causes the autonumber to jump). But the gaps as they are now are way too much.

What would be the proposed solution here, I wish there were "synchronized" methods in salesforce for doing this. Hopefully this can all be done in apex.

2 Answers 2

4

There is the "for update" locking mechanism in SOQL. So code like this:

public static Integer nextNumbers(Integer n) {
    Sequence__c s;
    Boolean ok;
    do {
        try {
            ok = true;
            s = [select Number__c from Sequence__c limit 1 for update];
            s.Number__c = s.Number__c + n;
            update s;
        } catch (QueryException e) {
            ok = false;
        }
    } while (!ok);
    return s.Number__c.intValue();
}

will allocate sequential numbers because of the locking between the query and the update. If n = 1, then is just the next number returned, but n can be made bigger to essentially grab a block of sequential numbers with the method returning the first of those numbers.

If a request is made while another process has the "for update" lock a QueryException is thrown, hence the retry loop. Where possible, doing all other work first and just calling this at the end will minimise the contention window. This lock remains in place until the transaction completes and so ensures that updates are made one after another (rather than at the same time) avoiding duplicates and leaving no gaps.

If you search in this forum you will see other examples of this pattern with comment threads that are useful e.g. Help with Trigger to that will index Account Number field.

9
  • how would this work bulkified? as my batch job can create 200 opportunities at once?
    – pjcarly
    Nov 7, 2014 at 12:10
  • 1
    @PJC I've modified the code for one pattern for this where a block of numbers is allocated e.g. 200 at once. But I'm having second thoughts about this whole approach in that the change isn't committed to the database on the "update" but rather on the commit, so between the update and the commit other requests could be allocated the same numbers...
    – Keith C
    Nov 7, 2014 at 13:13
  • Wouldn't I be in risk of running into a LimitException (Too many soql queries 101) when there's a whole lot of processes querying that same Sequence__c object
    – pjcarly
    Nov 7, 2014 at 14:59
  • @KeithC because you use "for update' the db lock is held for the remainder of the transaction, so you don't have the problem you mention. (but then if there's lots of other processing going on, you get can high contention on this select)
    – superfell
    Nov 7, 2014 at 15:43
  • 1
    Salesforce document this behaviour in auto-numbers with some explanation of why they make it so: help.salesforce.com/s/articleView?id=000383836&type=1
    – RobD
    Mar 27, 2023 at 14:03
0

Recently (I believe Spring '14) an option has been added for tests to use a separate auto-numbering sequence. This might be enough for your needs.

You can find it via Setup->Build->Apex Test Execution->Options->Independent Auto-Number Sequence

enter image description here

1
  • Hello guy, thanks for your response. But the tests don't cause the large gaps in autonumbers, it's the batch job. My guess is that salesforce locks a bunch of autonumbers when running batches.
    – pjcarly
    Nov 7, 2014 at 9:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .