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
laytro1978laytro1978 

Availability Checker

I have custom object with a

 

 

  1. name (unique)
  2. start date
  3. end date
I want create a VF / Apex availability checker to see where there are no records with the same name and date.
Any help much appreciated.
Thanks
R

 

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

No, this will not handle overlaps.  It will only find identical record.

 

You'll need a more complex query to handle those situations. I think the following should handle the date side:

 

 

where  (Start_Date__c >= :startDate  
        and 
        Start_Date__c <= :endDate
       ) 
       or
      (End_Date__c >= :startDate
        and
        End_Date__c <= :endDate)

 

 

All Answers

bob_buzzardbob_buzzard

Checking there are no others should be straightforward.  In the example below the object is My_Custom_Object__c - replace this as needed.  Note that I've retrieved the record rather than just a straightforward count, in case you want to add details to the error message

 

This will find objects that have the name 'Test Name', and a start/end date of today.

 

 

String theName='Test Name';
Date theStartDate=System.today();
Date theEndDate=System.today();

List<My_Custom_Object__c> existingObjs=[select id, Name, Start_Date__c, End_Date__c from My_Custom_Object where name=:theName and Start_Date_c=:theStartDate and End_Date__c=:theEndDate];

if (!existingObjs.isEmpty())
{
   // do some error handling here, existing items are available in existingObjs
}

 

 

The Visualforce side of things really depends on your use case.  Presumably you are capturing the name and start/end date into your controller?  That being the case you could simply have a check availability command button that executes the above code.

 

 
Laytro80Laytro80

Thanks Bob,

 

I have some time set aside tomorrow to have a go at the code.  Will this handle overlaps, example;

 

 

  • So if the query start date is 1 March and the end date is the 5 March
  • I have a record with a start date of the 2 March and an end date of 4 March
  • Will the query find the record?
  • Also if the start data is 2 March but the end date is the 6 March
Thanks
Ross

 
bob_buzzardbob_buzzard

No, this will not handle overlaps.  It will only find identical record.

 

You'll need a more complex query to handle those situations. I think the following should handle the date side:

 

 

where  (Start_Date__c >= :startDate  
        and 
        Start_Date__c <= :endDate
       ) 
       or
      (End_Date__c >= :startDate
        and
        End_Date__c <= :endDate)

 

 

This was selected as the best answer
laytro1978laytro1978

Hi Bob,

 

Thanks again for your help.   I have been able to buy myself a lot more time by writing a report that shows booked days  However I am still very keen to get this solution built and learn more about APEX.

 

I am working on a similar problem which I have gotten help for but I still can't get it to work.  It's more or less the same problem but simpler.

 

I just want to return bookings that have a duration for a dynamic number of days.  I was hoping when I cracked this I would be able to apply what I have learnt to the more complicated date query but I am struggling.

 

Code below, any pointers you can provide would be much appreciated. 

 

Thanks again,

 

Ross

 

 

 

Here is the VF page

 

 

<apex:page controller="Resmanager">
    <apex:sectionHeader title="Availability Search"/> 
        <apex:form >
             <apex:pageBlock mode="edit" id="block"> 
                <apex:pageBlockButtons >
                </apex:pageBlockButtons>
                <apex:pageBlockSection >        
                    <apex:pageBlockSectionItem >
                                <apex:outputLabel for="searchText">Amount of Nights</apex:outputLabel>  
                                <input type="number" name="{!minNumNights}"/>  
                    </apex:pageBlockSectionItem>
                </apex:pageBlockSection>
                </apex:pageBlock>    
              <apex:pageBlock title="Reservations">
                  <apex:pageBlockSection >
                        <apex:pageBlockTable value="{!Reservations}" var="res">
                            <apex:column value="{!res.Start_Date__c}"  />
                            <apex:column value="{!res.End_Date__c}"  />
                            <apex:column value="{!res.Nights__c}"  />
                        </apex:pageBlockTable>
                   </apex:pageBlockSection>               
               </apex:pageBlock>
     </apex:form>
</apex:page>

 

 

And the APEX code

 

 

public class Resmanager {

    Integer minNumNights {get; set;}
    Integer mnnDefault = 4;

    public Integer getMinNumNights() {
       if(minNumNights == null)
          minNumNights = mnnDefault;
       return minNumNights;
    }   
        
    public List<Reservation__c> getReservations(){
        List<Reservation__c> listBd=[select r.id, r.Start_Date__c, R.End_Date__c, nights__c from Reservation__c r where nights__c = :minNumNights];
        return listBd;   
    }
   
}

 

 

T

 
laytro1978laytro1978

Thanks Bob, all working now.  The overlapping date code you gave me works great!
Laytro80Laytro80

Hi Bob,

 

Sorry to ask but your code really helped me last time.  And it's an expansion on the same problem.  The code you provided works great for date searches within the criteria.  Problem I now have is I need to return records that fall completely outside the exact search but have overlap.

 

So for example if my search is look for records that

 

start on the 3 March and end on the 8 March

 

I need to bring back records that start on the 1 March and end on the 10 March as the overlap the search above.

 

More information and all of my code is on the link is to the new post -> http://boards.developerforce.com/t5/Apex-Code-Development/Date-Problem/td-p/259943

 

Thanks again, and no problems if you can't help thanks for everything you have done.