How to Generate Sequential Order Numbers in Salesforce?

Question:

I have a requirement where orders are created by users every minute. When an order is created, it starts in the stage “Draft” and then moves through the stages “Submitted” and “Fulfilled.” I want a custom field called OrderNumber to be automatically populated in the format ABC-00001, ABC-00002, and so on, but only once the order moves from “Draft” to “Submitted.”

This means the first order submitted should have ABC-00001, the second ABC-00002, and so forth. I am thinking of using a trigger for this, but I am concerned about how to properly maintain sequential numbering and avoid conflicts when multiple users submit orders at the same time. What is the best way to achieve this requirement?

Answer:

While it is technically possible to implement this requirement with Apex code and triggers, doing so comes with challenges such as handling concurrency, ensuring atomic updates, and avoiding unnecessary queries. Managing sequential numbering in a multi-user environment can quickly become complex and error-prone.

Boost your career with CRS Info Solutions’ Salesforce online training, offering expert guidance and practical experience. Master the Salesforce ecosystem through comprehensive, hands-on learning.

The simplest and most reliable solution is to use Salesforce’s built-in Auto Number field. Auto Number ensures unique, sequentially increasing numbers without custom code. You can format it in the required pattern, for example ABC-{00000}. If you only want the number to appear once the order is moved to “Submitted,” you can still use Auto Number but hide it from users until that stage.

This can be done by creating a formula field that checks whether the stage is “Submitted.” If true, the formula displays the value from the Auto Number field; if false, it returns blank.

For example:

IF(Stage__c = "Submitted", TEXT(Auto_Number__c), "")

IF(Stage__c = "Submitted", TEXT(Auto_Number__c), "")

If business requirements strictly mandate custom numbering logic (e.g., resetting numbers yearly or skipping certain sequences), then you would need Apex with a mechanism to handle concurrency safely. One common approach is to use a custom object that stores the current counter, and update it using FOR UPDATE in a trigger:

trigger OrderNumberTrigger on Order__c (before update) {
    for (Order__c ord : Trigger.new) {
        if (ord.Stage__c == 'Submitted' && String.isBlank(ord.OrderNumber__c)) {
            Counter__c counter = [SELECT CurrentValue__c FROM Counter__c WHERE Name = 'OrderNumber' FOR UPDATE];
            counter.CurrentValue__c++;
            ord.OrderNumber__c = 'ABC-' + String.valueOf(counter.CurrentValue__c).padLeft(5, '0');
            update counter;
        }
    }
}

trigger OrderNumberTrigger on Order__c (before update) {
    for (Order__c ord : Trigger.new) {
        if (ord.Stage__c == 'Submitted' && String.isBlank(ord.OrderNumber__c)) {
            Counter__c counter = [SELECT CurrentValue__c FROM Counter__c WHERE Name = 'OrderNumber' FOR UPDATE];
            counter.CurrentValue__c++;
            ord.OrderNumber__c = 'ABC-' + String.valueOf(counter.CurrentValue__c).padLeft(5, '0');
            update counter;
        }
    }
}

Explanation:

This trigger runs before update on the Order__c object and checks if an order is moving to the “Submitted” stage without an existing OrderNumber. It then locks a Counter__c record using FOR UPDATE, increments its CurrentValue__c, and assigns a sequential order number in the format ABC-00001. Finally, it updates the counter so the next order gets the next number in sequence.

Summing Up

This trigger ensures that each order moving from Draft to Submitted gets a unique, sequential order number in the format ABC-00001, ABC-00002, and so on. By using a dedicated counter record with FOR UPDATE, it prevents conflicts when multiple users submit orders at the same time. This guarantees consistency and avoids duplicate numbering.

While effective, this solution introduces complexity and requires careful testing to handle concurrency and performance. In many cases, Salesforce’s built-in Auto Number field with a supporting formula can achieve the same business goal with less effort, maintenance, and risk.

Master Salesforce with Expert Training in Hyderabad

Kickstart your career with our comprehensive Salesforce training in Hyderabad. Whether you’re new to Salesforce or looking to enhance your skills, our program offers specialized tracks in Salesforce Administration, Development, and Artificial Intelligence. We equip you with the knowledge and hands-on experience necessary to excel in the tech industry.

Our training program includes interactive modules, real-world projects, and expert-led sessions to ensure you’re fully prepared for certification exams and interviews. Our experienced instructors provide personalized attention, making even complex concepts easy to understand.

Start your journey to success today! Enroll for the free demo and set yourself on the path to a rewarding future!!!