You need to sign in to do that
Don't have an account?
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
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.
All Answers
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:
Safe harbor - I haven't compiled this, so my date instantiators may be incorrect, but hopefully you get the idea.
Given StartDateSearch, StopDateSearch, StartDate__c, and StopDate__C, the following are overlapping records:
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.
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:
looks like it should be revised to:
Otherwise it won't catch the full overlap of the "end" time of your object with the criteria.
Nice catch, Bob. I'll update that rather obvious typo in my original post.
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.
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:
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.
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.
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.
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.
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
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.
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
The Apex Controller I currently have
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.
Thanks so much for this I am getting an initial error.
Ah I think I know the problem I need to change the search from private to public. Will test this
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.
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!
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
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