Ideas on how to display dates not currently used in a drop down.....

[TonyU]

I've developed an internal website\schedule where all users are able to schedule our conference rooms at any time.

The problem I'm having is that because the way I have the

start time &
end time formatted

I can not force the users NOT TO select the same time already in the database thus creating conflicting times when scheduling

See time format below:

I was wondering on what the best way to approach this would be.
Thanks in advance all...

[tt]
^{"The only ideas that will work for you are the ones you put to work."}
[/tt]

​

 

[Targol]

Well I have a few questions and ideas :
Q :
How do you store your times in DB (string or datetime) ?
I :
May be the best way to perform what you want is to cut your page in 2 : on the first page, ask the room and the date. Once you've got these elements, you're able to give the user the times when this room is free for this day. Water is not bad as soon as it stays out human body ;-)

 

[TonyU]

I'm using Access 2000 and the
start\end times are medium Date\Time fiels

Thanks for that idea, I'm trying to gather as many as possible.

I was thinking about storing all my times in a drop-down menu say in increments of 1/2 hour then have those times not display if already used.

[tt]
^{"The only ideas that will work for you are the ones you put to work."}
[/tt]

​

 

[krisbrixon]

well, for us to understand the problem we might need to know how the times are formated.

Kris

ps. the screen looks nice.

 

[krisbrixon]

ignore last post, I waited too long to send it and the question was already answered.
Kris

 

[Targol]

Well, I think this is a good way but may be a good one would be to store only the start time and then a duration in minutes as an integer (ie 120 for two hours) in place of the endind time. I think that this way should ease the computing of the free hours. Water is not bad as soon as it stays out human body ;-)

 

[krisbrixon]

I do not understand the problem:

I understand that if all you send the computer is the time then the date associated with it may be bogus, but it will be consistant. So, why not search on mutiple criteria.

SELECT COUNT(FIELD1) FROM tblSCHEDULING
WHERE SCHEDULE_DATE = txtDate.text
AND START_TIME >= txtStartTime.Text
AND END_TIME <= txtEndTime.Text
AND LOCATION = txtLocation.Text

IF value > 0 Then
show the user when the conference is scheduled and ask them to pick a different time.

Or an I just not getting the problem?

Kris

 

[Targol]

Well Kris, your query is almost good but it only detects two meetings that EXACTLY have the same time that the one teh user wants to get or that is INCLUDED into the new meeting. Let's try to explain whith a time ladder :

/---------AM----------\ /--------PM-----------0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 0 0 1 1 1
1 2 3 4 5 6 7 8 9 0 1 2 1 2 3 4 5 6 7 8 9 0 1 2
              |-----|   -> the new meeting a user want to schedule
Your query will detect these ones :
                |--|    -> included
              |-----|   -> equal
but not these ones :
           |-----|      -> starting before, ending during.
                |-----| -> starting during, ending after.

Water is not bad as soon as it stays out human body ;-)

 

[TonyU]

ps. the screen looks nice.
Thanks.

Kris\Targol, I've been (trying) working with ASP for about a year now and am begining to better undertand it's function\syntax\productivity potential, tho your suggestion makes sense, I don't quite undertand how to implemented.

Would you mind explaining it in more detail. Thanks

I'm sure you guys understand what I'm trying to do, but just to be more clear in my explanation:

John Smith
Reserved conference room 1
for 10:00 am to 11:30 am
on 10/3/02

next user wants to also schedule the same conference room for the same day and after viewing the displayed schedule he\she forgets that the times from 10:00 am to 11:30 am are already taken so she goes and schedules it.
Right now when this happens, I have an edit schedule page where they make changes. See my problem?

So, my thinking was, if I store times in a 1/2 hour increments in a drop-down box and not show them as they are used it would be a good idea.

[tt]
^{&quot;The only ideas that will work for you are the ones you put to work.&quot;}
[/tt]

​

 

[Targol]

My remark was not about user interface but about data storing. for user interface, I should do it the way you writed for the beginning time (select with half hours options excluding already used hours) but for ending, i'd replace it with a duration select with options from 1/2h to 4h (it's widely enough for a meeting isn't it ? ;-)). The point is that I'd make a script on the &quot;onchange&quot; event of the starting time select to remove some of the options of the duration select to avoid schedule conflicts. Water is not bad as soon as it stays out human body ;-)

 

[mwolf00]

I would actually suggest that you don't use end times. If you want use half hour increments in the droplist, make sure you make it so users can select mulitple times. Then write each half hour block in the bookings table individually(based on start time only). Then select all bookings for each room in order of time. You'll have to decide how many days you want to make available in the droplist (8hrs/day = 16 time slots x 14 days{10 business days})... You get the picture.

Then you would need to do something like this for each room:

<%
rightNow = Date()-1
'default to the current day - midnight 

sql = _
&quot;SELECT booktime &quot;&_
&quot;FROM booking &quot;&_
&quot;WHERE room = &quot;&roomNum&_
&quot;AND booktime > #&quot;&rightNow&&quot;#&quot;&_
&quot;ORDER BY booktime&quot;

set objRS = objCN.execute(sql)
if not objRS.EOF then
	numDays = 14			'number of days wanted in list
	interval = 30				'time increment wanted in minutes
	startHour = 8 * 60		'start time in list in minutes
	endHour = 18 * 60		'end time in list in minutes
	
	for dayCount = 0 to numDays
		rightNow = dateAdd(&quot;d&quot;,1,rightNow)
		for minuteCount = startHour to endHour step interval
			thisTime = dateAdd(&quot;n&quot;,minuteCount,rightNow)
			if thisTime>now() then
				if thisTime = objrs(&quot;booktime&quot;) then
					objrs.movenext
				else
					optionStr = optionStr & &quot;<option value='&quot;&thisTime&&quot;'>&quot;&formatDateTime(thisTime)
				end if
			end if
		next
	next	
end if
%>

 

[entaroadun]

Why force users to NOT select booked times?

Just use server-side validation to kick out unacceptable requests. You have to validate on the server-side before you book the room anyways, because:


User 1 goes to page to see conference room availability. Invalid times are blocked out.

User 2 goes to page. Requests conference room for block of time. Request approved.

User 1 doesn't know about User 2's time, because the page is stale. User 1 makes request overlapping User 2's time. The server must reject this request.


This renders the whole point moot, since you need to validate the request after it is submitted anyways.

If you are bent on client-side validation, you can download the time blocks to be stored in a hidden form element. The submit button starts out disabled. Whenever the user updates the text boxes with start and end-times, your client-side script validates the request. Only if the request is valid does the submit button become enabled and the user is able to click it. You can have a label saying &quot;Invalid time selected&quot; that disappears when the start and end-times are valid.

There are many ways to skin a cat. I prefer this way because a) you need no fancy drop-down boxes, regular text boxes will do, and b) you don't limit bookings to half-hour increments, any time is valid, as long as there is no overlap. Just write a client side script that can validate the input times vs. the list in memory. Hope this helps.

 

[TonyU]

[tt]
thanks for all your responses, after all my brainstorming the database guru will handle it from his side.

Phew!

[tt]
^{&quot;The only ideas that will work for you are the ones you put to work.&quot;}
[/tt]

​