Finding range of dates over web

[Peter Knowles]

I've created a database which allows teachers at our school to reserve videoconferencing equipment and alerts our tech person as to the availability of it. I want to allow users to search for bookings between certain dates. The date fields are currently formatted as drop-down choices to enter into three separate FM fields (day, month, and year). I'd like to allow people to search for all bookings between, say, 10/16/00 and 11/15/00. However, I don't know if I need to use different fields for a gte and lte search query (to produce a range of bookings after the first date -- gte -- and before the second date -- lte). So far, all I get is "find all" results or bad command replies. It seems this must be a common thing to do, but I've yet to find it addressed in any FM literature or in the help sections. Any ideas?

[Vaughan]

Could the problem be partly caused by the way that peope are selecting dates from the drop-down menus? I mean, how does FMP combine the three different fields into one date value?

Try these two steps: perform the find in FMP to determine the exact find criteria syntax you need to perform the search between two dates (I have no idea how to do it). You might need to use multiple find requests or something.

Once you have the find criteria syntax worked out, get the web solution working where you just type the dates into text fields (forget the menus for now). You might find that Web Companion requires a different date format from what you used in FMP.

OK, now you know that it can be done at least with text fields and you know the exact format that FMP needs the search to be in. Now you have to build up that string using a combination of hidden fields and drop-down menus. This exploits the feature that if you have three text areas (or whatever) in your html form that all reference the same FMP field, Web Companion will join all the text up into one string.

I should state clearly that I haven't done this myself yet. I imaging you'll need to have a web form that looks like this:

[drop-down menu with month number, 1 to 12]

[hidden field with delimiter character "/"]

[drop-down menu with day number, 1 to 31]

[hidden field with delimiter character "/"]

[drop-down menu with year number, 2000 to 2005]

Note that these reference the same date field. You'll also need to have more form stuff to generate the .gt. and .lt. operators and define the other date as well. It's a lot of work.

Hopefully, when the submit button is clicked, web companion will get all five fields and join them all up into the one string in the date field, then perform the find.

Good luck.

[This message has been edited by Vaughan (edited October 17, 2000).]

[Anatoli]

quote:

---

Originally posted by Peter Knowles:

I've created a database which allows teachers at our school to reserve videoconferencing equipment and alerts our tech person as to the availability of it. I want to allow users to search for bookings between certain dates. The date fields are currently formatted as drop-down choices to enter into three separate FM fields (day, month, and year). I'd like to allow people to search for all bookings between, say, 10/16/00 and 11/15/00. However, I don't know if I need to use different fields for a gte and lte search query (to produce a range of bookings after the first date -- gte -- and before the second date -- lte). So far, all I get is "find all" results or bad command replies. It seems this must be a common thing to do, but I've yet to find it addressed in any FM literature or in the help sections. Any ideas?

---

I am using standard date fields with begin...end syntax. I know visitors are forced to learn this FileMaker thing, but only few complained. Probably the same number said it is clever interface thinking, and thought I've developed that myself :-)

[This message has been edited by Anatoli (edited November 06, 2000).]