Sample Three: URL Title Search

' SBM AppScript Example: SearchTitle.tsc

'
' Perform a search specified by the URL,
' display hyperlinked list of resulting items.
'
' Requirements:
' This script is designed for URL context, in which the user enters a URL
' specifying the desired search. Note that a user-friendly HTML form could
' generate the URL from a user-supplied search string.
'
' Sample URL for searching for issues with "color" in the title:
'   "http://...tmtrack.dll?ScriptPage&scriptName=SearchTitle&find=color"
'   (where "..." is replaced by your system's path to the
'   SBM web server DLL)
' Require all variables to be declared before use
Option Explicit
' MODIFY THESE CONSTANTS FOR YOUR DATABASE
' ----------------------------------------
const TBL_NAME = "Issues"   ' Display name of the Primary table to search
const COL_NAME = "TS_TITLE"  ' Database name of Title field in Primary table
' Declare all variables
' Several subroutines below will use these global variables
Dim findMe, tableID, itemList, sqlWhereClause, QUOTE
QUOTE = Chr( 34 )  ' only way to get a quote in VBScript
If Shell.Context = "URL" Then
  ' Get params
  findMe = Shell.Params.Item( "find" )
  tableID = Ext.TableId( TBL_NAME, "display" )
  ' Build the HTML page
  openHTML
  If getItemList() Then
    resultHTML
  Else
    errorHTML
  End If
  closeHTML
Else
  ' They are running this in the wrong context (e.g., post-transition)
  Call Ext.LogErrorMsg( _
    "SBM AppScript error: this script must run in URL context." )
End If
' Use SQL to find findMe in title column of Issues table
' Resulting list goes into global itemList
' Return True for successful SQL operation, False for failure
Function getItemList()
  Set itemList = Ext.CreateAppRecordList( tableID )
  sqlWhereClause = COL_NAME & " like '%" & findMe & "%'"
  getItemList = itemList.ReadWithWhere( sqlWhereClause )
End Function
' Generate HTML for entire body of result page
Sub resultHTML
  Dim s, numItems, itemWord, item
  numItems = itemList.Length()
  If numItems = 1 Then
    itemWord = "item"
  Else
    itemWord = "items"
  End If
  ' Heading shows search string and # items found
  s = "<h1>Search Results</h1>" _
    & "<p>Found " & numItems & " " & itemWord & " containing <b>" _
    & findMe & "</b></p>"
  Call Ext.WriteStream( s )
  ' HTML "definition list" (<dl>) containing an entry for each found item
  Call Ext.WriteStream( "<dl>" )
  For Each item in itemList
    itemHTML item
  Next
  Call Ext.WriteStream( "</dl>" )
End Sub
' Generate HTML for one item's list entry
Sub itemHTML( item )
  ' Convert to a var record item so we can get display ID
  ' (GetDisplayIssueId() is a method of VarRecord, not AppRecord)
  Dim varrec
  Set varrec = Ext.CreateVarRecord( tableID )
  Call varrec.Read( item.GetId())
  ' Generate <dt> element as the title of this list entry
  Dim s
  s = "<dt><a href='tmtrack.dll?View&T=" & tableID & "&I=" _
    & varrec.GetId() & "' target='srch_detail'>" _
    & varrec.GetDisplayIssueId() & "</a></dt>"
  Call Ext.WriteStream( s )
  ' Generate <dd> element for each field to be listed for this entry
  textFldHTML varrec, "Title"
  selFldHTML varrec, "State"
  textFldHTML varrec, "Description"
  Call Ext.WriteStream( "<p></p>" )
End Sub
' Generate HTML for a text field's name and value
Sub textFldHTML( item, fldName )
  Dim s, fldVal
  If Not item.GetFieldValue( fldName, fldVal ) Then fldVal = "[none]"
  s = "<dd>" & fldName & ": " & fldVal & "</dd>"
  Call Ext.WriteStream( s )
End Sub
' Generate HTML for a selection field's name and value
' (item.GetFieldValue() yields the index, not the string value)
Sub selFldHTML( item, fldName )
  Dim s, fldList, fldObj, fldVal
  Set fldList = item.Fields()
  Set fldObj = fldList.FindField( fldName )
  If fldObj Is Nothing Then
    fldVal = "[none]"
  Else
    Call fldObj.GetDisplayValue( fldVal )
  End If
  s = "<dd>" & fldName & ": " & fldVal & "</dd>"
  Call Ext.WriteStream( s )
End Sub
' Generate HTML for an error page (search did not execute)
Sub errorHTML
  Dim s
  s = "<h1>SQL Error</h1>" _
    & "<p>Unable to search for <b>" & findMe & "</b></p>" _
    & "<p>SQL &quot;where&quot; clause:<br><b>" _
    & sqlWhereClause & "</b></p>"
  Call Ext.WriteStream( s )
End Sub
' Start the HTML, take care of entire <head>, open the <body>
Sub openHTML
  Dim s
  s = "Title Search for " & QUOTE & findMe & QUOTE & " in " _
    & TBL_NAME & " table"
  Call Ext.WriteStream( "<html><head><title>" )
  Call Ext.WriteStream( s )
  Call Ext.WriteStream( "</title></head><body>" )
End Sub
' End the <body> and the page
Sub closeHTML
  Call Ext.WriteStream( "</body></html>" )
End Sub