# Help with Trigger to that will index Account Number field

I have an urgent requirement as follows:

--Whenever the Opportunity Probability is moved to 80%, an Integer should be added to the AccountNumber field on the Account object. There is a lookup relationship between the Opportunity(child) and Account(parent) object.

--The Account number will never be changed by the Trigger; only set once if the AccountNumber field is NULL when the Opportunity Probability gets to 80%.

--The trigger will find the highest number in the appropriate number range and assign a number one higher.

--The number assigned by the trigger will depend on the type of Account(Partner or User) & the region of the Account (Americas or Offshore). as follows:

`````` If Account = Partner & Region = Americas assign a number between 3000-3999
If Account = Partner & Region = Offshore assign a number between 9000-9999
If Account = User & Region = Americas assign a number between 1000-1999
If Account = User & Region = Offshore assign a number between 8000-8999
``````

Here is what I have so far but i'm stuck with the logic:

`````` trigger AssignAccountNumber on Opportunity (after insert, after update){
list<opportunity> oppsToUpdate = new list<opportunity>();
for (Opportunity p:Trigger.new){
if ((p.Probability >= 80)&&(p.accountnumber==null)){
//your processing for pre-defined range, and update p
}
}
if(oppsToUpdate.size()>0)update oppsToUpdate;
}
``````

Any direction will be appreciated

• Your first loop should just gather a set of Opportunity.AccountId values. You can then use that to query the Accounts (into a map keyed by the Account Id), then do your number calculations and finally update the Accounts that you queries. May 27, 2014 at 15:35
• Thanks Keith C for the direction! Could you post an example of code that would process the predefined range e.g assigning a number between 1000-1999? May 27, 2014 at 16:36

The assigning numbers part is awkward if you want to avoid duplicates when simultaneous requests are made.

One way to accomplish that is to use for update. Let me caution you that I have never written code that uses this so the code below is based on what I read in the documentation not real experience.

The idea is to dedicate a single row of a custom SObject (that you'll also have to be careful to insert to start with) and ensure that only one request can update it; other requests suffer a `QueryException` while the "for update" lock is on:

``````Number__c n;
Boolean ok;
do {
try {
ok = true;
n = [
select Ones__c, Threes__c, Eights__c, Nines__c
from Number__c
limit 1
for update
];
if (...) {
n.Ones__c = n.Ones__c + 1;
} else if (...) {
n.Threes__c = n.Threes__c + 1;
} else if (...) {
n.Eights__c = n.Eights__c + 1;
} else if (...) {
n.Nines__c = n.Nines__c + 1;
}
update n;
} catch (QueryException e) {
ok = false;
}
} while (!ok);

... use the appropriate n field value e.g. n.Ones__c
``````

Another way would be to define 4 custom objects with an auto-number field on each (that is set to start at the correct number). You can then allocate a number like this:

``````Ones__c ones = new Ones__c();
insert ones;
Integer number = [select AutoNumber__c from Ones__c where id = :ones.Id];
delete ones;
Database.emptyRecycleBin(ones);
``````

Another way if you don't mind the occasional duplicate you could use an aggregate query and find the current max value and use one more than that. That would be simpler.

• Instead of a custom object though, I'd suggest a custom setting. May 27, 2014 at 17:14
• @sfdcfox Yeah meant to mention that I wasn't sure about "for update" on those. I Googled for number allocators for Salesforce but didn't find a hit - presume there are some good posts somewhere on the subject with code that is proven to work... May 27, 2014 at 17:16
• Thank you! Let me try this out. I'll post the final trigger once I'm done. May 27, 2014 at 17:35
• I'm trying to use a custom setting. Should I use a single custom setting with 4 custom number fields, or 4 separate custom settings each with a dedicated custom number field? Also, autonumber is not available in custom settings. May 27, 2014 at 20:59
• @user8556 If "for update" does work with custom settings then you will need 4 fields to track the 4 sequences. May 27, 2014 at 21:22