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
Ashu sharma 38Ashu sharma 38 

How to prevent from overlap the Dates with unique field values

Hi,

I have a requirement,in a object i have to restrict for  crearting records on below case:
Suppose I have created a records and on custom field(Test_code__c) I have put value:"ABC",and start date-->29/11/2019
End date:->29/12/2019

So in next records if I tried to create the record with same value 'ABC',the Start date and End should not be overlaped.
Best Answer chosen by Ashu sharma 38
Gian Piere VallejosGian Piere Vallejos
Hello, 

I'm using as example the Contact Object and I'm using the LastName as "Test_code__c" you referred. Also, I created two custom fields for the Contact call Start_Date__c, End_Date__c. The idea is create a before Trigger for handle what you want.
  
trigger ContactTriggerHandler on Contact (before insert, before update) {
    if( Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate) ){
        List <Contact> newContactData = Trigger.new; //Getting data of the current Contact that will be add
        String newName = newContactData.get(0).LastName;
        Date newStartDate = newContactData.get(0).Start_Date__c;
        Date newEndDate = newContactData.get(0).End_Date__c;
        
        //Getting the all Contacts with the same Last Name
        List <Contact> contactsWithSameLastName = [Select Id, LastName, Start_Date__c, End_Date__c FROM Contact WHERE LastName =: newName];
        
        if( !contactsWithSameLastName.isEmpty() ){
            for( Contact curContact : contactsWithSameLastName ){ //For all Contacts with same Last Name that the new evaluate if dates overlaps
                if( curContact.Start_Date__c <= newEndDate && curContact.End_Date__c >= newStartDate ){
                    Trigger.new[0].addError('You cannot overlap dates in same Contact'); //Show error in the new record
                    break;
                }
            }
        }
    }
}

I hope this can help you!

All Answers

Gian Piere VallejosGian Piere Vallejos
Hello, 

I'm using as example the Contact Object and I'm using the LastName as "Test_code__c" you referred. Also, I created two custom fields for the Contact call Start_Date__c, End_Date__c. The idea is create a before Trigger for handle what you want.
  
trigger ContactTriggerHandler on Contact (before insert, before update) {
    if( Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate) ){
        List <Contact> newContactData = Trigger.new; //Getting data of the current Contact that will be add
        String newName = newContactData.get(0).LastName;
        Date newStartDate = newContactData.get(0).Start_Date__c;
        Date newEndDate = newContactData.get(0).End_Date__c;
        
        //Getting the all Contacts with the same Last Name
        List <Contact> contactsWithSameLastName = [Select Id, LastName, Start_Date__c, End_Date__c FROM Contact WHERE LastName =: newName];
        
        if( !contactsWithSameLastName.isEmpty() ){
            for( Contact curContact : contactsWithSameLastName ){ //For all Contacts with same Last Name that the new evaluate if dates overlaps
                if( curContact.Start_Date__c <= newEndDate && curContact.End_Date__c >= newStartDate ){
                    Trigger.new[0].addError('You cannot overlap dates in same Contact'); //Show error in the new record
                    break;
                }
            }
        }
    }
}

I hope this can help you!
This was selected as the best answer
Ashu sharma 38Ashu sharma 38
Hi Gian Piere Vallejos,

Thanks It works fine for me!!!!



 
Ashu sharma 38Ashu sharma 38
Hi Gian Piere Vallejos,

how to write Test class for above code.
Thanks
Andrew GAndrew G
I would suggest a good search of the forums to find previous examples.

You would then perhaps find previous posts like this one:

https://developer.salesforce.com/forums/ForumsMain?id=9062I000000IKZPQA4
Gian Piere VallejosGian Piere Vallejos
Ashu, you can use this as test class with 100% coverage. 
@isTest
public class ContactTriggerHandlerTest {    
    @isTest static void contactTriggerHandlerTest2(){
        Boolean exceptionThrown = false;
        Contact c1 = new Contact();
        c1.LastName = 'ABC';
        c1.Start_Date__c = Date.newInstance(2019, 11, 20);
        c1.End_Date__c = Date.newInstance(2019, 11, 24);
        
        insert c1;
        
        try{ 
            Contact c2 = new Contact();
            c2.LastName = 'ABC';
            c2.Start_Date__c = Date.newInstance(2019, 11, 18);
            c2.End_Date__c = Date.newInstance(2019, 11, 26);
            insert c2;
        }catch(Exception e){
            exceptionThrown = true;
            Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Contact')) ? true : false; 
            System.AssertEquals(true, expectedExceptionThrown, e.getMessage()); 
        }
    }
}

 
Ashu sharma 38Ashu sharma 38
Hi Gian Piere Vallejos,

Its works but I am not getting full code coverage on trigger.new add error.(80% code coverage)User-added image

and one thing in test class u have written in boolean :
Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Contact')) ? true : false;
so can u explain me why u have written ? true : false


Thanks
 
Gian Piere VallejosGian Piere Vallejos
Weird though, I'm getting 100% coverage. Try this out:  
@isTest
public class ContactTriggerHandlerTest {    
    @isTest static void contactTriggerHandlerTest2(){
        Boolean exceptionThrown = false;
        try{ 
            Contact c1 = new Contact();
            c1.LastName = 'ABC';
            c1.Start_Date__c = Date.newInstance(2019, 11, 20);
            c1.End_Date__c = Date.newInstance(2019, 11, 24);
            
            insert c1;
            
            Contact c2 = new Contact();
            c2.LastName = 'ABC';
            c2.Start_Date__c = Date.newInstance(2019, 10, 20);
            c2.End_Date__c = Date.newInstance(2019, 10, 24);
            
            insert c2;
            
            Contact c3 = new Contact();
            c3.LastName = 'ABC';
            c3.Start_Date__c = Date.newInstance(2019, 11, 18);
            c3.End_Date__c = Date.newInstance(2019, 11, 26);
            insert c3;
        }catch(Exception e){
            exceptionThrown = true;
            Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Contact')) ? true : false; 
            System.AssertEquals(true, expectedExceptionThrown, e.getMessage()); 
        }
    }
}

Basically when we are using "addError" function to code will fire an exception, if we don't handle that exception the test won't pass. However, to make sure the exception is the error message we had on the "addError" part, we are using a flag setting it true if the message we get in the exception is 'You cannot overlap dates in same Contact'.
Ashu sharma 38Ashu sharma 38
Hi,

As I am activate both my trigger and Process builder but getting error.User-added image

in my process builder I have update the status picklist field based on comparsion on current date,startDate,end date.
User-added image

User-added image

Any suggestions.
Ashu sharma 38Ashu sharma 38
Hi,

I am getting 70% code coverage.
@isTest
private class StartEnddateOverlapPreventionHandlerTest {
    @isTest static void partnerSaleTermTriggerHandlerTest(){
        Boolean exceptionThrown = false;
        Partner_Sales_Term__c pst1=new Partner_Sales_Term__c(); 
        pst1.Partner_Code__c='ABC';
        pst1.Sales_Cycle_Start_Date__c=Date.newInstance(2019, 12, 2);
        pst1.Sales_Cycle_End_Date__c=Date.newInstance(2019, 12, 12);
        insert pst1;

        try{ 
            Partner_Sales_Term__c pst2=new Partner_Sales_Term__c();
            pst2.Partner_Code__c = 'ABC';
            pst2.Sales_Cycle_Start_Date__c = Date.newInstance(2019, 11, 18);
            pst2.Sales_Cycle_End_Date__c = Date.newInstance(2019, 11, 26);
            insert pst2;

        }catch(Exception e){
            exceptionThrown = true;
            
           Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Partner Code')) ; 
            System.AssertEquals(true, expectedExceptionThrown, e.getMessage()); 
          system.assertEquals(true, expectedExceptionThrown); 
        }
    }
}

and my class
public class StartEnddateOverlapPreventionHandler {
    public static  Boolean isFirstTime = true;
    public static void StartEnddateOverlapPreventionMethod(list<Partner_Sales_Term__c> newPartenerSaleTermData){
        //  List <Partner_Sales_Term__c> newPartenerSaleTermData = Trigger.new; //Getting data of the current record that will be add
        if(newPartenerSaleTermData!=null && !newPartenerSaleTermData.isEmpty() && isFirstTime==true){
            set<string> partnerCodeSet=new set<string>();
                            if(trigger.isInsert){
                isFirstTime=false;
                }
            for(Partner_Sales_Term__c ps:newPartenerSaleTermData){
                partnerCodeSet.add(ps.Partner_Code__c);
            }
            
            if(!partnerCodeSet.isEmpty())
            {
            List <Partner_Sales_Term__c> partnerCodewitSamePartnerCode = [Select Id, name,Partner_Code__c, Sales_Cycle_Start_Date__c, Sales_Cycle_End_Date__c FROM Partner_Sales_Term__c WHERE Partner_Code__c IN: partnerCodeSet];
            if( !partnerCodewitSamePartnerCode.isEmpty() && partnerCodewitSamePartnerCode!=null){
                for(Partner_Sales_Term__c ps:newPartenerSaleTermData){
                    for(Partner_Sales_Term__c samePSCode :partnerCodewitSamePartnerCode){
                        system.debug('@@@startDate ' +samePSCode.Sales_Cycle_Start_Date__c);
                        system.debug('@@@end Date ' +samePSCode.Sales_Cycle_End_Date__c);
                        
                        if(ps.Sales_Cycle_End_Date__c >=samePSCode.Sales_Cycle_Start_Date__c && ps.Sales_Cycle_Start_Date__c<=samePSCode.Sales_Cycle_End_Date__c){
                               ps.addError('You cannot overlap dates in same Partner Code');
                           }
                    }
                }
            }
        }
    }       
    }

}
Gian Piere VallejosGian Piere Vallejos
Well, are you calling StartEnddateOverlapPreventionHandler class from a trigger? otherwise, things would change. Where "newPartenerSaleTermData" list came from?
Ashu sharma 38Ashu sharma 38
Hi Gian Piere Vallejos,

Yes I am using handler class for this.
newPartenerSaleTermData:trigger.new
as 73% code coverage I am getting.
Ashu sharma 38Ashu sharma 38
Hi ,Gian Piere Vallejos


public class StartEnddateOverlapPreventionHandler {
    public static  Boolean isFirstTime = true;
    public static void StartEnddateOverlapPreventionMethod(list<Partner_Sales_Term__c> newPartenerSaleTermData){
        //  List <Partner_Sales_Term__c> newPartenerSaleTermData = Trigger.new; //Getting data of the current record that will be add
        if(newPartenerSaleTermData!=null && !newPartenerSaleTermData.isEmpty() && isFirstTime==true){
            set<string> partnerCodeSet=new set<string>();
                            if(trigger.isInsert){
                isFirstTime=false;
                }
            for(Partner_Sales_Term__c ps:newPartenerSaleTermData){
                partnerCodeSet.add(ps.Partner_Code__c);
            }
            
            if(!partnerCodeSet.isEmpty())
            {
            List <Partner_Sales_Term__c> partnerCodewitSamePartnerCode = [Select Id, name,Partner_Code__c, Sales_Cycle_Start_Date__c, Sales_Cycle_End_Date__c FROM Partner_Sales_Term__c WHERE Partner_Code__c IN: partnerCodeSet];
            if( !partnerCodewitSamePartnerCode.isEmpty() && partnerCodewitSamePartnerCode!=null){
                for(Partner_Sales_Term__c ps:newPartenerSaleTermData){
                    for(Partner_Sales_Term__c samePSCode :partnerCodewitSamePartnerCode){
                        system.debug('@@@startDate ' +samePSCode.Sales_Cycle_Start_Date__c);
                        system.debug('@@@end Date ' +samePSCode.Sales_Cycle_End_Date__c);
                        
                        if(ps.Sales_Cycle_End_Date__c >=samePSCode.Sales_Cycle_Start_Date__c && ps.Sales_Cycle_Start_Date__c<=samePSCode.Sales_Cycle_End_Date__c){
                               ps.addError('You cannot overlap dates in same Partner Code');
                           }
                    }
                }
            }
        }
    }       
    }

}
=========
@isTest
private class StartEnddateOverlapPreventionHandlerTest {
    @isTest static void partnerSaleTermTriggerHandlerTest(){
        Boolean exceptionThrown = false;
        Partner_Sales_Term__c pst1=new Partner_Sales_Term__c(); 
        pst1.Partner_Code__c='ABC';
        pst1.Sales_Cycle_Start_Date__c=Date.newInstance(2019, 12, 2);
        pst1.Sales_Cycle_End_Date__c=Date.newInstance(2019, 12, 12);
        insert pst1;

        try{ 
            Partner_Sales_Term__c pst2=new Partner_Sales_Term__c();
            pst2.Partner_Code__c = 'ABC';
            pst2.Sales_Cycle_Start_Date__c = Date.newInstance(2019, 11, 18);
            pst2.Sales_Cycle_End_Date__c = Date.newInstance(2019, 11, 26);
            insert pst2;

        }catch(Exception e){
            exceptionThrown = true;
            
           Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Partner Code')) ; 
            System.AssertEquals(true, expectedExceptionThrown, e.getMessage()); 
          system.assertEquals(true, expectedExceptionThrown); 
        }
    }
}

//Trigger Handler
trigger StartEnddateOverlapPrevention on Partner_Sales_Term__c (before insert,before update) {

    if( Trigger.isBefore && (Trigger.isInsert ||Trigger.isUpdate ) ){
        StartEnddateOverlapPreventionHandler.StartEnddateOverlapPreventionMethod(trigger.new);
}
}
Ashu sharma 38Ashu sharma 38
Hi Gian Piere Vallejos,

As not able to complete the above code coverage.
User-added image

Thanks..
Gian Piere VallejosGian Piere Vallejos
Hey Ashu,

You can insert this code by Anonymous Window (Debug/Open Execute Anonymous Window):  
Partner_Sales_Term__c pst1=new Partner_Sales_Term__c(); 
pst1.Name = 'Parter Sales Test';
pst1.Partner_Code__c='CODE001';
pst1.Sales_Cycle_Start_Date__c=Date.newInstance(2019, 10, 2);
pst1.Sales_Cycle_End_Date__c=Date.newInstance(2019, 10, 12);            
            
insert pst1;

and then you can run this test class:  
@isTest
public class StartEnddateOverlapPreventionHandlerTest {
    @isTest(SeeAllData=true)
    static void testWithAllDataAccess() {
    	Boolean exceptionThrown = false;
        try{
        	Partner_Sales_Term__c pst2=new Partner_Sales_Term__c();
            pst2.Name = 'Partner Sales Test';
            pst2.Partner_Code__c = 'CODE001';
            pst2.Sales_Cycle_Start_Date__c = Date.newInstance(2019, 10, 2);
            pst2.Sales_Cycle_End_Date__c = Date.newInstance(2019, 10, 6);
            
            insert pst2;
                    
        }catch(Exception e){
            exceptionThrown = true;
            Boolean expectedExceptionThrown =  (e.getMessage().contains('You cannot overlap dates in same Partner Code')) ? true : false; 
            System.AssertEquals(true, expectedExceptionThrown, e.getMessage()); 
        }
    }
}

A test class doesn't consider the data at the org unless we use "SeeAllData=true" on it. However, in this case our SOQL at "partnerCodewitSamePartnerCode" list is returning an empty list, what we are going to do is insert a real data by Anonymous Window and then inserting a record from test class that provoke the exception and showing the message 'You cannot overlap dates in same Partner Code'. For deploy, you can include in your test class a Partner_Code__c from a real record and a range of Sales Cycle data related to that real code.

This was very hard to solve, I hope it helps you!
Gian Piere VallejosGian Piere Vallejos
And maybe this article could help you with the Process Builder issue: https://salesforce.stackexchange.com/questions/138247/how-to-prevent-process-builders-to-fire-triggers-on-an-update (https://salesforce.stackexchange.com/questions/138247/how-to-prevent-process-builders-to-fire-triggers-on-an-update" target="_blank)
Swetha EramaliSwetha Eramali
Hi Gian Piere Vallejos,
I tried the above code with similar requirement but in my case even if one record exists with the same combination its throwing an error. Basically, it should prevent only if with same id same name data overlapping should happen. Could yu please help me with this