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
si risi ri 

Salesforce trigger addError method to compare old and new fields

I am creating events on Account object. In the event object I have two date/time fields Start Time and End Time.
My question is how I can validate new Start Time or End Time of an event exists with any  existing event related to an Account?

The only way I can think to validate if the sessions overlap is to use a trigger with the addError() method.
It means new event should not be created on an Account if there is an existing event relays with same date/time.
how can I compare the times of each event for an Account?

here is my trigger I am failing to achieve my requirement. Any one help me appretiated.
trigger sessionValidation on Event (before insert,before update) {
    Set<ID> accID = new set<ID>();

    for(Event eve : trigger.new)
    {
      accID.add(eve.AccountId);
    }
  
    List<Event> ExistingEventList = [Select StartDateTime , EndDateTime from Event where AccountID in : accID];
    system.debug('ExistingEventList :'+ExistingEventList);
    
    for(Event sNew : trigger.new)
    {
        
        for(Event sOld : ExistingEventList)
        {
                            if((sNew.StartDateTime <= sOld.StartDateTime && sNew.EndDateTime  >=  sOld.StartDateTime && sNew.EndDateTime <= sOld.EndDateTime) || 
                   (sNew.StartDateTime <= sOld.StartDateTime && sNew.EndDateTime  >= sOld.StartDateTime && sNew.EndDateTime >= sOld.EndDateTime) ||
                   )
            {
                sNew.addError('Overlap Session');
            }
        }
    }
}
 
Andrew GAndrew G
try checking your tests for the times and where they are landing.
Something like:
 
if(
  //is the new start in another event time window
  (sNew.StartDateTime >= sOld.StartDateTime && sNew.StartDateTime <= sOld.EndDateTime) 
  ||
  //OR is the new end in another event time window
  (sNew.EndDateTime >= sOld.StartDateTime && sNew.EndDateTime <= sOld.EndDateTime) 
  ||
  //OR do the new times wrap around another event time window
  (sNew.StartDateTime <= sOld.StartDateTime && sNew.EndDateTime  >= sOld.EndDateTime)
  )
  {
    sNew.addError('Overlap Session');
  }

Regards
Andrew​​​​​​​
Andrew GAndrew G
After reading the logic you were testing, I am just adding some extra detail to help explain my choice of tests,

Test 1 - 
If we have a pre-existing event between 9 and 11, if my start time is 9:15, it does not matter when I finish, as i'm already overlapping.  Doesn't matter if I end at 10:15 or 11:15, there is an overlap.
Test 2 -
If we have a pre-existing event between 9 and 11, if my end time is 10:15, it does not matter when I start, as i'm already overlapping.  Doesn't matter if I start at  8:15 or 9:15, there is an overlap.
Test 3 - 
If we have a pre-existing event between 10 and 11, and my new event is 9 to 12, then there is an overlap.  

There is no need to test if both the new start and end are within a pre-existing event, as if either one or the other is inside the previous event window, the first 2 x tests would detect the overlap.

You may wish to tweak the <= & >= signs if you want to allow back to back events, e.g. previous was 9:00 to 10:00 and you want the next to be 10:00 to 11:00, then adjust as

//is the new start in another event time window
(sNew.StartDateTime >= sOld.StartDateTime && sNew.StartDateTime < sOld.EndDateTime)
||
//OR is the new end in another event time window
(sNew.EndDateTime > sOld.StartDateTime && sNew.EndDateTime <= sOld.EndDateTime)

Regards
Andrew
si risi ri

Hi Andrew G,
       I agree with your logic senarios,but my question is how compare the existing all the child  events realetd to Account and throw validation error before when a new event is created with the same start or end date ?

In my trigger it is checking only the event which I am trying to update, it is not checking all the child events af an Account.
 
Andrew GAndrew G
Hi

So are you saying that you have an Account with existing Events that were created before the Trigger was created.  And you want to check those Events?  As a data cleanup process?

Since it is data cleanup, consider if using dataloader to extract, cleanse and reload would work.

If we consider a batchable process, it would be quite heavy as in (psuedo code)
get all Accounts ids.(big SOQL query result set)
get all Events for all Account Ids group by Account Id  (bigger SOQL query result set)
create a Map<AccountId, List<Events>>    (big loop to go through each event record and put into the Map at the correct location)
//now we have the data
Loop the Map
get each list of events, 
  loop list of events
     compare each event to every other event,  if overlap, tag the event
  get next event

At the end, you would have events with a checkbox saying there is an overlap.  You could then run a report.  
Due to the heavy processing, i would not keep this as an ongoing process.  Once the data is clean, the trigger should take care of anything new.

Other alternative, create an action button on the Account record that invokes code to loop all events.  
Given that it is in the UI, you could do a throw error, or mark the events with a flag,or both.


Regards
Andrew