Transform data from a secondary data source using XSLT

Use XSLT to transform the data stored within a secondary data source.

ADVERTISEMENTS

Problem

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.

Solution

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.

Discussion

You can accomplish this functionality as follows:

  1. You have a SharePoint list called AtlantaBravesTeam with the columns LastName, FirstName, and FullName, where FullName is a calculated field that uses the formula
    =IF(FirstName<>"",CONCATENATE(LastName,", ",FirstName),LastName)
  2. Add an extra field to the SharePoint list and call it ExtraFieldForInfoPath. This field will remain empty in SharePoint.
  3. Create a New Blank Form in InfoPath.
  4. 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.
  5. The XML data for the SharePoint list used in this solution resembles the following:
    <dfs:myFields>
    <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=""/>
    </dfs:dataFields>
    </dfs:myFields>
    Note: Namespace declarations have been left out to increase clarity.
  6. Add a Table with Title to the InfoPath form.
  7. 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.
  8. Add a Button control to the table and give it the Label Refresh List.
  9. Modify the table and controls to resemble the following figure:

    Figure 1. The InfoPath form in design mode.
  10. 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:attribute name="ExtraFieldForInfoPath">
    <xsl:value-of select="@LastName"/>
    <xsl:if test="@FirstName != ''">, </xsl:if>
    <xsl:value-of select="@FirstName"/>
    </xsl:attribute>
  11. 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:
    transformATeamList();
    This code ensures that whenever the form is opened, the data in the secondary data source will be transformed to the format specified by the stylesheet.
  12. Switch back to InfoPath, double-click on the Button control to open its Properties dialog box.
  13. 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:
    XDocument.DataObjects["AtlantaBravesTeam"].Query();
    transformATeamList();
    where AtlantaBravesTeam is the name of the data connection to the SharePoint list. The first line in this code refreshes the data stored within the secondary data source (just in case items were added, edited, or deleted from within SharePoint), while the second line transforms the data in the secondary data source to the format specified by the stylesheet.
  14. 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:
    function transformATeamList()
    {
    // 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();
    newATeamDOMDataFieldsDOM.loadXML(transformedXml);

    // Set the namespaces used in the aTeamDOM
    aTeamDOM.setProperty("SelectionNamespaces",
    'xmlns:dfs=
    "http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
    xmlns:dsf=
    "http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
    ');

    // 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
    aTeamDOMDataFieldsNode.parentNode.replaceChild(newATeamDOMDataFieldsDOM.documentElement, aTeamDOMDataFieldsNode);
    }

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.

 
 Subscribe for updates via RSS or Email

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.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services