Developing a form for searching a database

Step 1 - create a form
Step 2 - add fields that I want to parse by
             Date1, Date2, UserSearch, Number1, Number2
Step3 - create a lotusscript / Javascript?
            get values from the above fields
            FTSearch
            popualte a view with selected documents?


            Need help on step 3?
JaziarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

madheeswarCommented:
is it Web or Notes?
0
JaziarAuthor Commented:
Notes Client
0
Sjef BosmanGroupware ConsultantCommented:
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?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Sjef BosmanGroupware ConsultantCommented:
Ah, Notes, even easier... I assumed Web. Good question Maddy!
0
madheeswarCommented:
So... Sjef, are you providing the Solution ???
0
JaziarAuthor Commented:
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"
0
Steve KnightIT ConsultancyCommented:
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
0
Sjef BosmanGroupware ConsultantCommented:
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"
0
Sjef BosmanGroupware ConsultantCommented:
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
0
Steve KnightIT ConsultancyCommented:
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
0
Sjef BosmanGroupware ConsultantCommented:
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.
0
JaziarAuthor Commented:
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?
0
Sjef BosmanGroupware ConsultantCommented:
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..  :-$
0
JaziarAuthor Commented:
So I am a little lost - I have the form created with the values,

what is my next step?
0
JaziarAuthor Commented:
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
0
Sjef BosmanGroupware ConsultantCommented:
You read the article? I think it says it all. And you have my example of the search string you need.
0
JaziarAuthor Commented:
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?
0
Sjef BosmanGroupware ConsultantCommented:
I suppose you don't need variants, nor the complex code with FTSQuery1 at the beginning of your last post.

Code like:

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

' 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 IsTime(FromDate) Then
         If query <> "" Then qyery = query & " And " 
         query= query & "([Date] >= " & FromDate & ")"
     End If
     If IsTime(ToDate) Then
        If query <> "" Then qyery = query & " And " 
        query= query & "([Date] <= " & ToDate & ")"
     End If
 ' modified end
     Set collection = db.FTSearch( query , )          
     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

The SearchUser is for you...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JaziarAuthor Commented:
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)

0
JaziarAuthor Commented:
If IsTime(FromDate) Then

I thought IsTime is a @ Command?
0
Sjef BosmanGroupware ConsultantCommented:
Oooops... I'm ALWAYS mistaken with IsTime and IsDate... You're right, you need IsDate in LotusScript. Sorry :$
0
JaziarAuthor Commented:
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  )          
0
Sjef BosmanGroupware ConsultantCommented:
Typo, please, always use Option Declare in your code!

I accidentally put qyery instead of query. Sorry.
0
Sjef BosmanGroupware ConsultantCommented:
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"
0
Sjef BosmanGroupware ConsultantCommented:
Also, in the Designer Help db, "Customizing search forms"
0
JaziarAuthor Commented:
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

0
JaziarAuthor Commented:
I know understand the question above

db.FTSearch( query, 25  )    

It searches the entire database.
0
JaziarAuthor Commented:
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.
0
Sjef BosmanGroupware ConsultantCommented:
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.
0
JaziarAuthor Commented:
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.
0
Sjef BosmanGroupware ConsultantCommented:
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...
0
JaziarAuthor Commented:
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.
0
Sjef BosmanGroupware ConsultantCommented:
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")

0
JaziarAuthor Commented:
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?
   
0
Sjef BosmanGroupware ConsultantCommented:
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
0
Sjef BosmanGroupware ConsultantCommented:
So, in answer to your last question, I think the original code can handle that wordsearch is empty.
0
Sjef BosmanGroupware ConsultantCommented:
Hey thanks! It works??
0
JaziarAuthor Commented:
It dont have all the bells and whistles, but it works.  As always thanks for your help.
0
Sjef BosmanGroupware ConsultantCommented:
Great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.