Increment Record based on field

Question

I'd like to increment fields based on the quote field, so when a quote is created, a custom field will increment depend on the region (each field will have format of "ZO - {000}). Each region should have it's own series of Unique numbers. For example: if it's a "US" region quote, "Quote Number1" custom field will increment (. If it's a "ROW" region quote, "Quote Number2" custom field will increment.

Standard "Autonumber" field works on any record creation, and not customized by record types..

UPDATE: I modified a trigger posted as an answer (@user2590, see below your modified code). I want the autonumbers to work only for "Invoice" record type in Quotes ("Quote record type should be affected"). I assume it is simpler than the original code but somehow it doesn't work...I'm getting: "System.NullPointerException: Attempt to de-reference a null object". Can you have a look and see what's not working?

trigger SampleAutoNumberRegion on Quote (before insert){
String[] name = new String[]{'Invoice'};

Map<Id, RecordType> regions = new Map<Id,RecordType>([SELECT Name from RecordType WHERE sObjectType = 'Quote' AND Name IN :name]);

AggregateResult res = [SELECT MAX(US_Invoice__c) max1, MAX(ROW_Invoice__c) max2 FROM Quote WHERE RecordType.Name IN :name ];


Integer max1 = Integer.valueOf(((String)res.get('max1')).substringAfter('- ') );
Integer max2 = Integer.valueOf(((String)res.get('max2')).substringAfter('- ') );


for(Quote theQuote : trigger.new){    


   if(thequote.Opportunity.Account.Region__c.contains('US')){   //region corresponds to 'US'
        String num1 = String.valueOf( (max1 != null) ? ++max1 : 1 );//will use the incremented value of max unless max was null (else use 1 instead)

        for(Integer i=0; i< (3 - num1.length() ); i++){ //should pad with leading zeros to ensure format of {000}
            num1 = '0' + num1;
        }
        theQuote.US_Invoice__c = 'ZO - ' + num1;

    }

     else if(!thequote.Opportunity.Account.Region__c.contains('US')) {

        String num2 = String.valueOf( (max2 != null) ? ++max2 : 1 );
        for(Integer i=0; i< (3 - num2.length() ); i++){ //should pad with leading zeros to ensure format of {000}
            num2 = '0' + num2;
        }
        theQuote.ROW_Invoice__c = 'ZO - ' + num2;
   }

}
}

Answer 1

Updated to include sample code You should be able to use a trigger on Quote, acting before insert (and maybe update as well, depending on your needs).

I assume that your recordtype's name corresponds to the region.

trigger Sample on Quote (before insert){
String[] names = new String[]{'US','ROW'};
Map<Id, RecordType> regions = new Map<Id,RecordType>([SELECT Name from RecordType WHERE sObjectType = Quote AND Name IN :names]);
AggregateResult res = [SELECT MAX(Quote_Number1__c) max1, MAX(Quote_Number2__c) max2 FROM Quote WHERE RecordType.Name IN :names];
Integer max1 = Integer.valueOf(((String)res.get('max1')).right(3) );
Integer max2 = Integer.valueOf(((String)res.get('max2')).right(3) );

for(Quote theQuote : trigger.new){

    if( regions.get(theQuote.RecordTypeId).Name == 'US'){   //region corresponds to 'US'
        String num1 = String.valueOf( (max1 != null) ? ++max1 : 1 );//will use the incremented value of max unless max was null (else use 1 instead)
        for(Integer i=0; i< (3 - num1.length() ); i++){ //should pad with leading zeros to ensure format of {000}
            num1 = '0' + num1;
        }
        theQuote.Quote_Number1__c = 'ZO - ' + num;
    }else if( regions.get(theQuote.RecordTypeId).Name == 'ROW'){    //region corresponds to 'ROW' 
        String num2 = String.valueOf( (max2 != null) ? ++max2 : 1 );
        for(Integer i=0; i< (3 - num2.length() ); i++){ //should pad with leading zeros to ensure format of {000}
            num2 = '0' + num2;
        }
        theQuote.Quote_Number2__c = 'ZO - ' + num;
    }
}
}

Answer 2

Food for thought, if Region was promoted to a custom object, then the number could be generated declaratively. Worth testing the deletion case behaves is as you expect.

Region object:

• Field QuoteCount__c is a Roll-Up Summary (MAX Quote Sequence__c)

Quote object:

• Field Region__c is a Master-Detail(Region)
• Field Sequence__c is a Number
• SequenceIncrement is a Workflow Rule creating Sequence__c with Region__r.QuoteCount__c + 1