function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lily ChanLily Chan 

Case number with special format by sequence

Our case maganement is using for customer complaint, internal, safty different types.
Each type needs to have it's own unique case number with special format by sequence.
How can I achieve that?

For exsample,
first person create the case, choose type as "customer complaint", case number will be CCN-0001.
second person create the case, choose type as "safty", case number will be SFN-0001.
third persion create the case, choose type as "customer complaint", case number will be CCN-0002.
etc.

Please help with this question.
Thank you!
Best Answer chosen by Lily Chan
Bryan JamesBryan James
Alright so I came up with a solution that seems to be working. So the first thing I did was create 2 fields.
The first field is a number field that you can leave hidden.
The second field is a text formula field that reference the numberfield in order to determine what prefix to apply based on the case type.
The formula is as follows:
CASE( Type , "Customer Complaint", "CCN-" & TEXT(Case_Number__c), "SAFTY", "SFN-"&TEXT(Case_Number__c),"Internal", "INT-"&TEXT(Case_Number__c),TEXT(Case_Number__c))

Then create a trigger that will pull the last created case of each type based on case types this is done in case there is an issue where a large group of cases with different case types are bulk inserted.

trigger CaseNumberUpdateTrigger on Case (before insert) {
    List<Case> caseList = trigger.new; //list of all the cases being inserted
    Set<String> caseTypes = new Set<String>();//Set to hold only unique case types
    Map<String,Integer> caseMap = new Map<String,Integer>(); //Map to store the current counter of each case type in the set
    
    /**
     * Loop through all the cases and build a set of unique case sets.
     */
    for(Case ct : caseList)
    {
        caseTypes.add(ct.Type);
    }
    
    /**
     * Loop through the set of caseTypes and query for the most recent created record of each type.
     */
    for(String ct:caseTypes)
    {
        /**
         * Use a list even though we are limiting it to only 1 to prevent from a null object error         * 
         */  
        List<Case> caseNumber = [Select Case_Number__c From Case Where Type=:ct Order By CreatedDate DESC LIMIT 1];
        
        /**
         * If our list size is less than 1 meaning it returned no records set the caseMap count to 0
         */ 
        if(caseNumber.size() < 1){
            caseMap.put(ct,0);//put the case type and 0 into the Map
        } else {
            caseMap.put(ct,Integer.valueOf(caseNumber[0].Case_Number__c));//put the case type and Case_Number of the most recently created case of that type into the Map
        }
    }
    
    /**
     * Loop through the cases in the trigger one last time 
     */ 
    for(Case ct:caseList)
    {
        ct.Case_Number__c = caseMap.get(ct.Type) + 1; //Assign the cases Case_Number to the current caseMap Value +1
        caseMap.put(ct.Type,caseMap.get(ct.Type)+1);//Increment the current caseMap Value by 1
    }
}

Hope this helps you out! Good luck!

All Answers

Bryan JamesBryan James
Alright so I came up with a solution that seems to be working. So the first thing I did was create 2 fields.
The first field is a number field that you can leave hidden.
The second field is a text formula field that reference the numberfield in order to determine what prefix to apply based on the case type.
The formula is as follows:
CASE( Type , "Customer Complaint", "CCN-" & TEXT(Case_Number__c), "SAFTY", "SFN-"&TEXT(Case_Number__c),"Internal", "INT-"&TEXT(Case_Number__c),TEXT(Case_Number__c))

Then create a trigger that will pull the last created case of each type based on case types this is done in case there is an issue where a large group of cases with different case types are bulk inserted.

trigger CaseNumberUpdateTrigger on Case (before insert) {
    List<Case> caseList = trigger.new; //list of all the cases being inserted
    Set<String> caseTypes = new Set<String>();//Set to hold only unique case types
    Map<String,Integer> caseMap = new Map<String,Integer>(); //Map to store the current counter of each case type in the set
    
    /**
     * Loop through all the cases and build a set of unique case sets.
     */
    for(Case ct : caseList)
    {
        caseTypes.add(ct.Type);
    }
    
    /**
     * Loop through the set of caseTypes and query for the most recent created record of each type.
     */
    for(String ct:caseTypes)
    {
        /**
         * Use a list even though we are limiting it to only 1 to prevent from a null object error         * 
         */  
        List<Case> caseNumber = [Select Case_Number__c From Case Where Type=:ct Order By CreatedDate DESC LIMIT 1];
        
        /**
         * If our list size is less than 1 meaning it returned no records set the caseMap count to 0
         */ 
        if(caseNumber.size() < 1){
            caseMap.put(ct,0);//put the case type and 0 into the Map
        } else {
            caseMap.put(ct,Integer.valueOf(caseNumber[0].Case_Number__c));//put the case type and Case_Number of the most recently created case of that type into the Map
        }
    }
    
    /**
     * Loop through the cases in the trigger one last time 
     */ 
    for(Case ct:caseList)
    {
        ct.Case_Number__c = caseMap.get(ct.Type) + 1; //Assign the cases Case_Number to the current caseMap Value +1
        caseMap.put(ct.Type,caseMap.get(ct.Type)+1);//Increment the current caseMap Value by 1
    }
}

Hope this helps you out! Good luck!
This was selected as the best answer
Bryan JamesBryan James
In the Queries Where clause add a second conditional to account for previous unnumbered records.
AND Case_Number__c != null
In full the query would look like
 List<Case> caseNumber = [Select Case_Number__c From Case Where Type=:ct AND Case_Number__c != null Order By CreatedDate DESC LIMIT 1];
Lily ChanLily Chan
Hi Bryan

It works! Thank you so much!

One more question, right now Case Number show CCN-1, CCN-2 etc. How can I make it to CCN-0001, CCN-0002 etc.

Thank you!
Lily ChanLily Chan
I found the answer about format~ Thank you, Bryan. :)
 
Bryan JamesBryan James
Awesome Lily, I was planning to try and dig in to it after work tonight.
Lily ChanLily Chan
Hi Bryan,

Here is my formula. See bold texts. Share with you. :)

Case Custom Field: Case # ~ Salesforce - Enterprise EditionCASE(CaseType__c,"QC-Customer Complaint","CCN-"&CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null),"QC-Internal","INT-"&CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null),"HR-Enrollment", "HRN-"&CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null),"IT-Support Ticket","ITN-"&CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null),"Purchasing-Purchase Request","PRN-"&CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null),CASE(LEN(TEXT(CaseCount__c)),1,"000"&TEXT(CaseCount__c),2,"00"&TEXT(CaseCount__c),3,"0"&TEXT(CaseCount__c),4,TEXT(CaseCount__c),null))

Thank you for helping. You are realy kind!
 
Lily ChanLily Chan
Just updated null to TEXT(CaseCount__c). :)