+ Start a Discussion
laytro1978laytro1978 

Validation Help Please

Hi,

 

I have a room object, and related booking object.

 

The booking object has a start date and end date.

 

I'd like to write a piece of validation which would stop someone from taking a room which already have a booking on the same date.

 

Any pointers would be welcome, for various reason I can't use the standard resources on activities.

 

Thanks

 

Ross

kritinkritin

create a unique key formula field on Booking object with following formula: booking__c.roomName + ':' + booking_c.StartData + ':' + booking__c.EndDate

 

 

or you can write a triiger on this object and add Custom Error mesaage on before Insert.

gotherthanthougotherthanthou

 


kritin wrote:

create a unique key formula field on Booking object with following formula: booking__c.roomName + ':' + booking_c.StartData + ':' + booking__c.EndDate

 

 

or you can write a triiger on this object and add Custom Error mesaage on before Insert.


 

The unique key thing is not going to work because it won't handle overlap.

 

You need something a bit more complex.  Consider booking1 and booking2 dates:

 

startdate1, enddate1, startdate2, enddate2

 

now consider 2 booleans:

 

OneTotallyAfterTwo - true if startdate1 > enddate2

OneTotallyBeforeTwo - true if enddate1 < startdate2

 

if either one is not true, there is overlap.

 

According to deMorgan's law,  [Not (A or B)] is equivalent to [Not A and Not B]

 

So you write a trigger for before insert and before update that takes the start and end of of the booking being saved, and queries the booking table for:

 

SELECT count(*) FROM Booking__c WHERE StartTime__c <  curRecordEnd
         AND EndTime__c >  curRecordStart  AND Room__c = curRecordRoom

 

(Note that that's air code and you'll have to get the syntax right)

 

If you get a result other than 0 you've got overlap.

 

laytro1978laytro1978

Thanks for the reply, yes I thought that there would be a problem with the overlap.

 

I have not had any experience writing APEX Triggers.

 

The objects are booking__c and room__c , fields are start_date__c and end_date__c.

 

Can anyone provide some sample code to get me started.

 

Thanks

 

Ross