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
Laytro80Laytro80 

Is it possible to search for overlapping dates

I have a custom object which has a start and an end date. I want to write some code which will search to see if any record occurs within those dates - easy.

 

But I also want some code that check to show any overlapping records. So for example if I search to see if records exists between the 1st March and the 30 March I want it to return all of the records below because they either fall within the search or overlap.

 

a. Record -  with a start date of 2 March and and end date of the 29 March

b. Record - with a start date of the 1st Feb and an end date of the 28 March

c. Record - with a start date of the 2nd March and an end date of the 28 April

d. Record - with a start date of the 1st Jan and an end date of the 30 April

 

Be great to get some help on this one as I am a bit stuck

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
Here's my updated version:
<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <!-- Here's where {!Search} should be. -->
                    <apex:commandButton action="{!search}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <!--
                        We are using a standard controller, so reference that controller instead.
                        We do this to make the code a bit more readable, as future coders won't have
                        to look at the controller to guess what the variable "room" is. Room is now
                        a private variable, too.
                    -->
                    <apex:inputField value="{!Room_Allocation__c.Search_Start_Date__c}"/>
                    <apex:inputField value="{!Room_Allocation__c.Search_End_Date__c}"/> 
                </apex:pageBlockSection>
            </apex:pageBlock>  
            
            <apex:pageBlock title="Available Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!availablerooms}" var="avail">
                        <apex:column value="{!avail.Lodge__c}"  />
                        <apex:column value="{!avail.Room_No__c}"  />
                        <apex:column value="{!avail.Name}"  />                        
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
            <apex:pageBlock title="Booked Rooms">
                <apex:pageBlockSection >
                    <!--
                        By removing the global "room" variable, we avoid ambiguity between
                        this local variable and the global variable. This is to help
                        future developers that might look at the code.
                    -->
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Room__r.Lodge__c}"  />
                        <apex:column value="{!room.Room__r.Room_No__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Name}"  />                        
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />   
                        <apex:column value="{!room.Reservation__r.Reservation_Contact__c }"  />                         
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
    </apex:form>
</apex:page>

 

public class Roommanager {
    public void search() {
        // Provide default values in the event that no dates are selected. This is optional.
        if(room.Search_Start_Date__c==null)
            room.Search_Start_Date__c=System.today().toStartOfMonth();
        if(room.Search_End_Date__c==null)
            room.Search_Start_Date__c=System.today().toEndOfMonth();
        
        // Swap the dates if start is before end. Optional nice logic.
        if(room.Search_Start_Date__c>room.Search_End_Date__c) {
            Date tempDate = room.Search_Start_Date__c;
            room.Search_Start_Date__c = room.Search_End_Date__c;
            room.Search_End_Date__c = tempDate;
        }
        
        // Remove previous search results, if any.
        rooms.clear();
        availableRooms.clear();
        
        // Find all the room allocations, add them to the list.
        rooms.addAll(
            [SELECT
                Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c, Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c
            FROM
                Room_Allocation__c r     
            WHERE (
                (Start_Date__c >= :room.Search_Start_Date__c  AND Start_Date__c <= :room.Search_End_Date__c) OR
                (End_Date__c  >= :room.Search_Start_Date__c  AND End_Date__c  <= :room.Search_End_Date__c) OR
                (Start_Date__c <= :room.Search_Start_Date__c  AND End_Date__c  >= :room.Search_End_Date__c))  
            ORDER BY
                Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC]);
        
        // Build a list of allocated rooms.
        Set<Id> allocatedRooms = new Set<Id>();
        for(Room_Allocation__c roomAlloc:rooms)
            allocatedRooms.add(roomAlloc.Room__c);
        
        // Add the rooms that are still available.
        // I like making queries indented for legibility.
        availableRooms.addAll(
            [SELECT
                Id, Name, Room_No__c, Lodge__c,
                (SELECT
                    Id, Name, Start_Date__c, End_Date__c, Room__c
                FROM
                    Room_Allocations__r)
            FROM
                Room__c
            WHERE
                Id NOT IN :allocatedRooms
            ORDER BY
                Lodge__c ASC, Room_No__c ASC]
        );
    }

    private Room_Allocation__c room;
    
    public List<Room_Allocation__c> rooms { get; set; }
    public List<Room__c> availableRooms { get; set; }
    
    // COnstructor. We initialize our memory variables here.
    public Roommanager(ApexPages.StandardController controller) {
        room=(Room_Allocation__c)controller.getRecord();
        rooms=new List<Room_Allocation__c>();
        availableRooms = new List<Room__c>();
        // Do a default search for our user.
        search();
    }
}

I added some optional features just as a suggestion, but note that the main problem that you had was that you were calling a null function {!room} instead of the {!search} function (see the VF page). Other than that, most of your code was sound. I assumed the queries were already working more or less okay and left them alone.

Note the other piece, where I referenced "room" by the controller instead of by a member variable in the VF page. Since they both point to the same place, you reduce (developer) confusion by using the standard controller when possible. There's no other real benefit here, but legible code is always a plus.
Finally, as you can see here, we consolidated the search logic into a single function, which provides the greatest flexibility.
Let me know how it works out.
Edit: Changed a private modifier to public.

All Answers

bob_buzzardbob_buzzard

In order to trap these cases, I think you'll need to search for the record start date falling inside the criteria (i.e. >= criteria start and <= criteria end) and likewise the end date.

 

 

Something like the following SOQL query:

 

 

Date startDate=Date.newInstance(2011, 3, 1);
Date endDate=Date.newInstance(2011, 3, 30);
                    
List<MyObject__c> results=[select id, StartDate__c, EndDate__c, Detail__c 
                           where 
                             (StartDate__c>=:startDate AND
                              StartDate__c<=:endDate)
                           or
                             (EndDate__c>=:startDate AND
                              EndDate__c<=:endDate)
                          ];

 

Safe harbor - I haven't compiled this, so my date instantiators may be incorrect, but hopefully you get the idea.

 

sfdcfoxsfdcfox

Given StartDateSearch, StopDateSearch, StartDate__c, and StopDate__C, the following are overlapping records:

 

 

WHERE (
(StartDate__c >= :StartDateSearch AND StartDate__c <= :StopDateSearch) OR
(StopDate__c  >= :StartDateSearch AND StopDate__c  <= :StopDateSearch) OR
(StartDate__c <= :StartDateSearch AND StopDate__c  >= :StopDateSearch))

Basically, if either the start or stop dates fall between the search ranges, or if the search range completely overlaps (the third condition), it is an overlapping date. Condition A in your example matches the first two filters, Condition B matches the second filter, Condition C matches the first filter, and Condition D matches the last filter.

 

This assumes that StartDateSearch and StopDateSearch are Date values, StartDate__c and StopDate__c are date type fields, and that you're running this in a SOQL query or some sort (this would be the WHERE clause).

 

Edit: Fixed silly mistake.

bob_buzzardbob_buzzard

Actually, I think you need a combination of my reply and sfdcfox reply.  I missed the scenario where the record falls completly inside the criteria (i.e. starts before the start criteria and ends after the end criteria).

 

However, the middle condition of sfdcfox post:

 

 

(StopDate__c  >= :StopDateSearch  AND StopDate__c  <= :StopDateSearch) OR

looks like it should be revised to:

 

 

(StopDate__c  >= :StartDateSearch  AND StopDate__c  <= :StopDateSearch) OR

 

 Otherwise it won't catch the full overlap of the "end" time of your object with the criteria.

 

 

 

 

 

sfdcfoxsfdcfox

Nice catch, Bob. I'll update that rather obvious typo in my original post.

Laytro80Laytro80

Big thanks for this, all of my code is below.

 

I think this has worked, although I have an eagle eye tester who will be on this first thing on Monday morning.

 

 

Public class Roommanager {

    public PageReference room() {
        return null;
    }
    
    public List<Room_Allocation__c> listBd; 

    public Room_Allocation__c room {get;set;}
    public Roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
            
    public List<Room_Allocation__c> getRooms(){

            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c, Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c from Room_Allocation__c r      
             WHERE (
                (Start_Date__c >= :room.Search_Start_Date__c  AND Start_Date__c <= :room.Search_End_Date__c) OR
                (End_Date__c  >= :room.Search_Start_Date__c  AND End_Date__c  <= :room.Search_End_Date__c) OR
                (Start_Date__c <= :room.Search_Start_Date__c  AND End_Date__c  >= :room.Search_End_Date__c))   
                ORDER BY Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC];    
             return listBd;
 }

public List<Room__c> getAvailableRooms()
{
if(listBd == null || listBd.size() == 0)
getRooms();

List<Id> allocatedRooms = new List<Id>();

for (Room_Allocation__c roomAlloc : listBd)
allocatedRooms.add(roomAlloc.Room__c);

return ([Select Id, Name, Room_No__c, Lodge__c,(Select Id, Name, Start_Date__c, End_Date__c, Room__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms ORDER BY Lodge__c ASC, Room_No__c ASC]);

} 
}

 

 

Laytro80Laytro80

Thanks for this I am on the home stretch.  I have one issue left, when you search for the first time everything works perfectly - thanks :smileyvery-happy:

 

Now your a looking a page with the results.  You might want to change the search and re-run and here is the problem. When you change the dates press search, straight after a previous search, the code re-runs but it does not use the new dates it uses the old dates. The new dates are displayed so it is misleading but the results are incorrect. It's quickly fixed by pressing the search button again and the code runs properly. So right now if I want to run a second search straight after the first I have to:

 

 

  1. Amend the dates
  2. Hit search
  3. Hit search again
Is there an easy solution here?
Thanks
Ross

 

sfdcfoxsfdcfox

Your getRooms and getAvailableRooms methods are written incorrectly, which causes them to behave strangely. What's happening here is that getAvailableRooms may trail one step behind getRooms, because getAvailableRooms conditionally calls getRooms, while getRooms always performs a query, so it may leap step step ahead of getAvailableRooms' perception of what the list looks like. Try it out on paper and you'll see what I mean. The problem happens because calling getter methods are non-deterministic, so you can't depend on their order being called one particular way each time. Your logic works only when getRooms is called before getAvailableRooms.

 

Pages in visualforce proceed through three phases. The setter phase, the action phase, and the getter phase. The fix here is to not modify any variables during the "getter phase" of the page, because it's too late in the page's life cycle to be modifying variables. Instead, move your queries to the "action" phase, where they belong.

 

 

Public class Roommanager {

    public PageReference room() {
        return null;
    }

    public Room_Allocation__c room {get;set;}

    public Roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }

    public void search() {
        List<Room_Allocation__c> rooms =
         [SELECT
            Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c,
            Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c
          FROM
            Room_Allocation__c r
          WHERE (
            (Start_Date__c >= :room.Search_Start_Date__c  AND Start_Date__c <= :room.Search_End_Date__c) OR
            (End_Date__c  >= :room.Search_Start_Date__c  AND End_Date__c  <= :room.Search_End_Date__c) OR
            (Start_Date__c <= :room.Search_Start_Date__c  AND End_Date__c  >= :room.Search_End_Date__c))
          ORDER BY
            Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC];
        Set<Id> allocatedRooms = new Set<Id>();
        for(Room_Allocation__c roomAlloc:rooms)
          allocatedRooms.add(roomAlloc.Room__c);
        availableRooms =
          [SELECT
              Id, Name, Room_No__c, Lodge__c,
              (SELECT
                 Id, Name, Start_Date__c, End_Date__c, Room__c
              FROM
                 Room_Allocations__r)
           FROM
             Room__c
           WHERE
             Id NOT IN :allocatedRooms
           ORDER BY
             Lodge__c ASC, Room_No__c ASC];
    }

    public List<Room_Allocation__c> rooms { get; set; }
    public List<Room__c> availableRooms { get; set; }
}

Now, all you have to do is use {!search} on your commandButton or commandLink, and your woes should just melt away. You should never write getters that modify a variable or depend on the actions of any other getter, and you should never write a setter that determines its behavior based on a variable that any other setter modifies, or modifies a value set by any other setter. Save those types of actions for action methods only.

 

Laytro80Laytro80

Thanks for this it's just amazing help, really appreciate it and as you tell I am still learning.

 

I have used the new code in the controller and made the VF Page search button point to {!search}.

 

But when I put in a date range the booked rooms list remains empty when I know there are bookings.

 

Not sure what I am missing.

Laytro80Laytro80

Thanks for this it's just amazing help, really appreciate it and as you tell I am still learning.

 

I have used the new code in the controller and made the VF Page search button point to {!search}.

 

But when I put in a date range the booked rooms list remains empty when I know there are bookings.

 

Not sure what I am missing.

Laytro80Laytro80

Thanks for your help again Bob.

 

SFDC Fox has provided some optimised code which is not bringing back any booked rooms.

 

I think the code is a lot better than what I cobbled together and I suspect it's something I have done wrong although I can't see what.

 

I have replied to SFDC Fox but I think he might not be around I was wondering if you had any thoughts. 

 

Was hoping to get this knocked off before the weekend.

 

No worries if not thanks again

 

R

sfdcfoxsfdcfox

I can go ahead and take a look at this for you. If you wouldn't mind, could you post the page and its controller as you have it currently? I've got a few hours before class starts, so I can take a look at this for you and see if we can figure out what's going on. I have a feeling it's probably something simple, but since we don't have a sample of your Visualforce page, I can't tell exactly where it's gone wrong.

Laytro80Laytro80

That is so kind of you, thanks for this.

 

These are my old pieces of code they work but with the problem we are trying to fix.

 

Your code is very clean so would be keen to use.

 

Thanks again

 

The VF page I currently have

 

 

<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <apex:commandButton action="{!room}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <apex:inputField value="{!room.Search_Start_Date__c}"/>
                    <apex:inputField value="{!room.Search_End_Date__c}"/> 
                </apex:pageBlockSection>
            </apex:pageBlock>  
            
            <apex:pageBlock title="Available Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!availablerooms}" var="avail">
                        <apex:column value="{!avail.Lodge__c}"  />
                        <apex:column value="{!avail.Room_No__c}"  />
                        <apex:column value="{!avail.Name}"  />                        
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
            <apex:pageBlock title="Booked Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Room__r.Lodge__c}"  />
                        <apex:column value="{!room.Room__r.Room_No__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Name}"  />                        
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />   
                        <apex:column value="{!room.Reservation__r.Reservation_Contact__c }"  />                         
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
    </apex:form>
</apex:page>

 

The Apex Controller I currently have

 

 

Public class Roommanager {
    public PageReference room() {
        return null;
    }
   
    public List<Room_Allocation__c> listBd;

    public Room_Allocation__c room {get;set;}
    public Roommanager(ApexPages.StandardController controller) {
           room=(Room_Allocation__c)controller.getRecord();
             }
           
    public List<Room_Allocation__c> getRooms(){

            listBd=[select Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c, Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c from Room_Allocation__c r     
             WHERE (
                (Start_Date__c >= :room.Search_Start_Date__c  AND Start_Date__c <= :room.Search_End_Date__c) OR
                (End_Date__c  >= :room.Search_Start_Date__c  AND End_Date__c  <= :room.Search_End_Date__c) OR
                (Start_Date__c <= :room.Search_Start_Date__c  AND End_Date__c  >= :room.Search_End_Date__c))  
                ORDER BY Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC];   
             return listBd;
 }

public List<Room__c> getAvailableRooms()
{
if(listBd == null || listBd.size() == 0)
getRooms();

List<Id> allocatedRooms = new List<Id>();

for (Room_Allocation__c roomAlloc : listBd)
allocatedRooms.add(roomAlloc.Room__c);

return ([Select Id, Name, Room_No__c, Lodge__c,(Select Id, Name, Start_Date__c, End_Date__c, Room__c from Room_Allocations__r) from Room__c where Id NOT IN :allocatedRooms ORDER BY Lodge__c ASC, Room_No__c ASC]);

}
}

 

 

 

sfdcfoxsfdcfox
Here's my updated version:
<apex:page standardController="Room_Allocation__c"  extensions="Roommanager" tabStyle="Room_Availability_Search__tab">
    <apex:form >
    
        <apex:sectionHeader title="Room Availability Search"/>
                    
            <apex:pageBlock title="Search">
                <apex:pageBlockButtons >
                    <!-- Here's where {!Search} should be. -->
                    <apex:commandButton action="{!search}" value="Search"/>
                </apex:pageBlockButtons>
                <apex:pageBlockSection columns="2">
                    <!--
                        We are using a standard controller, so reference that controller instead.
                        We do this to make the code a bit more readable, as future coders won't have
                        to look at the controller to guess what the variable "room" is. Room is now
                        a private variable, too.
                    -->
                    <apex:inputField value="{!Room_Allocation__c.Search_Start_Date__c}"/>
                    <apex:inputField value="{!Room_Allocation__c.Search_End_Date__c}"/> 
                </apex:pageBlockSection>
            </apex:pageBlock>  
            
            <apex:pageBlock title="Available Rooms">
                <apex:pageBlockSection >
                    <apex:pageBlockTable value="{!availablerooms}" var="avail">
                        <apex:column value="{!avail.Lodge__c}"  />
                        <apex:column value="{!avail.Room_No__c}"  />
                        <apex:column value="{!avail.Name}"  />                        
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
            <apex:pageBlock title="Booked Rooms">
                <apex:pageBlockSection >
                    <!--
                        By removing the global "room" variable, we avoid ambiguity between
                        this local variable and the global variable. This is to help
                        future developers that might look at the code.
                    -->
                    <apex:pageBlockTable value="{!rooms}" var="room">
                        <apex:column value="{!room.Room__r.Lodge__c}"  />
                        <apex:column value="{!room.Room__r.Room_No__c}"  />
                        <apex:column value="{!room.Room__c}"  />
                        <apex:column value="{!room.Name}"  />                        
                        <apex:column value="{!room.Start_Date__c}"  />
                        <apex:column value="{!room.End_Date__c}"  />
                        <apex:column value="{!room.Nights__c}"  />
                        <apex:column value="{!room.Reservation__c}"  />   
                        <apex:column value="{!room.Reservation__r.Reservation_Contact__c }"  />                         
                    </apex:pageBlockTable>
                </apex:pageBlockSection>   
            </apex:pageBlock> 
            
    </apex:form>
</apex:page>

 

public class Roommanager {
    public void search() {
        // Provide default values in the event that no dates are selected. This is optional.
        if(room.Search_Start_Date__c==null)
            room.Search_Start_Date__c=System.today().toStartOfMonth();
        if(room.Search_End_Date__c==null)
            room.Search_Start_Date__c=System.today().toEndOfMonth();
        
        // Swap the dates if start is before end. Optional nice logic.
        if(room.Search_Start_Date__c>room.Search_End_Date__c) {
            Date tempDate = room.Search_Start_Date__c;
            room.Search_Start_Date__c = room.Search_End_Date__c;
            room.Search_End_Date__c = tempDate;
        }
        
        // Remove previous search results, if any.
        rooms.clear();
        availableRooms.clear();
        
        // Find all the room allocations, add them to the list.
        rooms.addAll(
            [SELECT
                Id, Name, Start_Date__c, End_Date__c, Nights__c, Room__c, Room__r.Room_No__c, Room__r.Lodge__c, Reservation__c, Reservation__r.Reservation_Contact__c
            FROM
                Room_Allocation__c r     
            WHERE (
                (Start_Date__c >= :room.Search_Start_Date__c  AND Start_Date__c <= :room.Search_End_Date__c) OR
                (End_Date__c  >= :room.Search_Start_Date__c  AND End_Date__c  <= :room.Search_End_Date__c) OR
                (Start_Date__c <= :room.Search_Start_Date__c  AND End_Date__c  >= :room.Search_End_Date__c))  
            ORDER BY
                Room__r.Lodge__c ASC,Room__r.Room_No__c ASC, Room__r.Lodge__c ASC]);
        
        // Build a list of allocated rooms.
        Set<Id> allocatedRooms = new Set<Id>();
        for(Room_Allocation__c roomAlloc:rooms)
            allocatedRooms.add(roomAlloc.Room__c);
        
        // Add the rooms that are still available.
        // I like making queries indented for legibility.
        availableRooms.addAll(
            [SELECT
                Id, Name, Room_No__c, Lodge__c,
                (SELECT
                    Id, Name, Start_Date__c, End_Date__c, Room__c
                FROM
                    Room_Allocations__r)
            FROM
                Room__c
            WHERE
                Id NOT IN :allocatedRooms
            ORDER BY
                Lodge__c ASC, Room_No__c ASC]
        );
    }

    private Room_Allocation__c room;
    
    public List<Room_Allocation__c> rooms { get; set; }
    public List<Room__c> availableRooms { get; set; }
    
    // COnstructor. We initialize our memory variables here.
    public Roommanager(ApexPages.StandardController controller) {
        room=(Room_Allocation__c)controller.getRecord();
        rooms=new List<Room_Allocation__c>();
        availableRooms = new List<Room__c>();
        // Do a default search for our user.
        search();
    }
}

I added some optional features just as a suggestion, but note that the main problem that you had was that you were calling a null function {!room} instead of the {!search} function (see the VF page). Other than that, most of your code was sound. I assumed the queries were already working more or less okay and left them alone.

Note the other piece, where I referenced "room" by the controller instead of by a member variable in the VF page. Since they both point to the same place, you reduce (developer) confusion by using the standard controller when possible. There's no other real benefit here, but legible code is always a plus.
Finally, as you can see here, we consolidated the search logic into a single function, which provides the greatest flexibility.
Let me know how it works out.
Edit: Changed a private modifier to public.
This was selected as the best answer
Laytro80Laytro80

Thanks so much for this I am getting an initial error.

 

ErrorError: Method is not visible: [Roommanager].search()
Laytro80Laytro80

Ah I think I know the problem I need to change the search from private to public.  Will test this

Laytro80Laytro80

Thanks SFDCfox for the amazing support.  I have changed the private to public on the search method and all seems to be working very well.  I will test more throughly over the weekend.

 

It looks good so I have marked the post as a solution.

 

Thanks again for everything, the notes are so helpful to me as a new developer.

sfdcfoxsfdcfox

You're absolutely right on the private access modifier for the search function. I'm not sure how I managed to do that, but you figured it out on your own easily enough. Have a great Easter weekend!

Laytro80Laytro80

Thanks again for all your help.  The documentation is so good it really helps me.

 

I have tested all the functionality and your new code works like a charm, much faster as well.

 

Now have to build a test script, I hate to ask but would be great to get your help on this.

 

But I do realise I have taken a lot of your time already so no worries if note.

 

Thanks again

sales4cesales4ce

Any ideas on how to use the same criteria in Triggers. Specifically, i mean for Bulk loading.

The query checks for 1 record at a time, but when using triggers we need to make sure to check for bulk load as well.

I am not able to modify this to get it to work for bulk load. any help is highly appreciated.

 

Thanks,

Sales4ce