Friday, September 28, 2012

Merge two lists using a DataView web part

For a survey I created, I needed to merge some user information (e.g. department, Division, etc.) from another list into the survey results, and get those results to display nicely in Excel. A straight DataView data source join got me 80% of the way there, but it was far from ideal, throwing the results of the joined table into a single column at the end of the first table.  I wanted to go one step further and make the html  and the look and feel be ONE table.  So I did the following:

1.  Created a new page and added a DataView web part using SharePoint Designer. 

2.  Followed this article to join my data sources and add all of the fields I wanted:   http://office.microsoft.com/en-us/sharepoint-designer-help/display-data-from-multiple-sources-in-a-single-data-view-HA010099144.aspx 

3.  For each column, move the column titles that get repeated in the joined dataview.  These are held in the dvt_2, 3, 4, etc. templates. 

         <th class="ms-vh" nowrap="nowrap">Department</th>

Paste the columns into the dvt_1 template along with the first table headers: 

           <th class="ms-vh mpi">Department</th>

(I removed the nowrap css since they were survey questions and quite long)

4.  For each column you added, add another column in the dvt_1.rowview template:  
            <td class="ms-vb">
                <xsl:call-template name="dvt_3" />              
            </td>
            .....
           <td class="ms-vb">
                <xsl:call-template name="dvt_9" />                                                                   
            </td>

5.    Immediately after the dvt_2.empty template, copy all the dvt_2 templates and paste it, then change all of the _2 to _3.  Should look like:

<xsl:variable name="dvt_3_automode">0</xsl:variable>
    <xsl:template name="dvt_3">
        <xsl:variable name="dvt_StyleName">Table</xsl:variable>
        <xsl:variable name="dvt_ParentRow" select="current()" />
        <xsl:variable name="Rows" select="../../../Hiring_Manager_Survey/Rows/Row[@Author.title=$dvt_ParentRow/@Title]" />
        <xsl:variable name="dvt_RowCount" select="count($Rows)" />
        <xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0" />
        <xsl:choose>
            <xsl:when test="$dvt_IsEmpty">
                <xsl:call-template name="dvt_3.empty" />
            </xsl:when>
            <xsl:otherwise>
                <table border="0" width="100%" cellpadding="2" cellspacing="0">
                    <xsl:call-template name="dvt_3.body">
                        <xsl:with-param name="Rows" select="$Rows" />
                        <xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
                    </xsl:call-template>
                </table>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>
    <xsl:template name="dvt_3.body">
        <xsl:param name="Rows" />
        <xsl:param name="dvt_ParentRow" />
        <xsl:for-each select="$Rows">
            <xsl:call-template name="dvt_3.rowview" />
        </xsl:for-each>
    </xsl:template>
    <xsl:template name="dvt_3.rowview">
        <tr>
            <xsl:attribute name="class">ms-alternating</xsl:attribute>

            <xsl:if test="$dvt_3_automode = '1'" ddwrt:cf_ignore="1">
                <td class="ms-vb" width="1%" nowrap="nowrap">
                    <span ddwrt:amkeyfield="" ddwrt:amkeyvalue="string($XPath)" ddwrt:ammode="view"></span>
                </td>
            </xsl:if>
            <td class="ms-vb mpi">
                <xsl:value-of select="@On_x0020_a_x0020_scale_x0020_of_2" />
            </td>
           
        </tr></xsl:template>
    <xsl:template name="dvt_3.empty">
    </xsl:template>

6.  Change the <xsl:value-of select="@On_x0020_a_x0020_scale_x0020_of_2" /> to the Internal Name of the field that you want displayed.

7.  Repeat steps 5 and 6 for each column you added.

The end result will be a merged dataview showing the joined results from two separate lists, that users can view or right-click to easily export to Excel:



2 comments:

Isa Guha said...

great info to read.

Best Dentist said...

As a Newbie, I am always browsing online for articles that can help me. Thank you, Your method of explaining the whole thing in this post is in fact pleasant.

Funeral homes website design and marketing solutions.