[Back to "Keyword magic for the Web, Part 2"]

CustSearch agent code
Here is the complete LotusScript code for the CustSearch agent.

Sub Initialize

Dim s As New NotesSession
Dim db As NotesDatabase
Dim note As NotesDocument
Dim custNote As NotesDocument
Dim coll As NotesDocumentCollection
Set db = s.CurrentDatabase
Set note = s.DocumentContext
'get the customer name from the querystring
qs = note.Query_String_Decoded(0)
pname = Strright(qs, "=" ) 'get the string to the right of the equal sign
query = "[CustName] Like " & pname
Set coll = db.Ftsearch(query, 0, FT_SCORES, FT_FUZZY + FT_STEMS)
Set custNote = coll.GetFirstDocument
Print "<script>"
'delete current options in the keyword list
Print | oldcustList = parent.document.all("CustList").options;|
Print | for(x=oldcustList.length-1; x>=0; x--){|
Print | oldcustList.options[x] = null;;|
Print | }|
'Now add the list of the customers returned from the ftsearch
Do While Not(custNote Is Nothing)
custName = custNote.CustName(0)
'add the new option
Print | var oOption=parent.document.createElement("OPTION");|
Print | oldcustList.options.add(oOption);|
Print | oOption.innerText="| & custName & |";|
Print | oOption.value="| & custName & |";|
'get the next customer from the search results
Set custNote = coll.GetNextDocument( custNote)
Loop
'close the javascript tag
Print "</script>"

End Sub

RDBCustSearch agent code
Here is the complete LotusScript code for the RDBCustSearch agent.

Sub Initialize

Dim s As New NotesSession
Dim db As NotesDatabase
Dim note As NotesDocument
Dim custNote As NotesDocument
Dim coll As NotesDocumentCollection
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim res As ODBCResultSet
Dim dataSource As String
Dim userName As String
Dim password As String
Dim SQLStmt As String

Set db = s.CurrentDatabase
Set note = s.DocumentContext
'get the customer name from the querystring
qs = note.Query_String_Decoded(0)
pname = Strright(qs, "=" ) 'get the string to the right of the equal sign

Set con = New ODBCConnection
Set qry = New ODBCQuery
Set res = New ODBCResultSet
dataSource = "CUSTDB"
userName = "MPOLLY"
password = "test"

If Not con.ConnectTo(dataSource, userName, password) Then
Messagebox "Could not connect to " & dataSource
Else
Set qry.Connection = con
End If

SQLStmt = "SELECT LTRIM(TRANSLATE(B.CUST_NM, '', '*')) AS CUST_NAME" &_
"FROM CUSTDB.EDW00T.VRPM_CUST_DIM B " &_
"WHERE B.CUST_NM LIKE '*" & pname & "%' OR B.CUST_NM LIKE '" & pname & "%' " &_
"ORDER BY CUST_NAME"

qry.SQL = SQLStmt
Set res.Query = qry
res.Execute
If Not(res.IsResultSetAvailable) Then ' No matches were found exit
Exit Sub
End If

Print "<script>"
'delete current options in the keyword list
Print | oldcustList = parent.document.all("CustList").options;|
Print | for(x=oldcustList.length-1; x>=0; x--){|
Print | oldcustList.options[x] = null;;|
Print | }|
'Now add the list of the customers returned from the ftsearch
Do
res.NextRow
custName = res.GetValue(Cust_Name)
'add the new option
Print | var oOption=parent.document.createElement("OPTION");|
Print | oldcustList.options.add(oOption);|
Print | oOption.innerText="| & custName & |";|
Print | oOption.value="| & custName & |";|
Loop Until res.IsEndOfData
'close the javascript tag
Print "</script>"

End Sub