+ Start a Discussion
Sylvio AvillaSylvio Avilla 

Prevent DateTime Overlap Trigger

Hello Everyone,

I would like to create a simple trigger and prevent the user to add a new record that will overlap an existing record. 
  • The idea is that the same person couldn't do 2 things at the same time!
I read a lot and try to find an answer for my problem before star a discussion, but could't find it. 
This is the closest I could get to solve it:
// referene link https://developer.salesforce.com/forums/?id=906F00000008zTcIAI

// 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
}
The problem is that when try to use it, receive the following error:

"Invalid bind expression type of Schema.SObjectField for column of type Datetime.

Can anyone tell me whats wrong?

Thanks
bob_buzzardbob_buzzard
This usually means that you are binding an sobject rather than a record instance on the right hand side of the comparison operators, but that doesn't look to be the case here.  Can you post the code that creates myRec?
Sylvio AvillaSylvio Avilla
Hello bob_buzzard, thanks for the reply.

So, myRec is a custom object, witch contains the Start_Date__c, End_Date__c, Business_Type__c custom fields. 
I just click "new" to create a new myRec.

Thanks again
bob_buzzardbob_buzzard
Ah - so you don't create an instance of myRec before executing the query?  That's the issue. You need to execute this query for the records in the trigger rather than the sobject name itself. Otherwise you are comparing against a schema item rather than a record containing fields.
Sylvio AvillaSylvio Avilla
Hello again Bob

Maybe I'm being redundant, but here is the trigger with my real custom objects ( the other code was just an example).
Keep receiving the same error 

"Invalid bind expression type of Schema.SObjectField for column of type Datetime."
trigger Overlap on Viagem__c  (before insert, before update) {


List<Viagem__c> overlaps=[select Chegada_na_Base__c, Sa_da_da_Base__c, Veiculo__c 
 from Viagem__c
  where 
  (
      (Sa_da_da_Base__c >= :Viagem__c.Sa_da_da_Base__c AND Sa_da_da_Base__c <= :Viagem__c.Chegada_na_Base__c) OR
      (Chegada_na_Base__c  >= :Viagem__c.Sa_da_da_Base__c AND StopDate__c  <= :Viagem__c.Chegada_na_Base__c) OR
      (Sa_da_da_Base__c <= :Viagem__c.Sa_da_da_Base__c AND StopDate__c  >= :Viagem__c.Chegada_na_Base__c)
  )
  and Veiculo__c=:Viagem__c.Veiculo__c];

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


}

Sorry for the elementay question, but I'm not very good at it

Thanks again
bob_buzzardbob_buzzard
The issue here is that your query is binding "Viagem__c.Chegada_na_Base__c", but you don't have a variable of "Viagem__c". So this tries to bind the field definition from the sobject type "Viagem__c" which obviously won't work.

You need to check against the records in the trigger, e.g. 
 
(Sa_da_da_Base__c >= :Viagem__c.Sa_da_da_Base__c AND Sa_da_da_Base__c <= :trigger.new[0].Chegada_na_Base__c) OR

This won't scale beyond 100 records though, as you have to execute the query against a single record in order to validate the date range, which is fine if you are only creating records via the UI.
Sylvio AvillaSylvio Avilla
Thanks again.

I tried to simplify the code, so I could see if it would work. 
 
trigger Overlap on Viagem__c  (before insert, before update) {

List<Viagem__c> overlaps=[select Sa_da_da_Base__c
 from Viagem__c
  where 
     
      Sa_da_da_Base__c = :trigger.new[0].Sa_da_da_Base__c
  ];
    
if (!overlaps.isEmpty())
{
   trigger.new[0].Sa_da_da_Base__c.addError('See error  message will appear when u insert or update an account');
}


}


The thing is that I've tried to create a new record that would meet the criteria (
Sa_da_da_Base__c = :trigger.new[0].Sa_da_da_Base__c) and generate the error, informing that is duplicated, but nothing happens!
 
Sylvio AvillaSylvio Avilla
Hello ,

Here I am again. 

Can you help me once again? I tried do simplify the code with just one statement 
Data_Hora_Chegada_Base__c =:trigger.new[0].Data_Hora_Chegada_Base__c
 
trigger Overlap on Viagem__c (after insert, after update) 
{ 

List<Viagem__c> overlaps=[select ,Data_Hora_Chegada_Base__c, Data_Hora_Saida_Base__c, Veiculo__c from Viagem__c where (Data_Hora_Chegada_Base__c =:trigger.new[0].Data_Hora_Chegada_Base__c )]; 

if (!overlaps.isEmpty()) { } 
else{ trigger.new[0].Hora_Saida_Base__c.addError('See error message will appear when u insert or update an account'); } 

}



The problem is that its always returning something and displaying the error, even with just one record!

​Thanks again
 
bob_buzzardbob_buzzard
Your logic doesn't look right there to me - you don't do anything if there are overlaps, thanks to :
 
if (!overlaps.isEmpty()) { }

so you will only return the error if there are no overlaps, via the else clause:
 
else{ trigger.new[0].Hora_Saida_Base__c.addError('See error message will appear when u insert or update an account'); }



 
Sylvio AvillaSylvio Avilla
Hello Bob, it's working! 

Just have one last issue. When I update the record, it returns me "Overlap". There is a way to avoid it? 

​Thanks!