+ Start a Discussion
salesforce@14salesforce@14 

To autopopulate the number field automatically when i delete the records using trigger.

Hi,
  I need to autopopulate the number datatype field after the record is deleted.
For example,
I have 5 records and their serial numbers are  1,2,3,4 and 5. If i delete the 3rd record , then the serial number of the 4th record should be automatically populated as 3 and 5th records as 4.
How to achieve this delete operation?

My Trigger Code:

trigger RecordSteps on Record__c(before insert,after delete) 
    {
        List<Record__c> cp = new List<Record__c>(); 
        set<id> id = new set<id>();
        List <AggregateResult> requirements = new List<AggregateResult>();  
       if (Trigger.isBefore && Trigger.isInsert)
       { 
        for(Record__c pt:trigger.new)
        {
         requirements=[select Count(Seq__c) from Record__c];
         if(requirements.size() > 0)
         {
         for(AggregateResult ar:requirements)
         {
         pt.Seq__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
          }
      }
      }
}
      if (Trigger.isAfter&& Trigger.isDelete) 
      {
      for(Record__c pt1:trigger.old)
        {
          }
          }   
     }
Best Answer chosen by salesforce@14
MithunPMithunP
Hi Sushma,

Try this updated code.
trigger RecordSteps on Record__c(before insert,before delete) 
    {
        List<Record__c> cp = new List<Record__c>(); 
        set<id> id = new set<id>();
        List <AggregateResult> requirements = new List<AggregateResult>(); 
        
       if (Trigger.isBefore && Trigger.isInsert)
       { 
        List <Record__c> reqSize = [select id,Seq__c from Record__c]; 
        for(Record__c pt:trigger.new)
        {
         requirements=[select Count(Seq__c) from Record__c];
         system.debug('+++++++++++'+requirements.size());
         system.debug('++++++reqSize+++++'+reqSize.size());
         if(requirements.size() > 0)
         {
         for(AggregateResult ar:requirements)
         {
         pt.Seq__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')))+1;
          }
      }
         if(reqSize.size() <= 0){
          pt.Seq__c = 1;
         }
      }
}
      if (Trigger.isBefore && Trigger.isDelete) 
      {
         List<Record__c> allRecs = [select id,Seq__c from Record__c where id not in: trigger.oldmap.keyset()];
         Map<Decimal,Record__c> RecMap = new Map<Decimal,Record__c>();
                 
         for(Record__c rcs: allRecs){
              
              RecMap.put(rcs.Seq__c,rcs);
         }
         for(Record__c pt1:trigger.old)
          {
              for(Record__c rc : allRecs){
                  if(rc.Seq__c > pt1.Seq__c){
                      rc.Seq__c-=  1;
                  }
              }
        
        
          }
          update allRecs;
          }   
     }

Best Regards,
Mithun.

All Answers

MithunPMithunP
Hi Sushma,

You can try below updated code.
trigger RecordSteps on Record__c(before insert,before delete) 
    {
        List<Record__c> cp = new List<Record__c>(); 
        set<id> id = new set<id>();
        List <AggregateResult> requirements = new List<AggregateResult>();  
       if (Trigger.isBefore && Trigger.isInsert)
       { 
        for(Record__c pt:trigger.new)
        {
         requirements=[select Count(Seq__c) from Record__c];
         if(requirements.size() > 0)
         {
         for(AggregateResult ar:requirements)
         {
         pt.Seq__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')));
          }
      }
      }
}
      if (Trigger.isBefore && Trigger.isDelete) 
      {
         List<Record__c> allRecs = [select id,Seq__c from Record__c where id not in: trigger.oldmap.keyset()];
         Map<Decimal,Record__c> RecMap = new Map<Decimal,Record__c>();
                 
         for(Record__c rcs: allRecs){
              
              RecMap.put(rcs.Seq__c,rcs);
         }
         for(Record__c pt1:trigger.old)
          {
              for(Record__c rc : allRecs){
                  if(rc.Seq__c > pt1.Seq__c){
                      rc.Seq__c-=  1;
                  }
              }
        
        
          }
          update allRecs;
          }   
     }

Mark this as Best Answer, if it solves your problem.

Best Regards,
Mithun.
salesforce@14salesforce@14
Thanks MithunP,

  I got output. 

when i am inserting new record the serial number is starting from 0 but i want to start  it from 1.

How to do this?

Thanks for your support.
MithunPMithunP
Hi Sushma,

Try this updated code.
trigger RecordSteps on Record__c(before insert,before delete) 
    {
        List<Record__c> cp = new List<Record__c>(); 
        set<id> id = new set<id>();
        List <AggregateResult> requirements = new List<AggregateResult>(); 
        
       if (Trigger.isBefore && Trigger.isInsert)
       { 
        List <Record__c> reqSize = [select id,Seq__c from Record__c]; 
        for(Record__c pt:trigger.new)
        {
         requirements=[select Count(Seq__c) from Record__c];
         system.debug('+++++++++++'+requirements.size());
         system.debug('++++++reqSize+++++'+reqSize.size());
         if(requirements.size() > 0)
         {
         for(AggregateResult ar:requirements)
         {
         pt.Seq__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')))+1;
          }
      }
         if(reqSize.size() <= 0){
          pt.Seq__c = 1;
         }
      }
}
      if (Trigger.isBefore && Trigger.isDelete) 
      {
         List<Record__c> allRecs = [select id,Seq__c from Record__c where id not in: trigger.oldmap.keyset()];
         Map<Decimal,Record__c> RecMap = new Map<Decimal,Record__c>();
                 
         for(Record__c rcs: allRecs){
              
              RecMap.put(rcs.Seq__c,rcs);
         }
         for(Record__c pt1:trigger.old)
          {
              for(Record__c rc : allRecs){
                  if(rc.Seq__c > pt1.Seq__c){
                      rc.Seq__c-=  1;
                  }
              }
        
        
          }
          update allRecs;
          }   
     }

Best Regards,
Mithun.
This was selected as the best answer
salesforce@14salesforce@14

Thanks MithunP.
salesforce@14salesforce@14
Hi,

For example:
Object__c - parent object.
record__c - child object

I am inserting new record in record__c under A record in Object__c, the serial number is 1. If i insert new record in record__c under B record in Object__c means the serial number is populated as 2. But i need to populate as 1 instead of 2.
 How to do this?

Thanks.
 
Suraj GharatSuraj Gharat
Hi Sushma,

You may try below code :
 
trigger RecordSteps on Record__c (before insert,after delete) {
	
    /* This 'IF' handles serial numbers assignments for all new inserted records */
    if(Trigger.isBefore && Trigger.isInsert){
        /* Get the current count */
        List<Record__c> lstRecords=[SELECT Seq__c FROM Record__c ORDER BY Seq__c DESC LIMIT 1];
        Integer currentCount=(lstRecords!=null&&!lstRecords.isEmpty())?(Integer)lstRecords.get(0).Seq__c:0;
        
        /* Release unused memory */
        lstRecords=null;
        
        /* Assign record number for each new record according to current count */
        for(Record__c record:Trigger.new){
            record.Seq__c=currentCount+1;
            currentCount++;
        }
	}
    /* This 'else if' handles serial number updation as some records get deleted */
    else if(Trigger.isAfter && Trigger.isDelete){
        
        List<Integer> lstDeletedNumbders=new List<Integer>();
        Integer lowestDeletedNumber;
        
        for(Record__c record:Trigger.old){
        	/* Get the deleted serial numbers */
            lstDeletedNumbders.add(Integer.valueOf(record.Seq__c));
            
            /* Get the lowest deleted serial number  */
            if(	lowestDeletedNumber==null	||
               	record.Seq__c<lowestDeletedNumber
              )
   				lowestDeletedNumber=Integer.valueOf(record.Seq__c);
        }
        
        /* Sort the deleted serial numbers for ease in processing and to not miss anything */
        lstDeletedNumbders.sort();
        
        /* Get the all records for which serial numbers ought to be updated */
        List<Record__c> lstRecordsToBeUpdated = [SELECT Id,Seq__c FROM Record__c WHERE Seq__c > :lowestDeletedNumber ORDER BY Seq__c DESC];
        
        /* Update serial numbers */
        for(Record__c record:lstRecordsToBeUpdated)
            for(Integer i=lstDeletedNumbders.size()-1;i>=0;i--)
                if(lstDeletedNumbders.get(i)<record.Seq__c)
                    record.Seq__c--;
        
        if(!lstRecordsToBeUpdated.isEmpty())
            update lstRecordsToBeUpdated;
    }
}

The above code will work in all situations and will not run into the Salesforce goverer limits,unless there are more 10,000 rows to be updated. If the Record object holds more that 10k records then I'd urge you to use an asynchronous apex approach to do it, otherwise you'd hit 10k DML rows exception.
salesforce@14salesforce@14
Hi Suraj Gharat,

Thanks for your support.

The code which u mentioned above is updating the serial number continuously for all the records in object__c. But my requirement is like given below.

For example i created two records as Test and Test1 in Object__c.
1)I want to insert 100 records in Test, then the serial number should be 1 to 100.
2)Sameway if i insert 200 records in  Test1 ,then the serial number should starts from 1 and endup with 200.

Thanks.
Suraj GharatSuraj Gharat
Hi Sushma,

I did not get your requiremet. Here is what I understood:
  1. There is only one object "Object__c", for which you want this serial number logic.
  2. If there are zero records for "Object__c" object and you insert 100 records for this object, then all new 100 records should get the serial number from 1 to 100.
  3. Now let's say, if there are already 10 records for "Object__c" object and then you insert 100 records for this "Object__c" object,  then eventually all new 100 records should get the serial number from 11 to 110.
  4. And; If there are already 100 records for "Object__c" object and then you delete a record with serial number "11", then eventually there should be 99 records with serial numbers 1 to 99 (including 11).
Please correct me on my understanding.
salesforce@14salesforce@14
Hi Suraj Gharat,

Yes you are correct.

But there are two objects,
Object__c -> parent record
record__c -> child record 
These both have lookup relationship.
eg:
I am  inserting 100 records in record__c under Object__c(Test). The serial number should be populates as 1 to 100.
If i am inserting 200 records in record__c under Object__c(Test11). The serial number should be populates  as 1 to 200.

Thanks for your support.
 
Suraj GharatSuraj Gharat
Hi Sushma,

You may try the below updated code:
 
trigger RecordSteps on Record__c (before insert,after delete) {
    
    /* This 'IF' handles serial number assignments for all new inserted records */
    if(Trigger.isBefore && Trigger.isInsert){
		/* This set holds parent objects Ids */
        Set<Id> setParentIds=new Set<Id>();
        for(Record__c record:(List<Record__c>)Trigger.new)
            setParentIds.add(record.Object__c );
			
        /* Map to hold current count for each parent */
        Map<Id,Decimal> mapParentToCurrentCount=new Map<Id,Decimal>();
        
        /* Get the current count for each parent */
        for(AggregateResult result: [SELECT MAX(Seq__c),Object__c FROM Record__c WHERE Object__c IN :setParentIds GROUP BY Object__c])
            mapParentToCurrentCount.put((Id)result.get('Object__c'),(Decimal)result.get('expr0'));
        
        /* Assign record number for each new record according to current count of its parent */
        for(Record__c record:Trigger.new){
            Decimal currentCount=mapParentToCurrentCount.get(record.Object__c);
            currentCount=currentCount==null?1:(currentCount+1);
            record.Seq__c=currentCount;
            mapParentToCurrentCount.put(record.Object__c,currentCount);
        }
    }
    /* This 'ELSE If' handles serial number updation as some records get deleted */
    else if(Trigger.isAfter && Trigger.isDelete){
	
        Set<Id> setParentIds=new Set<Id>();
        Decimal lowestDeletedSerialNumber;
        Map<Id,List<Decimal>> mapParentToLstDeletedRecords=new Map<Id,List<Decimal>>();
        
        for(Record__c record:(List<Record__c>)Trigger.old){
			/* Get the parents' Ids */
            setParentIds.add(record.Object__c );
            
            if(mapParentToLstDeletedRecords.get(record.Object__c)==null)
                mapParentToLstDeletedRecords.put(record.Object__c,new List<Decimal>());
            
			/* Store each deleted serial number parent wise */
            mapParentToLstDeletedRecords.get(record.Object__c).add(record.Seq__c);
            
			/* Sort the deleted numbers so that we would not miss anything */
            mapParentToLstDeletedRecords.get(record.Object__c).sort();
            
			/* Infer lowest serial number, we could use it in next query */
            if(lowestDeletedSerialNumber==null||lowestDeletedSerialNumber>record.Seq__c)
                lowestDeletedSerialNumber=record.Seq__c;
                           
        }
        List<Record__c> lstRecordsRetrived = [SELECT Seq__c,Object__c FROM Record__c WHERE Object__c IN :setParentIds AND Seq__c > :lowestDeletedSerialNumber ORDER BY Seq__c DESC];
        
		/* Release unused memory */
        setParentIds=null;
        
        Map<Id,Record__c> mapIdToRecordsToBeUpdated=new Map<Id,Record__c>();
        
		/* Update serial numbers for records whose serial number was greater than the deleted serial number for given parent */
        for(Id parentId:mapParentToLstDeletedRecords.keyset()){
            for(Integer i=mapParentToLstDeletedRecords.get(parentId).size()-1;i>=0;i--){
                for(Record__c record:lstRecordsRetrived){
                    if(record.Object__c==parentId&&record.Seq__c>mapParentToLstDeletedRecords.get(parentId).get(i)){
                        record.Seq__c--;
						/* Update only records which get changed */
                        if(!mapIdToRecordsToBeUpdated.keyset().contains(record.Id))
                            mapIdToRecordsToBeUpdated.put(record.Id,record);
                    }
                }
            }
        }
        
		/* Release unused memory */
        mapParentToLstDeletedRecords=null;
        
        if(!mapIdToRecordsToBeUpdated.values().isEmpty())
            update mapIdToRecordsToBeUpdated.values();
       
    }
}

The above snippet should run correctly. This might hit the governor limits in case you have large record count for "Record__c" object. I think if your requirements allow, you should try it in asynchronous way.
Pathan SPathan S
I created One object Student in that object created on field No of students.
getting eror----Illegal assignment from Decimal to String
plz reply

trigger Records on Student__c(before insert,before delete)
    {
        List<Student__c> cp = new List<Student__c>();
        set<id> id = new set<id>();
        List <AggregateResult> requirements = new List<AggregateResult>();
        
       if (Trigger.isBefore && Trigger.isInsert)
       {
        List <Student__c> reqSize = [select id,NO_of_Student__c from Student__c];
        for(Student__c pt:trigger.new)
        {
         requirements=[select Count(NO_of_Student__c) from Student__c];
         system.debug('+++++++++++'+requirements.size());
         system.debug('++++++reqSize+++++'+reqSize.size());
         if(requirements.size() > 0)
         {
         for(AggregateResult ar:requirements)
         {
         pt.NO_of_Student__c = Decimal.ValueOf(String.ValueOf(ar.get('expr0')))+1;
          }
      }
         if(reqSize.size() <= 0){
          pt.NO_of_Student__c = 1;
         }
      }
}
      if (Trigger.isBefore && Trigger.isDelete)
      {
         List<Student__c> allRecs = [select id,NO_of_Student__c from Student__c where id not in: trigger.oldmap.keyset()];
         Map<Decimal,Student__c> RecMap = new Map<Decimal,Student__c>();
                 
         for(Student__c rcs: allRecs){
              
              RecMap.put(rcs.NO_of_Student__c,rcs);
         }
         for(Student__c pt1:trigger.old)
          {
              for(Student__c rc : allRecs){
                  if(rc.NO_of_Student__c > pt1.NO_of_Student__c){
                      rc.NO_of_Student__c-=  1;
                  }
              }
        
        
          }
          update allRecs;
          }   
     }
Johny SinsJohny Sins
This is Johny Sins from Plumbers In Georgia (https://loop.frontiersin.org/people/768062/bio). Very helpful information.
ravi sharma 148ravi sharma 148
Yes right. I will help you with a question. hdmovieshub (https://hdmovieshub.buzz/) love this valuable article increases me more to know to keep it up.