Searching by Date -- Help?

[RobR]

Hi all -- Kind of a newbie here, but not too clueless . . . Here's what I'm trying to do. I have a Web Companion site up and running. It serves a small database containing a number of records of events having a "Start Date" and an "End Date." (There are fields with those names, each formated MM/DD/YYYY.) I want web users to be able to enter a specific date on a form, e.g., 12/12/2004, and have the server return all records of events that are happening on that date. Obviously, doing a straight search for events having a Start Date of 12/12/2004 only returns events starting on that date and misses events that start earlier but continue through that date. I guess I need a script or something that will look for all events in the database that (a) start on or before the date entered by the user and which (: end on or after that date.

Any ideas?

Thanks in advance, Rob

[Roger1]

This is a manual way. try searching for less than / equal to the date they are looking for... eg <=12/12/2004 returns all dates before and including 12/12/2004

[Jeff Spall]

Hi,

try searching the forums for:

smartranges

(go back the full two years)

This is some very awsome work done by a number of FMP gurus on date-to-range, range-to-date, range-to-range relationships. I think it might contain elements of what you need

regards, jeff

[RobR]

Jeff -- Thanks for the tip. I did a search and pulled up some information on smartranges and date searching, but most of the discussion out there concerns scripting within FMP as opposed to searches originating from a web user.

[Garry Claridge]

If you are using Instant Web Publishing would Rodger's suggestion work? For example:

Start Date: >=12/12/2004

End Date: <=12/12/2004

If you are using Custom Web Publishing (CDML) you can use some Javascript to construct the range. As Jeff mentioned there have been some good discussion on date ranges for CDML.

All the best.

Garry

[RobR]

Garry and Jeff --

I know how to assign the date inputed by the user to the "Start Date" value and to have it find all records beginning on or before that date (-op = "lte"). But how do I assign that same value to the "End Date" (-op = "gte") without asking the user to input the date twice--awkward. In other words, I want the user to have to enter the date ONCE and have FMP treat that date as both the Start Date and the End Date and do the two calculations.

<form method=post action="FMPro"

<input type=hidden name="-find">

<input type=hidden name="-db" value="DCDB.fp5">

<input type=hidden name="-format" value="onthecalendar.html">

<input type=hidden name="-op" value="ite"

<input name="Start Date">

*** WHAT THE HECK SHOULD GO HERE ***

<input type=hidden name="-max" value="10">

<input type=hidden name="-sortfield" value="Start Date">

<input type=hidden name="-sortorder" value="ascend">

</form>

Appreciate the help, guys.

Rob

[Garry Claridge]

Seeing that you are using Custom Web Publishing (CDML) I recommend that you use some Javascript to add the value to both the "Start" and "End" fields.

For example,

<script>

function dodate()

   {

   document.calform.elements["End Date"].value = document.calform.elements["Start Date"].value ;

   document.calform.submit();

   } ;

</script>

</head>

....

<form name="calform" action="FMPro" method="POST" onsubmit="dodate(); return False;">

<input type=hidden name="-find">

<input type=hidden name="-db" value="DCDB.fp5">

<input type=hidden name="-format" value="onthecalendar.html">

<input type=hidden name="-op" value="gte">

Enter Date: <input type="text" name="Start Date">

<input type="hidden" name="-op" value="lte">

<input type="hidden" name="End Date">

<input type=hidden name="-max" value="10">

<input type=hidden name="-sortfield" value="Start Date">

<input type=hidden name="-sortorder" value="ascend">

<input type="submit" name="-find" value="Find Events">

</form>

Hope this helps.

Garry

[RobR]

Garry -- This works PERFECTLY. (I had the change "lte" to "gte" and vice versa -- I think you mistakenly transposed them.) Thanks a million! I added some code to filter out any records having "Exhibit" in the "Event Type" field. I wonder if there is a way to prompt users to check a box if they want to filter out records with "Exhibit."

<script>

function dodate()

{

document.calform.elements["End Date"].value = document.calform.elements["Start Date"].value ;

document.calform.submit();

} ;

</script>

</head>

<form name="calform" action="FMPro" method="POST" onsubmit="dodate(); return False;">

<input type=hidden name="-find">

<input type=hidden name="-db" value="DesignCalendarDB.fp5">

<input type=hidden name="-format" value="onthecalendar.html">

<input type=hidden name="-op" value="lte">

Enter Date: <input type="text" name="Start Date">

<input type="hidden" name="-op" value="gte">

<input type="hidden" name="End Date">

<input type="hidden" name="-op" value="neq">

<input type=hidden name="Event Type" value="Exhibit">

<input type=hidden name="-max" value="10">

<input type=hidden name="-sortfield" value="Start Date">

<input type=hidden name="-sortorder" value="ascend">

<input type="submit" name="-find" value="Find Events">

</form>

THANKS AGAIN. I'm slowly but surely learning this stuff . . .

Rob

[Garry Claridge]

Try this:

Show only Exhibitions:<input type="checkbox" name="Event Type" value="Exhibit">

All the best.

Garry

[RobR]

Hmm -- I'm trying to give users the option of excluding records from their search that have the term "Exhibit" in the "Event Type" field. This is my stab at it:

<script>

function dodate()

{

document.calform.elements["End Date"].value = document.calform.elements["Start Date"].value ;

document.calform.submit();

} ;

</script>

</head>

<form name="calform" action="FMPro" method="POST" onsubmit="dodate(); return False;">

<input type=hidden name="-find">

<input type=hidden name="-db" value="DesignCalendarDB.fp5">

<input type=hidden name="-format" value="onthecalendar.html">

<p class="style26"><span class="style22">Search Any Day:

<input type=hidden name="-op" value="lte">

<input type="text" size=12 name="Start Date">

<input type="hidden" name="-op" value="gte">

<input type="hidden" name="End Date">

Exclude Exhibits:

<input type="checkbox" name="-op" value="neq">

<input type=hidden name="Event Type" value="Exhibit"

<input type=hidden name="-max" value="10">

<input type=hidden name="-sortfield" value="Start Date">

<input type=hidden name="-sortorder" value="ascend">

<input type="submit" name="-find" value="Find Events ">

<input type=hidden name="-error" value="noresultsfound.html">

</span></p>

</form>

This has the effect of "toggling" the search: if not checked, it pulls up ONLY Exhibits; if not checked, it pulls up EVERYTHING BUT Exhibits. I would like it to find all records for the inputted date (including Exhibits) by default, but if the box is checked to pull up all records for the inputted date with the exception of Exhibits.

What am I missing?

(Thanks again for nursing me through this, Garry.)

Best, RR

[Garry Claridge]

Try this:

Do Not Show Exhibitions:<input type="checkbox" name="Event Type" value="<>Exhibit">

And delete the line:

<input type="checkbox" name="-op" value="neq">

All the best.

Garry

[RobR]

Nope, sorry. If checked, it returns "no results found." If not checked it correctly finds all events on that date, including exhibits. The problem is the line:

Do Not Show Exhibitions:<input type="checkbox" name="Event Type" value="<>Exhibit">

I'll keep working on it.

Thanks, RR

[RobR]

Got it. This is the right code:

Exclude Exhibits:

<input type=hidden name="-op" value="neq">

<input type="checkbox" name="Event Type" value="Exhibit" checked>

Thanks for the help, Garry.

Best, RR