db.search and time

I have the following fields date and start time & end time.

Given this information i need to go to the room reservation database and pick up all the documents that matches start time and end time. Is it possible using db.search

for ex: 03/02/2005 and start time = 9:00 am , end time = 11:00 pm

 rooms  booked between the times mentioned above shoudl be returned, so if a room is booked between 9:30 - 10 or time between 7:00- 9:30 should be returned...the times are separted for every half and hour

Can anybody state the logic for me please using formulas

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
Hmm. It might be possible with db.Search, but it might also be very slow. If it's possible you could set up FullText-search on the database, although that will cost some performance as well to the system, to maintain the index. The db.FTSearch call is usually a lot faster, perdominantly when you have a large database.

By far the fastest is a simple view, sorted on start-time. Walk through all documents until start-time is past your required end-time.
the simple logic will be creating a multi value field(list_x) and assigining values like:
take the same example like the above:
03/02/2005 9:00AM 11:00PM

Now through your code, you have to generate the following values and assign to the field: list_x .
like that....
Now u have all those values in the field. Create a view and in the first column, place this field name (list_x).
Show multi value as Separate entries.
Now all the values will shatter and display as separate entries. let it be sorted and categotised.
Now when a person is booking resource for 03/02/2005 10:00AM 11:00AM, u merge these like:

while searching whether there is a booking or not, loop through the above values and check each value into the new view which u have created and if it matches, give  a prompt that it is booked and END the process.  This will be easy to maintian Modify/Delete to the booked rooms.

Without FTSearch,db.Search, u can use:
set doc=view.getdocumentbykey(cstr(tmpkey(x)),true)
where tmpkey(x) is an array having the concatenated values (03/02/2005900AM930AM).

Looks simple right. Try it out and this will work.

srevenkAuthor Commented:
I want it to be done using formulas and db.search and i cannot use FTSEarch as it will not index immediately

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

if u are on a ND6, then u have luck. Since u can write loops through Formulae. That too its difficult.

Best bet would be Lotus Script.
if my logic is not clear, then let me know.
Sjef BosmanGroupware ConsultantCommented:

> I want it to be done using formulas and db.search

Virtually impossible in Formula. It's easy with LotusScript.

Just a question: don't you want to find rooms that are partially booked, like a room that's booked from 08:00 - 10:00?

What you're looking for is all room-reservations that match the following condition:
they have an end-date/time that's larger than the begin-time of the time-period you're looking for AND
they have a begin-date/time that's less than the end-time of the time-period you're looking for

If a Search that can easily be expressed as

    edt>=bt & bdt>=et

In case of a Search, assuming edt and bdt are field values in a document, bt and et could be filled in into the string, like this:

    edt>=[2/3/2005 9:00 AM] & bdt>=[2/3/2005 11:00 PM]

Hope this helps.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: sjef_bosman {http:#13447855}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.