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
Niamh LNiamh L 

Prevent Double Booking Trigger

Hi All, 

I am trying to prevent people double booking a piece of equipment out. The user uses the custom object Booking Request to book out equipment for certain dates. 
For example Mike books equipment1 from the 22/06/2016 until 24/06/2016, then someone else wants to book the same piece of equipment from the 23/06/2016 until 25/06/2016 but should not be allowed as it is already booked.

I have created the following trigger which is working if someone enters the same start date, but how do i stop the record saving if they book in between the start and finish date? Any help greatly appreciated.

trigger DuplicateBookingTrigger on Booking_Request__c (before insert, before update) {
  for (Booking_Request__c br:Trigger.new)
  {
      List<Booking_Request__c> b=[select ID from Booking_Request__c where Equipment_Code__c=:br.Equipment_Code__c and Start_Date__c=: br.Start_Date__c and Finish_Date__c=: br.Finish_Date__c];
      if(b.size()>0)
      {
          br.adderror('The equipment selected is already booked for this date');
      }
   }
tonantetonante
Niahm,

 I am just wondering if you are using DateTime for the start and end date fields because if you are,  then this SOQL comparison may be looking at the eaxct match of the date and time right down to the current seconds. If even a second doesn't match then even if the person has the same day, month, and year they will still be able to get the equipment because the seocnds are different.  I wonder if this is the case. If so try using just date type instead of datetime type. See https://help.salesforce.com/apex/HTViewHelpDoc?id=formula_using_date_datetime.htm
GauravGargGauravGarg
Hi Niamh,
The easiest solution is to add one new custom field naming "Booking Available" of type "checkbox", and set the checkbox to false everytime the Equipment is booked. 
Then query the Equipment "booking available" field and check, if the Equipment is available then book the order else show the error message. 

Thanks,
Gaurav
tonantetonante
Yep that's a good one too. I think both might work but Gaurav has the easiest solution. I just avoided having an extra field to be used but he avoids actually having to do a SOQL query which is more efficient.
GauravGargGauravGarg
Thanks Niamh, if the solution works for you. Please mark it as best answer.

Thanks,
Gaurav
Niamh LNiamh L
Hi Tonante and GauravGarg thanks for the speedy responses.

Tonante - I am using the Date type for my start and end date.

GauravGarg - creating a Booking Available" field of type "checkbox" would mean if i book the peice of equipement today for July, someone else couldn't place a booking today for the same piece of equipment for August because the checkbox would be false for the July booking. So it woun't allow future bookings, if that makes sense.
tonantetonante
This Might be pseudo thought but somehow you need to save the dates entered by the previous users per equipment record. Then when another enters the start and end dates for the same equipment code the the new dates entered should be compared with the start and end dates of the previous user which is stored in the equipment code record base on the example you just gave as the condition logic. Hope that helps. So if new start date is greater that current start date but less than current end date then prohibit the new user from acquiring the equipment.
GauravGargGauravGarg
Hi Niamh,

We have a solution here, we will surely be saving status field on Booking request object. The status field will display that the Booking request is in working or completed state . 
So lets say, We have two equipments A and B. Currently both are in available state i.e. Booking Available field is set to true. 
Now, we received one new Booking Request for equipment A. We check the Booking_Available field and compare equipment.End_Date__c > = Booking_Request.End_Date__c. 
If the condition if fulfill, we will book the Equipment and the set the Booking_Available field as False. 

Now, the booking_request record is in working state and will be completed after the End_Date i.e. the Equipment will be again available for booking. I.e. the booking_request record will get updated to "completed" state.

Here, in AfterUpdate call of Booking_request, we will query Equipment record and set the Booking_Available field to True. 

So, if we recieve any other booking_request on Equipment 'A'. We can book it. 

Please contact me via email: gauravgarg.nmims@gmail or skype: gaurav62990, if you still have some confusio in it. 

Thanks,
Gaurav
GauravGargGauravGarg
Hi Niamh,

If you got your answer, please select an best answer to close this question or please explain the issue you are still facing with this. 
Niamh LNiamh L
Hi Gaurav, 

This still would not work for someone wanting to make future bookings. It will only work in real time. Any other suggestions?

Thanks for your help, Niamh

 
GauravGargGauravGarg
Hi Niamh,

Can you please elaborate the requirement on future bookings.

Thanks,
Gaurav
Niamh LNiamh L
Hi Gaurav, 

Example, Today I place a booking for equipment A for September 1st to 8th.
Tomorrow another person should be allowed book equipment A for 10th Aug to 15th Aug
But if another person wants to book the equipemt A from 5th Sept to 7th Sept it should stop them saving the booking as it is booked from 1st Sept to 8th
Your line of "working state and will be completed after the End_Date i.e. the Equipment will be again available for booking. I.e. the booking_request record will get updated to "completed" state." is true but would mean no bookings can be made tomorrow for Aug as the today's booking has an end date of Sept.
If that makes sense.

Thanks,
Niamh
 
GauravGargGauravGarg
thanks Niamh for the explaination, I will look for the solution and update you soon on this. 

Thanks,
Gaurav
Email: gauravgarg.nmims@gmail.com
Skype: gaurav62990
Kasper JensenKasper Jensen
Hi Niamh,

Did you manage to find a solution that worked?

Kind regards,
Kasper
Niamh LNiamh L
Hi Kasper, 

Unfortunately not. Ended up using a calendar for users to view when items were booked.

Kind Regards,
Niamh
GauravGargGauravGarg
Hi Niamh,

Could you please provide the solution you get it done, so that I could assist you further on this. 

Thanks,
Gaurav
Skype: gaurav62990
Kasper JensenKasper Jensen
Hi Niamh,

I ended up looking at your example and added some more "conflicts". So instead of only checking start date I also have one checking for end date and another for dates in between. Maybe you can use it (if you still are interested in it) 
trigger BookCar on Rental_Order__c (before insert, before update) {

    for(Rental_Order__c booking : trigger.new){	
     
        List<Rental_Order__c> conflictsStartDate = 
            [SELECT id FROM Rental_Order__c WHERE 
             (Order_Status__c = 'Booked' OR Order_Status__c = 'Rented') AND Car__c = :booking.Car__c AND 
             ((Rent_Start_Date__c <= :booking.Rent_Start_Date__c AND Rent_End_Date__c >= :booking.Rent_Start_Date__c )) AND id != :booking.id];
        
        List<Rental_Order__c> conflictsEndDate = 
            [SELECT id FROM Rental_Order__c WHERE 
             (Order_Status__c = 'Booked' OR Order_Status__c = 'Rented') AND Car__c = :booking.Car__c AND 
             (Rent_Start_Date__c <= :booking.Rent_End_Date__c AND Rent_End_Date__c >= :booking.Rent_End_Date__c ) AND id != :booking.id];
        
        List<Rental_Order__c> conflicts = 
            [SELECT id FROM Rental_Order__c WHERE 
             (Order_Status__c = 'Booked' OR Order_Status__c = 'Rented') AND Car__c = :booking.Car__c AND 
             ((Rent_Start_Date__c >= :booking.Rent_Start_Date__c AND Rent_End_Date__c <= :booking.Rent_End_Date__c )) AND id != :booking.id];
        
        if(!conflictsStartDate.isEmpty()){
            
            booking.addError('The car is not available on that start date');
        }
        if(!conflictsEndDate.isEmpty()){
            
            booking.addError('The car is not available on that end date');
        }
        
        System.debug('conflicts: ' + conflicts);
        if(!conflicts.isEmpty()){
            
            booking.addError('The car is booked between the start and the end date');
        }
    }
    
}

Kind regards,
Kasper
 
JOANNE CONLUJOANNE CONLU
Hello Kasper - I'm a newbie; an Admin not a Developer. Could this work for our Resources not being able to get double booked? We host mutiple events and assign multiple resources per event. For example, we only have 3 projectors, we don't want to book 4 projectors from StartDate to EndDate. Could the above trigger work? Thank you!