Transform data from a secondary data source using XSLT
Use XSLT to transform the data stored within a secondary data source.
You have a SharePoint list that stores the first name, last name, and a calculated field as the full name, which is composed from the first and last name fields. When you add a data connection to this SharePoint list from within InfoPath, the calculated field for the full name does not appear in the list of fields you can select to use in the secondary data source, while you would still like to use this field as the display text in a drop-down list box that is bound to your secondary data source.
Add an extra non-calculated field to your SharePoint list that will show up in InfoPath and use an XSL transformation to produce the calculated value and populate the extra field of the secondary data source.
You can accomplish this functionality as follows:
- You have a SharePoint list called AtlantaBravesTeam with the columns LastName, FirstName,
and FullName, where FullName is a calculated field that uses the formula
- Add an extra field to the SharePoint list and call it ExtraFieldForInfoPath. This field will remain empty in SharePoint.
- Create a New Blank Form in InfoPath.
- Create a Data Connection to the SharePoint list, select the LastName, FirstName, and ExtraFieldForInfoPath to be included in the data connection, and call the data connection AtlantaBravesTeam. The XML data for the SharePoint list used in this solution resembles the following:
- Add a Table with Title to the InfoPath form.
- Add a Drop-Down List Box control to the table and bind it to the Data Connection for the SharePoint list. Use the LastName field as the Value and the ExtraFieldForInfoPath as the Display name.
- Add a Button control to the table and give it the Label Refresh List.
- Modify the table and controls to resemble the following figure:
Figure 1. The InfoPath form in design mode.
- Add the following XSLT stylesheet as a Data Connection
(select Receive data and XML document in the data connection wizard when creating the data connection) to the form and call it Transform:
Figure 2. XSLT used to transform data in the secondary data source.
This stylesheet uses the FirstName and LastName attributes of each dsf:AtlantaBravesTeam node in the secondary data source to generate the value for the ExtraFieldForInfoPath attribute:
<xsl:if test="@FirstName != ''">, </xsl:if>
- Go to Tools > Programming > On Load Event... to add an OnLoad
event handler to the form. Add the following code to the OnLoad event handler:
- Switch back to InfoPath, double-click on the Button control to open its Properties dialog box.
- Click on the Edit Form Code...
button to add an event handler for the button. Add the following code to the event handler for the button:
- Add the following JScript function that performs the XSL transformation and replacement of the
data in the secondary data source at the end of the script.js file:
// Load in the data from the secondary data source
var aTeamDOM = XDocument.GetDOM("AtlantaBravesTeam");
// Load in the stylesheet to perform the transformation
var xsltDoc = XDocument.GetDOM("Transform");
// Transform the data in the secondary data source
var transformedXml = aTeamDOM.transformNode(xsltDoc);
// Load the transformed data into a new XML document
var newATeamDOMDataFieldsDOM = XDocument.CreateDOM();
// Set the namespaces used in the aTeamDOM
// Retrieve the dfs:dataFields node of the aTeamDOM
var aTeamDOMDataFieldsNode = aTeamDOM.selectSingleNode("/dfs:myFields/dfs:dataFields");
// Replace the dfs:dataFields node of the sec. data source with the new dfs:dataFields
<dsf:AtlantaBravesTeam LastName="Cormier" FirstName="Lance" ExtraFieldForInfoPath=""/>
<dsf:AtlantaBravesTeam LastName="Francoeur" FirstName="Jeff" ExtraFieldForInfoPath=""/>
<dsf:AtlantaBravesTeam LastName="Giles" FirstName="Marcus" ExtraFieldForInfoPath=""/>
<dsf:AtlantaBravesTeam LastName="Jones" FirstName="Chipper" ExtraFieldForInfoPath=""/>
You should now have calculated values for the ExtraFieldForInfoPath field within the secondary data source showing up in your drop-down list box. These values were generated through the use of an XSL transformation of the original data from a secondary data source.
Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.
Related InfoPath Articles:
- Convert an InfoPath 2007 form into a Word 2007 document using XSLT and C#
- Convert an InfoPath form into a Word 2003 document and send this as an attachment in an e-mail
- Bulk export selected InfoPath forms to Word documents
- Move items between two InfoPath list boxes (no code)
- Update SharePoint tasks by looping through a repeating table on an InfoPath 2013 form