3

Our customer number ranges are different for each company we acquire. For example, company A has a customer number range that falls between 100,000-199,999. Company B has a number range that may fall between 200,000-250,000.

What we need is a way to automatically set the customer number for the correct company based on a field called Company Code.

Example: We have a new customer for company A. Company A's last customer is #127,002. The new customer number would be #127,003 and company B's number series would remain unchanged.

For right now, we would have this customer number stored in a separate field than the traditional Account Number field, as we are currently transitioning between ERP systems.

Any help would be greatly appreciated. Thank you!

2

I've found sequential numbering on Salesforce to be quite challenging, the more complex the requirement, the more challenging it gets. If you can rely on platform features (rather than code) to fulfill your requirement, then I believe you will have a better time of it.

However, based on what you're saying

Our customer number ranges are different for each company we acquire

Leads me to believe that you'll want to add new ranges as and when you acquire new companies, so I don't believe there is a simple 'platform' solution for you (hopefully, someone has a better idea).

Here is a solution I provided to a similar problem, which is far from ideal but seems to work.

  1. Create a custom object, call it e.g. Account Sequence Group. This can hold whatever additional information you like, but is essentially just a 'grouping' for each of your company/sequences
  2. Create another custom object, call it e.g. Account Sequence Number. Make it a child of both Account Group Sequence AND Account. Create an additional Sequence Number (numeric) field and any additional info you might wish to store. You will need to have something in place (probably a Trigger) to ensure that no duplicates of Company and Sequence Number occur.
  3. Create a new Rollup Summary field on Account, e.g. MAX(Sequence Number). This will allow you access to the sequence number from Account.

Structurally at least, this will allow you to maintain a list of sequences 'by company', for example:

Company A
    Account                 Sequence #
    -------------------     -------------------
    Oracle                  1
    Cisco                   2
    IBM                     3

Company B
    Account                 Sequence #
    -------------------     -------------------
    HP                      1
    Microsoft               2

You can then create a formula field on Account to present the sequence number as you wish.

Finally, you will need a Trigger on Account (insert) to create a new Account Sequence Number whenever a new Account is created. This trigger would simply create a new Account Sequence Number record, incrementing the sequence by 1 each time. The 'uniqueness' Trigger on Account Sequence Number would prevent duplicates should any occur.

I have provided this answer in the knowledge that it is far from ideal, but thought it worth presenting for others to scrutinise and also see if there are any better solutions.

  • Can you please be a little more specific with how the Account Group object would work? From what I'm understanding, you would create an Account Group object for company A, then each account would get it's own Sequence Number object. Wouldn't that be a lot of overhead in terms of objects? Also - how does this work for current accounts in the system? – Brock B. May 14 '15 at 15:07
  • 1
    Yes, I think you've understood correctly - the Account Group object is merely a way of segregating number ranges between different companies (I'm assuming accounts 'belong' to a given company). The data model is essentially allowing you to hold a set of sequences separately, but then providing a reference on the Account itself by using the rollup (perhaps a Lookup could be used also). You are right, it is a big overhead in terms of objects. For current accounts in the system, you'd have to run a 'backfill' script to generate a Sequence Number for each existing account. – Phil Hawthorn May 14 '15 at 15:14
  • Using custom objects... Just use row-level locking statements (SELECT ... FOR UPDATE), and you'll have your concurrent numbers, without duplicates, including ranges. – sfdcfox May 14 '15 at 16:28
  • Instead of the duplicate check trigger you mean? Good idea...thanks – Phil Hawthorn May 14 '15 at 16:31
  • I've used part of your answer for my solution. Rather than the overhead of a new record for each account, I have created an Account Group object, and inside of it contains Company Starting Number Ending Number Current Number and Company Code. On create of an account record, the trigger checks the company code and grabs the right Account Group and increments the Current Number by 1 and sets it to the Customer Number. Thank you very much for your help. It is working perfectly. – Brock B. May 14 '15 at 19:29
0

Just wanted to throw this out there... Wouldn't it be easier to use a company code to prefix the autonumber, to avoid issues you are certain to hit (for example, when company exceeds 100,000-199,999 band)?

Example:

  1. On your Company table, create a unique, case-sensitive field called "Company Code"
  2. Next, populate values for each company
  3. Create a workflow rule on the Child Object -- "Customer" and have it set the Customer Number field to Company.Company_Code__c + "-" + [salesforce autonumber value]

This will require no code, be much more intuitive for your end users, enable easier searching and use of the "rolodex" feature in list views, and queries should perform better.

  • Our ERP system that we are interfacing with is requiring us to use a seperate number series for each company. As for the upper limits of the numbers, the number is actually in the millions. I just used the hundred thousands as an example, as I don't know the exact number series at this time. – Brock B. May 14 '15 at 14:57
  • 2
    I like the idea of no code, but wouldn't you end up with sequences something line CMPA-000001, CMPA-000002, CMPB-000003 or am I misunderstanding your solution? – Phil Hawthorn May 14 '15 at 14:58
  • Right - is another issue I noticed with this solution as well. It seems like there could be large gaps in customer numbers between companies. – Brock B. May 14 '15 at 15:00
  • 1
    Indeed - believe me, i've spent more hours than enough trying to solve this issue (for transactional data, so its even worse!) - I really hope there is a better solution out there, but I'm yet to find it. – Phil Hawthorn May 14 '15 at 15:07
  • 1
    Yes, there would be large gaps in the sequence, but with this solution there is no need for the numerical categories since the company code is much more inuitive for end users. However, since there is the added requirement by the external ERP system, this would not be a viable solution for @BrockB. – James May 14 '15 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.