How to Search in Numeric or Date Fields

8. How to Search in Numeric or Date Fields

SFgate allows also the query of two special kind of fields:

DATE FIELDS
NUMERIC FIELDS

1. Date Fields

In the case of date fields you have a rich choice of formats to input data.
Generally in a specific form you are request for a specific format to use. At the moment no database at BioPD uses this feature.
However, in the figure below all kind of possible formats are reported.

There are two possibilities to specify a date field within a SFgate form (within a freeWAIS-sf databases date fields are indexed in yyyymmdd notation)

Mode 1

specifying date in one of the following formats:

yyyymmdd, yymmdd
dd.mm.yyyy, dd.m.yyyy, d.mm.yyyy, d.m.yyyy, dd.mm.yy, dd.m.yy, d.mm.yy, d.m.yy
dd.mm, dd.m, d.mm, d.m
yyyy-mm-dd, yyyy-mm-d, yyyy-m-dd, yyyy-m-d, yy-mm-dd, yy-mm-d, yy-m-dd, yy-m-d
dd/mm/yyyy, dd/m/yyyy, d/mm/yyyy, d/m/yyyy, dd/mm/yy, dd/m/yy, d/mm/yy, d/m/yy

Mode 2

1. Numeric Fields

In the case of numeric fields you can query for values:

> (greater than)
>= (greater than or equal to)
== (equal to)
<= (lower than or equal to)
< (lower than)

All these numeric predicates can be choosen from a drop-down list

Please note that also fractional number and negative numbers are allowed. Forms can be made in such a way that the same numeric or date field can be searched more than once so that a search for a range of numeric values or dates can be made.

A LIVE example is reported below.

Fields Group 1

Fields Group 2

Here you can inspect the .fmt file, while the relevant part of this form source code is here:

<FORM METHOD=POST ACTION="/htbin/SFgate">
<INPUT NAME="database" TYPE="hidden" VALUE="local//usr/local/wais-sources/telethon/telethon">

<CENTER>
<TABLE BORDER WIDTH=70%>
   <TR><TH> </TH><TH>
       <TABLE WIDTH="100%">             
          <TR><TH COLSPAN=3 align=left><FONT SIZE="+2">Fields Group 1</FONT></TH></TR>
          <TR><TD><SELECT NAME="fieldsel_1_description">
                    <OPTION SELECTED>Title of the Project
                    <OPTION>Investigator
                    <OPTION>Department
                    <OPTION>City
                    <OPTION>Classification of the Project
                    <OPTION>Grant
                   </SELECT></TD>
              <TD><INPUT TYPE="text" NAME="fieldsel_1_content" SIZE=40></TD>

              <TD><SELECT NAME="fieldsel_1_tie">
                    <OPTION>and
                    <OPTION>or
                  </SELECT>
              </TD></TR>

          <TR><TD><SELECT NAME="fieldsel_2_description">
                    <OPTION>Title of the Project
                    <OPTION SELECTED>Investigator
                    <OPTION>Department
                    <OPTION>City
                    <OPTION>Classification of the Project
                    <OPTION>Grant
                   </SELECT></TD>
              <TD WIDTH=50%><INPUT TYPE="text" NAME="fieldsel_2_content" SIZE=40></TD></TR>
       </TABLE>   

<TR><TD>
    <SELECT NAME="group_2_tie">
            <OPTION>and
            <OPTION>or
    </SELECT>
</TD></TR>
<TR><TH> </TH><TH>
        <TABLE ALIGN=left>             
          <TR><TH COLSPAN=4 align=left><FONT SIZE=+2>Fields Group 2</FONT></TH></TR>
          <TR><TD><SELECT NAME="fieldsel_3_description">
                    <OPTION>Time Extension
                    <OPTION>Renewals
                    <OPTION SELECTED>Financial Support
                   </SELECT></TD>

              <TD><SELECT NAME="fieldsel_3_p">
                    <OPTION> &gt;
                    <OPTION SELECTED> &gt;=
                    <OPTION> ==
                    <OPTION> &lt;=
                    <OPTION> &lt;
                  </SELECT></TD>

              <TD><INPUT TYPE="text" NAME="fieldsel_3_content" VALUE="300" SIZE=3 MAXLENGHT=3></TD>

              <TD><SELECT NAME="fieldsel_3_tie">
                    <OPTION>and
                    <OPTION>or
                  </SELECT></TD></TR>


          <TR><TD><SELECT NAME="fieldsel_4_description">
                    <OPTION>Time Extension
                    <OPTION>Renewals
                    <OPTION SELECTED>Financial Support 
                  </SELECT></TD>

              <TD><SELECT NAME="fieldsel_4_p">
                    <OPTION> &gt;
                    <OPTION> &gt;=
                    <OPTION> ==
                    <OPTION> &lt;=
                    <OPTION SELECTED> &lt;
                  </SELECT></TD>
        
               <TD COLSPAN=2><INPUT TYPE="text" NAME="fieldsel_4_content" VALUE="800" SIZE=3 MAXLENGHT=3></TD></TR>
        </TABLE>
    </TH></TR>
</TABLE>   
</CENTER>
<P>

<INPUT TYPE="hidden" NAME="fieldsel_name_title" VALUE="Title of the Project">
<INPUT TYPE="hidden" NAME="fieldsel_name_investigator" VALUE="Investigator">
<INPUT TYPE="hidden" NAME="fieldsel_name_department" VALUE="Department">
<INPUT TYPE="hidden" NAME="fieldsel_name_city" VALUE="City">
<INPUT TYPE="hidden" NAME="fieldsel_name_classification" VALUE="Classification of the Project">
<INPUT TYPE="hidden" NAME="fieldsel_name_grant" VALUE="Grant">
<INPUT TYPE="hidden" NAME="fieldsel_name_timeext" VALUE="Time Extension">
<INPUT TYPE="hidden" NAME="fieldsel_name_renewals" VALUE="Renewals">
<INPUT TYPE="hidden" NAME="fieldsel_name_amount" VALUE="Financial Support">

<INPUT TYPE="hidden" NAME="group_1" VALUE="fieldsel_1, fieldsel_2">
<INPUT TYPE="hidden" NAME="group_2" VALUE="fieldsel_3, fieldsel_4">

<CENTER>
<TABLE>
<TR>
<TH><INPUT TYPE="submit" VALUE="Start Search">
</TH><TH> </TH><TH>
<INPUT TYPE="reset"  VALUE="Reset Query">
</TH></TR>
</TABLE>
</CENTER>
<P>

<INPUT TYPE="hidden" NAME="tieinternal" VALUE="and">
<INPUT TYPE="hidden" NAME="application" VALUE="projects">
<INPUT TYPE="hidden" NAME="convert" VALUE="Table">
<INPUT TYPE="hidden" NAME="listenv" VALUE="table">
<INPUT TYPE="hidden" NAME="range" VALUE="1">
</FORM>

In particular note how the predicate is used, for example in this case:

              <TD><SELECT NAME="fieldsel_3_p">
                    <OPTION> &gt;
                    <OPTION SELECTED> &gt;=
                    <OPTION> ==
                    <OPTION> &lt;=
                    <OPTION> &lt;
                  </SELECT></TD>

© 1996-2003 BioPD - University of Padova (Italy) - Author: Leopoldo Saggin - Last version: January 28, 2003
Best efforts were made to provide correct information, however this document may contain technical inaccuracies and/or typographical errors.
The author declares that this material is provided "as is" without any warranty even in the implied warranty of merchantability or fitness for a particular purpose.
All trademarks cited inside this document are property of their respective owners