Sample Three: URL Title Search

This sample script searches for specific words in the Title field of all items the user has privileges to view within the Issues table. This sample can only be used in the URL context. The URL Title Search Results dialog box displays the results of this script.

image

Note: To use this script, you must add it to SBM using the Scripts editor in SBM Composer.

Title Search Script Contents

' 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