Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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
1 Solution
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now