Cascading Dropdown List using VBScript, XPATH and an XML back-end

Posted on by
Reply

I’ve been working on more toolsets recently, and we needed a way to populate multiple dropdown lists in a HTA file, and make them cascade. Cascading is basically where the results in the second dropdown list are dictated by the selection in the first dropdown list. For example:

Let’s say our first dropdown list contained car manufacturers, and the second dropdown list contained models. If I selected ‘Ford’ from the manufacturers dropdown list, I would want the model dropdown list to show me a list of all cars made by Ford (Fiesta, Mondeo, Escort etc etc) and so on.

Anyway, in our example we’ll use a good ol’ list of football clubs.  In dropdown list 1, we want a list of football clubs.  And in dropdown list 2, we want to see a list of players.  We also want to keep track of the stadium that each clubs plays in.  Here’s our sample XML chunk – we’ll call it ‘footballclubs.xml’:

<?xml version="1.0"?>
<PREMIERLEAGUEFOOTBALLCLUBS>
	<FOOTBALLCLUBS>
		<FOOTBALLCLUB>
			<FOOTBALLCLUBNAME>Manchester United FC</FOOTBALLCLUBNAME>
			<FOOTBALLCLUBSTADIUM>Old Trafford</FOOTBALLCLUBSTADIUM>
			<PLAYERS>				
				<PLAYER>
					<PLAYERNAME>Rio Ferdinand</PLAYERNAME>
				</PLAYER>		
				<PLAYER>
					<PLAYERNAME>Nemanja Vidic</PLAYERNAME>
				</PLAYER>				
			</PLAYERS>
		</FOOTBALLCLUB>
		<FOOTBALLCLUB>
			<FOOTBALLCLUBNAME>Chelsea FC</FOOTBALLCLUBNAME>
			<FOOTBALLCLUBSTADIUM>Stamford Bridge</FOOTBALLCLUBSTADIUM>
			<PLAYERS>				
				<PLAYER>
					<PLAYERNAME>Frank Lampard</PLAYERNAME>
				</PLAYER>				
			</PLAYERS>
		</FOOTBALLCLUB>
		<FOOTBALLCLUB>
			<FOOTBALLCLUBNAME>Arsenal FC</FOOTBALLCLUBNAME>
			<FOOTBALLCLUBSTADIUM>Emirates Stadium</FOOTBALLCLUBSTADIUM>
			<PLAYERS>				
				<PLAYER>
					<PLAYERNAME>Keiron Gibbs</PLAYERNAME>
				</PLAYER>		
				<PLAYER>
					<PLAYERNAME>Jack Wilshere</PLAYERNAME>
				</PLAYER>
				<PLAYER>
					<PLAYERNAME>Aaron Ramsey</PLAYERNAME>
				</PLAYER>				
			</PLAYERS>
		</FOOTBALLCLUB>		
	</FOOTBALLCLUBS>
</PREMIERLEAGUEFOOTBALLCLUBS>

You can see that the football clubs are: Manchester Utd, Chelsea and Arsenal. You should also be able to see the players for each club, and the stadium they play in.

Now we’ll write a HTA file, and save it in the same location as ”footballclubs.xml’:

<html>
<head>
<hta:application
 id="oHTA"
 applicationname="XML Cascading Drop Down"
 singleinstance="yes"
 windowstate="normal"
 border="no"
 maximize="no"
 caption="XML Cascading Drop Down"
 icon="alkane.ico"
 showintaskbar="yes"
 sysmenu="yes"> 
<title>XML Cascading Drop Down 1.0.0</title>
</head>

<script language="vbscript">

' *************
'
' Version 1.0.0
'
'**************

Option Explicit

Dim strXMLFile

Sub InitialWindow

	Dim intHorizontal, intVertical, intLeft, intTop, objItem	

	Dim menu_width : menu_width = "450"
	Dim menu_height : menu_height = "420"	

	' This moves the window to the middle of the screen
	Dim objWMIService : Set objWMIService = GetObject("winmgmts:\\.\root\cimv2") 
	Dim colItems : Set colItems = objWMIService.ExecQuery("Select * From Win32_DesktopMonitor WHERE DeviceID='DesktopMonitor1'") 

	For Each objItem in ColItems 
		intHorizontal = objItem.ScreenWidth 
		intVertical = objItem.ScreenHeight 
	Next 

	intLeft = (intHorizontal - menu_width)/2 
	intTop = (intVertical - menu_height)/2 
	window.resizeTo menu_width,menu_height 
	window.moveTo intLeft, intTop

	strXMLFile = FindCurrentDir & "footballclubs.xml"		

	Call PopulateDropdownLists

End Sub

Function FindCurrentDir

	Dim objShell : Set objShell = CreateObject("WScript.Shell")
	FindCurrentDir = Left(document.location.pathname,InStrRev(document.location.pathname,"\"))

End Function

Sub PopulateDropdownLists

	Dim objOption
	Dim strQuery, colItem, objItem

	Const intForReading = 1

	Dim xmlDoc : Set xmlDoc = CreateObject( "Microsoft.XMLDOM" )
	xmlDoc.Async = False

	If xmlDoc.Load(strXMLFile) Then

		'populate club names

		Set objOption = Document.createElement("OPTION")
		objOption.Text = "Select Club"
		objOption.value = "Select Club"
		ClubSelector.add(objOption)

		strQuery = "/PREMIERLEAGUEFOOTBALLCLUBS/FOOTBALLCLUBS/FOOTBALLCLUB/FOOTBALLCLUBNAME"	
		Set colItem = xmlDoc.selectNodes(strQuery)

		For Each objItem in colItem	
			Set objOption = Document.createElement("OPTION")
			objOption.Text = objItem.text
			objOption.value = objItem.text
			ClubSelector.add(objOption)		
		Next

		Set objOption = Document.createElement("OPTION")
		objOption.Text = "Select Player"
		objOption.value = "Select Player"
		PlayerSelector.add(objOption)	

	End If

	Set xmlDoc = Nothing

End Sub

Sub populateClubs()

	Dim footballclubname : footballclubname = ClubSelector.options(ClubSelector.SelectedIndex).value
	Dim objOption

	For Each objOption in PlayerSelector.Options
		objOption.RemoveNode
	Next 	

	Dim xmlDoc : Set xmlDoc = CreateObject("Microsoft.XMLDOM")
	xmlDoc.Async = False

	Set objOption = Document.createElement("OPTION")
	objOption.Text = "Select Player"
	objOption.value = "Select Player"
	PlayerSelector.add(objOption)

	If xmlDoc.Load(strXMLFile) Then

		dim strQuery : strQuery = "/PREMIERLEAGUEFOOTBALLCLUBS/FOOTBALLCLUBS/FOOTBALLCLUB [ FOOTBALLCLUBNAME = '" & footballclubname & "' ] /PLAYERS/PLAYER/PLAYERNAME"
		dim colItem : Set colItem = xmlDoc.selectNodes(strQuery)
		dim objItem

		For Each objItem in colItem
			Set objOption = Document.createElement("OPTION")
			objOption.Text = objItem.text
			objOption.value = objItem.text
			PlayerSelector.add(objOption)	
		Next
	End If

	Set xmlDoc = Nothing		

End Sub

Function getXMLValues(footballclubname,tag)

	Dim xmlDoc : Set xmlDoc = CreateObject( "Microsoft.XMLDOM" )
	xmlDoc.Async = False

	If xmlDoc.Load(strXMLFile) Then
		dim strQuery : strQuery = "/PREMIERLEAGUEFOOTBALLCLUBS/FOOTBALLCLUBS/FOOTBALLCLUB [ FOOTBALLCLUBNAME = '" & footballclubname & "' ] /" & tag & ""
		dim colItem : Set colItem = xmlDoc.selectNodes(strQuery)
		dim objItem

		For Each objItem in colItem
			getXMLValues = objItem.text
		Next
	End If

	Set xmlDoc = Nothing	

End Function

Sub displayStadium

	Dim footballclubname : footballclubname = ClubSelector.options(ClubSelector.SelectedIndex).value	
	Dim stadiumname : stadiumname = getXMLValues(footballclubname,"FOOTBALLCLUBSTADIUM")	
	MsgBox stadiumname

End Sub

</script>

<body scroll="no" onload="InitialWindow" style="text-align:center;font-family:Arial;font-size:12px;">

	<br /><br />
	<select size="1" id="ClubSelector" onChange="populateClubs()"></select><br /><br />
	<select size="1" id="PlayerSelector"></select><br /><br />

	<input type="button" value="Display Stadium" name="printSelection"  onClick="displayStadium()" />

</div>

</body>
</html>

 

Using VBScript, CAML queries and SOAP to Read from a Sharepoint List

Posted on by
4

We use a Sharepoint list to track various information. The toolsets that we create should ideally read from these lists, so that every team member shares the same information and can see real-time data. To do this, we used VBScript (we made a HTA actually…).

It uses a SOAP request to ‘ask’ for information from Sharepoint. Part of this request uses a CAML query to specify exactly what we’re after.

Option Explicit

Dim url, list, viewFields, request, xmlDoc, elements, colItem, objItem, queryNode, clientname, resultsArray, xmlArray(), strItem, spclientname

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.async = False

url = "http://xxxx/_vti_bin/Lists.asmx"

'search in client list for 'Multi Client', where LinkTitle equals 'Multi Client', and bring me back the target platform
resultsArray = getResults("331609D1-793D-4075-BC88-570956C6D729","LinkTitle","Multi Client","ows_Target_x0020_Platform")

'do what we want with the clients here
For Each strItem in resultsArray
    Wscript.Echo strItem
Next

Function getResults(splist, spwhere, spvalue, spreturnattribute)	

	request = "<?xml version='1.0' encoding='utf-8'?>" & _
	"<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" & _
	"  <soap:Body>" & _
	"    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" & _
	"      <listName>" & splist & "</listName>" + _
	"      <query><Query xmlns=''>" & _
	"      <Where>" & _
	"      <Eq>" & _
	"      <FieldRef Name='" & spwhere & "'/>" & _
	"      <Value Type='Text'>" & spvalue & "</Value>" & _
	"      </Eq>" & _
	"      </Where>" & _
	"      </Query>" & _
	"      </query>" & _
	"    </GetListItems>" & _
	"  </soap:Body>" & _
	"</soap:Envelope>"

	'post it up and look at the response
	with CreateObject("Microsoft.XMLHTTP")
		.open "Get", url, False, null, null
		.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
		.setRequestHeader "SOAPAction","http://schemas.microsoft.com/sharepoint/soap/GetListItems"
		.send request

		xmlDoc.setProperty "SelectionLanguage", "XPath"
		xmlDoc.async = false
		xmlDoc.validateOnParse = false
		xmlDoc.resolveExternals = false
		xmlDoc.setProperty "SelectionNamespaces", "xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:namespace='http://schemas.microsoft.com/sharepoint/soap/' xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'"

		xmlDoc.loadXML(.responseText)

		'dim strQuery : strQuery = "//soap:Envelope/soap:Body/namespace:GetListItemsResponse/namespace:GetListItemsResult/namespace:listitems/rs:data/z:row"
		dim strQuery : strQuery = ".//z:row"

		Set colItem = xmlDoc.selectNodes(strQuery)

		Dim objItemCount : objItemCount = 0
		'clear array
		ReDim xmlArray(objItemCount)

		'wscript.echo "No of Items: " & colItem.Length
		For Each objItem in colItem		
			ReDim Preserve xmlArray(objItemCount)
			xmlArray(objItemCount) = objItem.getAttribute(spreturnattribute)			
			objItemCount = objItemCount + 1
		Next
		Set colItem = Nothing

		'we could use this if we didnt filter it during the request	
		'Set queryNode = xmlDoc.selectSingleNode(".//z:row[@ows_LinkTitle = '" & clientname & "']")
		'wscript.echo queryNode.getAttribute("ows_Target_x0020_Platform")
		'Set queryNode = Nothing

		Set xmlDoc = Nothing

		getResults = xmlArray	

	end with

End Function

**UPDATE**

The example above pulls back ALL fields from the list.  We may want to refine this to only pull back specific fields by adding the following to our SOAP request:

    " 	   <viewFields><ViewFields><FieldRef Name='COMPANYNAME_x0020_Property' /><FieldRef Name='USERNAME_x0020_Property' /></ViewFields></viewFields>" + _
    "    <queryOptions>" + _
    " 	       <QueryOptions>" + _
    " 	           <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" + _
    " 	           <ViewFieldsOnly>TRUE</ViewFieldsOnly>" + _
    " 	       </QueryOptions>" + _
    " 	   </queryOptions>" + _

The first viewFields tag above should be at the same level as the <query> tag in the example above.  In other words, you can paste this code directly above the <query> line in the SOAP request.