Developing a form for searching a database

is it Web or Notes?

Notes Client

Sorry Jaziar, I didn't mean it that way :)

You create the form, with the fields, then you call an agent from a button using JavaScript (easiest). The agent will fetch the data from the URL, assemble a search string and call FTSearch.

The standard search string information can be found in the User Help database, but when you know names of fields etc, you'd better post that info here so we can put that string together for you.

There are some prerequisites however when using numbers in full-text searching: ALL fields with the same name HAVE to be in a number format, and I think this goes also for the date fields. To check whether a search wil work:
- enable full-text searching on the database
- open the fuill-text searchbar (Alt-V, E)
- enter your query-string manually

Can you post an example query that you'd like to try?

Ah, Notes, even easier... I assumed Web. Good question Maddy!

So... Sjef, are you providing the Solution ???

Bosman - no you really answered the question I was asking.  I had plans on opening a new question if possible.  I found your first answer funny and correct.

Does all the fields on the search form have to be in the search critera?  I mean maybe all I want is all the documents between April 1 and April 5.  Then maybe the user wants to search on all documents between April 1 and April 5 that was created by Joe Smith.  I dont think I need numbers now, but I will need to add a field fo subject.  So all documents between April 1 and April 5, that Joe Smith created with the subject "testing system" or it could be all documents between A1 and A5 with the subject "testing system"

I tend to do it with a table laid out with a radio button saying "date y/n" and if it is Y then it unhides a pair of date fields etc. for the user to fill in then use a LotusScript agent called from a search button to create a search string bsed on the fields, leaving out any that are blank or have their 'include' field to No.  In addtion some buttons to set the date range to 'last calendar month' 'last full year' and other useful ranges is often handy and simple to do.  I add a computed text hidden for normal users but visible for an admin role so I can see the search string is being constructed right.

Then you drop the results into a folder for the users to see, print as a report, and launch documents from.

I'm sure the others can supply the code but if you want anything from mine will post it too.

Steve

Form make-up is of a later concern. What you need is some flexible search string construction. Usually, the absence of field content will be sufficient to indicate that that field isn't interesting. Suppose a form with FromDate, ToDate, UserSearch, and you have two document fields called Date and User, you need to create a string like
    [Date]>= FromDate And [Date]<=ToDate And [User]=UserSearch

If any of the fields is left empty on the form, the corresponding part of the expression should be left out.

Code? Here's some examples:
    http:Q_21073459.html "How to search through documents based on a Criteria and place results in private folder"
    http:Q_20913646.html "FT Searching"

In your example, the query string you might try is:
    [_CreationDate]>=4/1/2006 And [_CreationDate]<4/6/2006 And [Author]="Joe Smith" And [Subject]="testing system"

Note that I used  ... <4/6/2006 instead of ... <= 4/5/2005 23:59:59

Here's my code if it helps at all.  I have a folder called Search_GS, shared, private on first use to hold the search results.  Works for me, might not be the best way so listening too :-)  To be able to clear the folder quickly the easiest way I found was to have a hidden sorted first column with known data in it "All" that could be brought into a document collection to remove from the folder.  Again might be better ways, it worked for me.

      Dim session As New notessession
      Dim db As notesdatabase
      Dim dc As notesdocumentcollection
      Dim searchdoc As notesdocument
      
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Dim datetime As New notesdatetime("1/1/1990")
      Dim searchfolder As notesview
      Set db=session.currentdatabase
      Set uidoc=ws.currentdocument
      
      Const foldername = "Search_GS"
      
' Empty the folder by selecting all documents in first column that have "All" (hidden fixed column)
      Set searchfolder=db.getview(foldername)
      Set dc=searchfolder.getalldocumentsbykey("All",False)
      Print "Removing old documents from folder"
      If Not dc Is Nothing  Then Call dc.RemoveAllFromFolder(foldername)
      
      
' Carry out search and re-populate the folder      
      
      Dim fromdate As String
      Dim todate As String
      Dim dept As String
      Dim incdate As String
      Dim incdept As String
      
      Dim count As Integer
      Dim cr As String * 1
      cr=Chr$(10)
      
      fromdate=uidoc.fieldgettext("GSF_DateFrom")
      todate=uidoc.fieldgettext("GSF_DateTo")
      dept = uidoc.fieldgettext("GSF_Department")
      incdept = uidoc.fieldgettext("GSF_IncludeDept")
      incdate = uidoc.fieldgettext("GSF_IncludeDateRange")
      
      searchformula$={Form="GS"}
      If incdate="Yes" Then searchformula$=searchformula$ & { & EI_Date >= [} & fromdate & {] & EI_Date<= [} & todate & {]}
      If incdept="Yes" And dept<>"" Then searchformula$=searchformula$ & { & (EI_Department="} & dept & {" | EI_TempDept="} & dept & {")}
      
      uidoc.fieldsettext "GSF_SearchString",searchformula$
      
      Set dc=db.search(searchformula$,datetime,0)
      Print "Adding new documents to folder"
      Call dc.PutAllInFolder(foldername)
      
      uidoc.Refresh

db.Search... Folder... Hmm. With FTSearch, you have your document collection, no folder, no mess to clean up. Only drawback: a full text index that needs some space.

Just Indexed the Database.
Now I will create a form with the following fields

fromDate
toDate
userSearch
subjectSearch

now that we have values for the fields - the user hits ok
is this where the search takes place?  I am assuming the user will click a button from the view page that will open the new search form and once OK is hit the view will be updated?

Here's a very interesting article on this subject:
    http://www-128.ibm.com/developerworks/lotus/library/appstrat-search/index.html
Go to the chapter called "Custom search forms"

Dragon-it: indeed, they use folders..  :-$

So I am a little lost - I have the form created with the values,

what is my next step?

Hello - Is there anyone out there????

I am not sure what to do next.  I have a form with values, I am not sure where to put the javascript?

Can someone help me get back on track, I really need to get this working today.

Jaz

You read the article? I think it says it all. And you have my example of the search string you need.

I am trying to use the Search code from the article and including what I need.  I have pretty much removed everything but the first page and last page of the table.

The first page is where I have added my fields to.  I have 4 fields

SearchWords (which came with the form)
FromDate
ToDate
SearchUser

There is an FTSQuery1 computed field (this fields creates the query string)  Here is the code in the field

CommonWords:=@GetProfileField("ProfileDoc"; "CommonWords");
CleanWords:=@Implode(@Replace(@UpperCase(@Explode(SearchWords)); "FIELD":"TITLE":"OR": "AND": "NOT": "EXACTCASE": "SENTENCE": "PARAGRAPH": "NOT" : "TERMWEIGHT": CommonWords; ""));
WildCleanWords:=@If(WildCards = "1"; @Implode("*" + @Explode(CleanWords) + "*"); CleanWords);
SearchWordQuery:=@If(
Clause= "1"; @Implode(@Explode(WildCleanWords); " AND " );
Clause= "2"; @Implode(@Explode(WildCleanWords); " OR " );
Clause= "3";  "\"" + @Implode(SearchWords) + "\"" ;
@Implode(SearchWords));
DatedWords:="(FIELD " + DateSpec+ ") AND (" + SearchWordQuery + ")";
DocQuery:= "(" + @LeftBack(@Implode("[Form]=" + @Trim(DocTypes) + " OR " ); 3 ) + ")";
@If(DocTypes!="All"; DocQuery + " AND (" + DatedWords + ")"; DatedWords)

Question 1.  How would I add my new fields to this query?

Here is the search button code

Sub Click(Source As Button)
      Dim w As New NotesUIWorkspace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim collection As NotesDocumentCollection
      Dim newsletter As NotesNewsletter
      Dim doc As NotesDocument
      Set db = session.CurrentDatabase
      Dim uidoc As notesuidocument
      Set uidoc = w.currentdocument
      Dim thisdoc As NotesDocument
      Set thisdoc = uidoc.document
      Call uidoc.refresh()
' test if user has NOT entered a question
      If thisdoc.SearchWords(0)=""  Then
            Msgbox "You did not enter any words or phrases to search for.  Please try again.", 16, "Error"
            Exit Sub
      End If
      
' test for index
      If db.IsFTIndexed Then
          ' if user is Designer, refresh the FTIndex if database has been modified after the FTINdex date
            If db.CurrentAccessLevel >= 5 Then
                  If ( db.LastModified > db.LastFTIndexed ) Then
                        Call db.UpdateFTIndex( False )
                  End If          
            End If
      Else
            Msgbox "The database is not full text indexed.  Please contact the Database Manager.", 16, "Error"
            Exit Sub
      End If
      
' check spelling
      If thisdoc.Spell(0) = "1" Then
            Call uidoc.Gotofield("SearchWords")
            Call uidoc.selectall
            Call uidoc.SpellCheck
            Call uidoc.refresh
      End If
      
      getVariants= Evaluate(|@Sum(@TextToNumber(Variants))|, thisdoc)  ' add both values
      Set collection = db.FTSearch( thisdoc.FTSQuery1(0) , Val(thisdoc.QTY(0)) ,Val(thisdoc.SortBy(0)), getVariants(0) )          
      If collection.Count = 0 Then
            Msgbox "There were no matching documents using your words or phrases.  Please try again.", 16, "Nothing Found"
            Exit Sub              
      Else      
            Set newsletter = New NotesNewsletter( collection )
            newsletter.DoScore = True
            newsletter.DoSubject = True
            newsletter.SubjectItemName = "Subject"
            Set doc = newsletter.FormatMsgWithDoclinks( db )            
            doc.subject = thisdoc.FTSQuery1(0)
            doc.form = "SearchResults"
            doc.SaveOptions = "0"
            Set uidoc = w.EditDocument(False, doc)
      End If
End Sub

Question 2. does anything in here need changed?

So I dont need to change the FTSQuery1 in the form

I was trying this

CleanWords:=@Implode(@Replace(@UpperCase(@Explode(SearchWords)); "FIELD":"TITLE":"OR": "AND": "NOT": "EXACTCASE": "SENTENCE": "PARAGRAPH": "NOT" : "TERMWEIGHT": CommonWords; ""));
WildCleanWords:=@If(WildCards = "1"; @Implode("*" + @Explode(CleanWords) + "*"); CleanWords);
SearchWordQuery:=@If(
Clause= "1"; @Implode(@Explode(WildCleanWords); " AND " );
Clause= "2"; @Implode(@Explode(WildCleanWords); " OR " );
Clause= "3";  "\"" + @Implode(SearchWords) + "\"" ;
@Implode(SearchWords));
@Implode(FromDate);
@Implode(ToDate);
@Implode(SearchUser);
DatedWords:="(FIELD " + DateSpec+ ") AND (" + SearchWordQuery + ")";
DocQuery:= "(" +  "[DateComposed]>=" + @Trim(FromDate) + " And " + " [DateComposed]<=" + @Trim(ToDate)  + " And " + "[Submittor]=" + @Trim(SearchUser) + ")";
@If(DocTypes!="All"; DocQuery + " AND (" + DatedWords + ")"; DatedWords)

If IsTime(FromDate) Then

I thought IsTime is a @ Command?

Oooops... I'm ALWAYS mistaken with IsTime and IsDate... You're right, you need IsDate in LotusScript. Sorry :$

Notes Error:
Query is not understandable
((FIELD ModifiedOn <= 05/04/2006) AND (James)([DateComposed] >= 01/01/2006)([DateComposed] <= 05/04/2006))

(James) is coming from the First Field WordSearch
[DateComposed] = the create document date on all the docs in the database

Is the FTSQuery1 causing the issue ->                 query= thisdoc.FTSQuery1(0)  
                                                               If Isdate(FromDate) Then
                                                       If query <> "" Then qyery = query & " And " 
                                                          query= query & "([DateComposed] >= " & FromDate & ")"
                                                               End If
                                                               If Isdate(ToDate) Then
                                                        If query <> "" Then qyery = query & " And " 
                                                           query= query & "([DateComposed] <= " & ToDate & ")"
                                                               End If
                                                                      ' modified end
                                                             Set collection = db.FTSearch( query, 25  )          

Typo, please, always use Option Declare in your code!

I accidentally put qyery instead of query. Sorry.

Just a hint: there are several fields that are always recognised by FTSearch, and two of them are _CreationDate and _RevisionDate (including the leading underscore. It isn't necessary to use separate fields, but it isn't wrong either.

See the User Help database, "Refining a search query using operators"

Also, in the Designer Help db, "Customizing search forms"

Bosman, I have some general questions maybe you can help me with.

Question 1.  Where is the query told to search all docs?  In the FTSQuery

DocQuery:= "(" + @LeftBack(@Implode("[Form]=" + @Trim(DocTypes) + " OR " ); 3 ) + ")";  - Is the refering to the search form or the Doc forms

this is the result of FTSQuery
((FIELD ModifiedOn <= 05/04/2006) AND (James)

I marked out the modified code and just left

query= thisdoc.FTSQuery1(0)
      'If Isdate(FromDate) Then
      '      If query <> "" Then query = query & " And " 
      '      query= query & "([DateComposed] >= " & FromDate & ")"
      'End If
      'If Isdate(ToDate) Then
      '      If query <> "" Then query = query & " And " 
      '      query= query & "([DateComposed] <= " & ToDate & ")"
      'End If
 ' modified end
      Set collection = db.FTSearch( query, 25  )          

It is not finding any docs - I know that James is very common in the docs

I know understand the question above

db.FTSearch( query, 25  )    

It searches the entire database.

Here is how it all ends

I removed all the code from the FTSQuery1 field and set the default as SearchWords (userInput)  It does work with the date logic you coded.

Ah, great! Sorry, was away for supper and couch/tv. I wondered what the code was for, but I suspected you had some special or later use for it. There is however some use: if you use special words in your query, they have to be quoted. E.g. if you want to look for a word like field, you must put it in the query like
    "field"
since field is a reserved word for full-text queries.

I am now trying to get the search to work with all the special features. I have learned quite a bit from this problem.   You have earned your points, but I will close this after I try a little more to get the search features working.  Is there a way to build the search before the search button is hit?

Example would be the field FTSQuery1 - can we build the code to get the search word and get the dates and build our query.  Then the search code stays the same.  

I don't want to user to have to put in a search word, if they need all docs between 2 dates.

The words can stay empty, no problem. If you want to see the query, you could insert a Print or a MessageBox:
    Print query
or
    MessageBox query

In fact, I don't quite understand what you want...

Let me better explain this a little better then.

On the first table of the SearchPage form - there is a field named FTSQuery1 (Computed)

CommonWords:=@GetProfileField("ProfileDoc"; "CommonWords");
CleanWords:=@Implode(@Replace(@UpperCase(@Explode(SearchWords)); "FIELD":"TITLE":"OR": "AND": "NOT": "EXACTCASE": "SENTENCE": "PARAGRAPH": "NOT" : "TERMWEIGHT": CommonWords; ""));
WildCleanWords:=@If(WildCards = "1"; @Implode("*" + @Explode(CleanWords) + "*"); CleanWords);
SearchWordQuery:=@If(
Clause= "1"; @Implode(@Explode(WildCleanWords); " AND " );
Clause= "2"; @Implode(@Explode(WildCleanWords); " OR " );
Clause= "3";  "\"" + @Implode(SearchWords) + "\"" ;
@Implode(SearchWords));
DatedWords:="(FIELD " + DateSpec+ ") AND (" + SearchWordQuery + ")";
DocQuery:= "(" + @LeftBack(@Implode("[Form]=" + @Trim(DocTypes) + " OR " ); 3 ) + ")";
@If(DocTypes!="All"; DocQuery + " AND (" + DatedWords + ")"; DatedWords)

As the user enters information the field updates with the query
here is what it looks like before anything is entered

([Form] = ) AND ([Field] AND [])  

I will enter James in the word search

Now it looks like

[FIELD ModifiedOn <= 05/05/2006] AND [James]

So I think the Search Action takes this as the query

Set collection = db.FTSearch( thisdoc.FTSQuery1(0) , Val(thisdoc.QTY(0)) ,Val(thisdoc.SortBy(0)), getVariants(0) )  

My question was can we build the query in FTSQuery1 before we hit Search?  It really is not that important.

Ah, build the query in that field, using Formula language? Should be possible, yes. But there are some things missing in the code or in the form:
- DocTypes?
- Clause?
- DateSpec?
- and the CommonWords?

That's why I thought that all code had better be removed, just to leave the query created using LotusScript.

Code like this should be added, starting with the last line:
    q1:= @If(DocTypes!="All"; DocQuery + " AND (" + DatedWords + ")"; DatedWords);
    q2:= @If(@IsTime(FromDate); "([Date]>=" + @Text(FromDate) + ")"; "");
    q3:= @If(@IsTime(ToDate); "([Date]<=" + @Text(ToDate) + ")"; "");
    @Implode(@Trim(q1:q2:q3); " AND")

OK - I agree lets do it in the script

' modified start
      Dim query As String
      Dim FromDate As Variant
      Dim ToDate As Variant
      'Dim SearchUser As String
      FromDate= thisdoc.FromDate(0)
      ToDate= thisdoc.ToDate(0)
      'SearchUser= thisdoc.SearchUser(0)
      query= thisdoc.FTSQuery1(0)
      If Isdate(FromDate) Then
            If query <> "" Then query = query & " And " 
            query= query & "([DateComposed] >= " & FromDate & ")"
      End If
      If Isdate(ToDate) Then
            If query <> "" Then query = query & " And " 
            query= query & "([DateComposed] <= " & ToDate & ")"
      End If
 ' modified end
      Set collection = db.FTSearch( query, 25  )    

How would I check for a null value in the   FTSQuery1(0)

FTSQuery1(0) is looking for a wordsearch but if nothing is entered I want it to show all documents or all documents between the dates - is that possible?
   

You don't have to, that's already included with the line
    If query<>"" Then...
only the final query should be tested, that it isn't the empty string.

Or do you want to prevent that users don't even enter a single word? Then add lines like
     query= thisdoc.FTSQuery1(0)
     If query="" Then
          Messagebox "Enter at least one word to search for"
          Exit Sub
     End If

So, in answer to your last question, I think the original code can handle that wordsearch is empty.

Hey thanks! It works??

It dont have all the bells and whistles, but it works.  As always thanks for your help.

Great!