function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
sales4cesales4ce 

Trigger to prevent Date Overlap records-help!!

Hi,

 

I have a custom object , which includes three custom fields (Start date, End Date and Business Unit(Type: Picklist).

The three custom fields should be unique for a record, so the combination of these fields makes my record unique.

 

 

 

Example: start date: 05/20/2011, End Date: 05/27/2011, Business Type: Devices

                  Start date: 05/20/2011, End date:05/27/2011, Business Type: Drugs

 

Also, we must make sure that for new record the start and end dates do not fall within the above mentioned dates.

So the next acceptable record is:

 

Start date: 05/28/2011, end date: 05/31/2011,business type:Devices.

 

Note: I have a validation rule that checks that end date is always after start date.

 

I am having a road block to get this logic right in my trigger . Any help/pseudo code on this is highly appreciated.

 

Thanks,

Sales4ce

 

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

The basic premise for checking for overlapping dates is:

 

 

// assuming the candidate is in myRec

List<MyRec__c> overlaps=[select Start_Date__c, End_Date__c, Business_Type__c 
 from MyRec__c
  where 
  (
      (Start_Date__c >= :myRec.Start_Date__c AND StartDate__c <= :myRec.Stop_Date__c) OR
      (Stop_Date__c  >= :myRec.Start_Date__c AND StopDate__c  <= :myRec.Stop_Date__c) OR
      (Start_Date__c <= :myRec.Start_Date__c AND StopDate__c  >= :myRec.Stop_Date__c)
  )
  and Business_Type__c=:myRec.Business_Type__c];

if (!overlaps.isEmpty())
{
   // error here
}

 

 

Where you may hit some issues here is if your trigger needs to work in bulk mode.  I've yet to find a way to produce a date range query that encompasses multiple candidate records and returns the results in a usable format.  

 

All Answers

VPrakashVPrakash

Try this,

 

Use Before trigger on the record, query all the existing records and for each existing record check 

 

if ( newrecord.start date < existing record.start date + 8){

adderror here;

 

 

note: only con for this approach is you might hit the governor limit, if your custom object data is more than 50,000 

 

I hope this will help 

 

VPrakash

 

sales4cesales4ce

Thanks for your reply.

 

I do not have a fixed end date , like start date+8.

All i want it to make sure thet it does not fall with any of the existing dates which don't have a fixed start and end dates.

Also, i am unable to get it to work.

 

Sales4ce

bob_buzzardbob_buzzard

The basic premise for checking for overlapping dates is:

 

 

// assuming the candidate is in myRec

List<MyRec__c> overlaps=[select Start_Date__c, End_Date__c, Business_Type__c 
 from MyRec__c
  where 
  (
      (Start_Date__c >= :myRec.Start_Date__c AND StartDate__c <= :myRec.Stop_Date__c) OR
      (Stop_Date__c  >= :myRec.Start_Date__c AND StopDate__c  <= :myRec.Stop_Date__c) OR
      (Start_Date__c <= :myRec.Start_Date__c AND StopDate__c  >= :myRec.Stop_Date__c)
  )
  and Business_Type__c=:myRec.Business_Type__c];

if (!overlaps.isEmpty())
{
   // error here
}

 

 

Where you may hit some issues here is if your trigger needs to work in bulk mode.  I've yet to find a way to produce a date range query that encompasses multiple candidate records and returns the results in a usable format.  

 

This was selected as the best answer
sales4cesales4ce

Bob,

 

This is super cool! It worked like charm.

As you outlined,it works only for 1 record and not in bulk mode. Do you have any ideas how to go about when checking in bulk mode?

 

Thanks for your help as always.

 

Sales4ce

bob_buzzardbob_buzzard

Bulk mode can be a bit of a nightmare in this situation, depending on how varied the criteria is.

 

You'll need to split out all your records by business type and store in a map keyed by business type with the list of records as the value.  The problem then is how to construct a query that finds overlaps for any of the dates.  If you can live with ensuring that the records fall outside of any existing record, you can simply traverse the list and find the min start date, max end date and query against that.  However, if there are gaps in the existing records you won't be able to fill them in.

 

 

vabvab

Hi Bob,

You really suggested very excellent solution for date overlapping. I am in same situation and need write bulk mode code for this date overlapping.

 

If you have already worked on it and has some updated piece of code then can you please share it.

 

It will be really appriciated if you can put your thoughts and provide your helping hand.

bob_buzzardbob_buzzard

I'm afraid I don't have a solution to this.  I couldn't craft a query that would handle all of the dates as I needed to allow appointments to be inserted between others down to single minute granularity.  We ended up mandating that it couldn't be used in bulk mode.

 

Sorry.

Saurabh_GuptaSaurabh_Gupta

Hi

 

I am in same situation and need write bulk mode code for this date overlapping.

 

I have written a bulk code but it involves nested loops , here is the process i a following

 

1) in my trigger loop i am making a list of  Parent id

2) From the list of Parent id , i am Querying the all child reocrds

3) After this i have created a nested loop , one loop is for trigger records and other loop is on all child records and then i have a if condition to compare the overlapping of  Dates

Thus each trigger record ,I am  comparing with all child records .

 

In this apporach i want to avoid nested loops

Does anyone has a soultion for it ?

theDtheD
Bob...I am using some of your model and it works ok non bulk. I am trying to expand my test coverage but not sure how/if I can force a commit in the test class so that the second insert would create expected error condition..any thoughts...is this possible?