Redirect A Connection, Feed A Parent and Child
In this scenario, we are utilizing a Workplace Parent Child field, which gives us “dependent dropdowns”, meaning the child values are dependent upon what is selected in the parent dropdown. With this, we want to retrieve our data from a SQL Server database. Our data will be driven depending on the SharePoint site that we are wanting information about. For example, we may have three sites: IT, HR and Accounting, and we want to retrieve a list of tasks in our child dropdown that are based on upon the projects in our parent dropdown, but to start the data rolling, we need to pass the name of the site to our SQL database to get the appropriate projects and tasks.
We accomplish our mission, by first retrieving the Site title utilizing the SharePoint SiteData Web service, then we’ll redirect our connection to take our Site title and pass it in as a parameter to our SQL Server connection. How do we accomplish this? We’ll utilize the External Data Provider and the Workplace Parent Child field. First, let’s set up our Connection file for our EDP. It will look like this:
<?xml version="1.0" encoding="utf-8" ?>
<CorasWorks>
<Data>
<Name>GetSiteTitle</Name>
<Default>true</Default>
<ConnectionType>Web Service</ConnectionType>
<Request>%URLToSearchFor%/_vti_bin/SiteData.asmx</Request>
<RDConnection><%SiteURL%>/Providers/SiteDP.aspx?ConnectionName=SelectProjects</RDConnection>
<RDConnectionPost>
<PassThrough>
<Item>
<PTType>post</PTType>
<PTName>SiteTitle</PTName>
<PTPath>data/site/title</PTPath>
</Item>
</PassThrough>
</RDConnectionPost>
<SOAP>
<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>
<GetWeb xmlns="http://schemas.microsoft.com/sharepoint/soap/" />
</soap:Body>
</soap:Envelope>
</SOAP>
<SOAPAction>http://schemas.microsoft.com/sharepoint/soap/GetWeb</SOAPAction>
<XslStylesheetLocation><%SiteURL%>/Providers/SiteTransform.xsl</XslStylesheetLocation>
<UseCurrentUserCredentials>2</UseCurrentUserCredentials>
<UserName>%CWUserID%</UserName>
<Password>%CWPassword%</Password>
<OutputType>text/xml</OutputType>
<Values>
<URLToSearchFor/>
</Values>
</Data>
<Data>
<Name>SelectProjects</Name>
<Default>false</Default>
<ConnectionType>ADO</ConnectionType>
<ConnectionString>Provider=SQLOLEDB;Data Source=1.2.3.4;Initial Catalog=MyDatabase;Integrated Security=SSPI;</ConnectionString>
<Query>Select ProjectName From dbo.Projects Where ProjectSite = '%SiteTitle%'</Query>
<Values>
<SiteTitle />
</Values>
<UseCurrentUserCredentials>1</UseCurrentUserCredentials>
</Data>
<Data>
<Name>SelectTasks</Name>
<Default>false</Default>
<ConnectionType>ADO</ConnectionType>
<ConnectionString>Provider=SQLOLEDB;Data Source=1.2.3.4;Initial Catalog=MyDatabase;Integrated Security=SSPI;</ConnectionString>
<Query>Select Tasks From dbo.Tasks Where ProjectName = '%ProjectName%'</Query>
<Values>
<ProjectName />
</Values>
</Data>
</CorasWorks>
Our first connection, GetSiteTitle is our default connection and will execute when the EDP is called. The connection expects the parameter URLToSearchFor, so you can specify where the site is in your EDP call that you want to point to. Using the <RDConnection> properties, we specify that we want to redirect our connection back through our EDP and calling our second connection, SelectProjects, which populates our parent dropdown. Through the <PassThrough> properties, we pass the Site title on to the next connection. In order to get our Site title XML data in a format for our next connection, we strip out the Site title returned from the web service using our XSLT transformation. This file looks like:
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:ddw1="http://schemas.microsoft.com/sharepoint/soap/" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt"
xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" >
<xsl:output method="xml" omit-xml-declaration="yes" />
<xsl:template match="/">
<data><site><title><xsl:value-of select="/soap:Envelope/soap:Body/ddw1:GetWebResponse/ddw1:sWebMetadata/ddw1:Title" /></title></site></data>
</xsl:template>
</xsl:stylesheet>
Now that we have our Site title retrieved, and redirecting to our SQL Server connection, we need to setup the Workplace Parent Child Field to use our connection.
We’ll create a new Workplace Parent Child field in our library and configure our “Field Configuration” property to look like this:
<FieldControls>
<Field>
<Name>Projects</Name>
<XMLToURL><%SiteURL%>/Providers/SiteDP.aspx?URLToSearchFor=http://myportal.mycompany.net/IT</XMLToURL>
<DisplayValue>:xpath=NewDataSet/Data/ProjectName</DisplayValue>
<ValueMember>:xpath=NewDataSet/Data/ProjectName</ValueMember>
<Parent />
<DefaultText>Select a Project</DefaultText>
<UpdateFieldName>Project</UpdateFieldName>
</Field>
<Field>
<Name>Tasks</Name>
<AutoSelectFirstValue>true</AutoSelectFirstValue>
<XMLToURL><%SiteURL%>/Providers/SiteDP.aspx?ConnectionName=SelectTasks&ProjectName=%ParentValue%</XMLToURL>
<DisplayValue>:xpath=NewDataSet/Data/Task</DisplayValue>
<ValueMember>:xpath=NewDataSet/Data/Task</ValueMember>
<DefaultText>Select a Task</DefaultText>
<Parent>Projects</Parent>
</Field>
</FieldControls>
In the first <Field> node, we define the parent, call our EDP passing in our URLToSearchFor parameter. Of note, the <UpdateFieldName> node is specifying another column that we created, a single line of text named Project, so that when the values are saved, the value of the selected Project will be saved to that field. Our second <Field> node is setting up our child dropdown, which calls our EDP, connecting to our third connection and passing in the value of the parent (%ParentValue%).
Now that we have our pieces wired up, when a new item to our list or in the image below, edit an item, we can see that our dependent list boxes are populated based on the site we chose (for the example, IT) and the project and tasks accordingly.
So we have successfully configured our solution to use the SharePoint web service to start our data gathering and populate our dependent dropdowns utilizing the External Data Provider and Workplace Parent Child field of the Data Integration Toolset!