I figure out how to access a SharePoint list. I have several procedures and functions.
Basically what I am doing is reading a SharePoint list to get data to populate a dropdown list. The dropdown list points to a repeating table that is populated if there is a match in the primary and secondary fields of the list.
I hope this helps someone.
Public Sub AddTempData(ByVal xSDconnectionName As String, ByVal xSDpath As String, ByVal xValueToFind As String, ByVal xCategoryType As String)
' This procedure will read secondary data and find all matches.
' I searches a primary field and if a match it will search a secondary field for a match
' To get the value of the secondary field it will call the GetValueOfSecondaryData function.
' When yhere is a match of both primary and secondary fileds it will read values from two other fields
' write them to another repeating table. This table is used for a dropdown list.
' When calling this procedure you must pass the:
' 1. Secondary Data connection name. This is the name that you used from the
' Tools/Data Connection menu.
' 2. Sencodary Data Path
' 3. The value to find; this is the value to match in the secondary field
' 4. The Category Type; this is the value to match in the primary field
' AddTempData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList/@Category", "T1", "MRC")
' NOTE: the secondary data must use dfs: in stead of my: as you would in the main data section.
' NOTE: the field name but us the @ signe follwoed by the name of the field.
Dim rowNumber As Integer = 0 ' The row number of the Temp Data Repeating Table
Dim currentRow As Integer = 0 ' The current row that is being read
'Clear out Old data
Dim rowCount As Integer
Dim i As Integer
rowCount = GetRowCount("/my:QuotingToolFields/my:Wizard/my:TempData/my:DataTable")
If rowCount > 1 Then
For i = 1 To (rowCount - 1)
DeleteRow("/my:QuotingToolFields/my:Wizard/my:TempData/my:DataTable", "/my:QuotingToolFields/my:Wizard/my:TempData")
Next
End If
rowCount = 0
'Get navigator to the root of the secondary datasource.
Dim secondaryDataSource As DataSource = DataSources.Item(xSDconnectionName)
Dim secondaryDOMNavigator As XPathNavigator = secondaryDataSource.CreateNavigator
'Get all the list items from the secondary datasource.
Dim secondaryListItems As XPathNodeIterator = secondaryDOMNavigator.Select(xSDpath, NamespaceManager)
Do While secondaryListItems.MoveNext()
If secondaryListItems.Current.Value = xValueToFind Then
If GetValueOfSecondaryData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList[" & secondaryListItems.CurrentPosition & "]/@ChargeCategory") = xCategoryType Then
rowNumber = rowNumber + 1
If rowNumber > 1 Then
AddRow("/my:QuotingToolFields/my:Wizard/my:TempData/my:DataTable", "/my:QuotingToolFields/my:Wizard/my:TempData")
End If
SetFieldValue(GetValueOfSecondaryData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList[" & secondaryListItems.CurrentPosition & "]/@ID"), "/my:QuotingToolFields/my:Wizard/my:TempData/my:DataTable[" & rowNumber & "]/my:DataID")
SetFieldValue(GetValueOfSecondaryData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList[" & secondaryListItems.CurrentPosition & "]/@Item"), "/my:QuotingToolFields/my:Wizard/my:TempData/my:DataTable[" & rowNumber & "]/my:DataItem")
End If
End If
Loop
End Sub
Public Function GetValueOfSecondaryData(ByVal xSDconnectionName As String, ByVal xSDpath As String) As String
' This function will return the data for the row and field that is passwed to it.
' When calling this procedure you must pass the:
' 1. Secondary Data connection name. This is the name that you used from the
' Tools/Data Connection menu.
' 2. Sencodary Data Path
' GetValueOfSecondaryData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList[32]/@ChargeCategory")
' GetValueOfSecondaryData("PriceList", "/dfs:myFields/dfs:dataFields/dfs:PriceList[" & secondaryListItems.CurrentPosition & "]/@ChargeCategory")
'Get navigator to the root of the secondary datasource.
Dim secondaryDataSource As DataSource = DataSources.Item(xSDconnectionName)
Dim secondaryDOMNavigator As XPathNavigator = secondaryDataSource.CreateNavigator
'Get all the list items from the secondary datasource.
Dim secondaryListItems As XPathNodeIterator = secondaryDOMNavigator.Select(xSDpath, NamespaceManager)
secondaryListItems.MoveNext() ' need to get to the current node
Return secondaryListItems.Current.Value
End Function
Public Sub AddRow(ByVal xpathTable As String, ByVal xpathParent As String)
' This procedure will add a row to the end of a repeating table.
' When calling this procedure you must pass it the path to the table and the
' the path to the oarrent section of the table.
' AddRow("/my:myFields/my:TempData/my:Data2", "/my:myFields/my:TempData")
' NOTE: There may be an issue that the repeating table must be in it's own section.
' and there can NOT be any other section at the same level as the repeating
' table section
Dim row As Xml.XPath.XPathNavigator = Me.MainDataSource.CreateNavigator.SelectSingleNode(xpathTable, NamespaceManager)
Dim parent As Xml.XPath.XPathNavigator = Me.MainDataSource.CreateNavigator.SelectSingleNode(xpathParent, NamespaceManager)
parent.AppendChild(row)
End Sub
Public Sub DeleteRow(ByVal xpathTable As String, ByVal xpathParent As String)
' This procedure will delete the last row in a repeating table.
' When calling this procedure you must pass it the path to the table and the
' the path to the oarrent section of the table.
' DeleteRow("/my:myFields/my:TempData/my:Data2", "/my:myFields/my:TempData")
' NOTE: There may be an issue that the repeating table must be in it's own section.
' and there can NOT be any other section at the same level as the repeating
' table section
Dim row As Xml.XPath.XPathNavigator = Me.MainDataSource.CreateNavigator.SelectSingleNode(xpathTable, NamespaceManager)
Dim parent As Xml.XPath.XPathNavigator = Me.MainDataSource.CreateNavigator.SelectSingleNode(xpathParent, NamespaceManager)
parent.MoveTo(row)
parent.DeleteRange(row)
End Sub
Public Sub SetFieldValue(ByVal Value As String, ByVal xpath As String)
' This procedure allows you to det the value of a Data source.
' When calling this procedure you must pass it the value (to be set) and location of the data source.
' SetFieldValue("XXX", "my:QuotingToolFields/my:Offices/my:OfficeInfo/my:OfficeName")
' or you can read a spcific row in a repeating table:
' SetFieldValue("XXX", "my:QuotingToolFields/my:Offices/my:OfficeInfo[32]/my:OfficeName")
' or you can use a verable for the row:
' SetFieldValue("XXX", "my:QuotingToolFields/my:Offices/my:OfficeInfo[" & CurrentOffice & "]/my:OfficeName")
Dim root As Xml.XPath.XPathNavigator = Me.MainDataSource().CreateNavigator()
Dim field As Xml.XPath.XPathNavigator = root.SelectSingleNode(xpath, Me.NamespaceManager())
field.SetValue(Value)
End Sub