Joined Subviews with Linked Datasources in SharePoint 2010

I recently completed a project that involved joining a list (Appendix A) and a document library (Appendix A documents). Each document, when added to the library, was associated with an item from the list. There were three document types and each item in Appendix A needed to have 3 matching documents in the library. The project administrators needed a quick way to scan for missing documents.

The obvious solution was to put a data view web part on a page with a linked datasource and a joined subview (displaying all the documents associated with each list item).

I was going for something like this:

A report showing items from the list and their associated documents

A report showing items from the list and their associated documents

I figured it should be fairly easy, considering I already had a lookup column on the Appendix A Documents library, linking it to the list.

But, you know, this is SharePoint. It’s never quite THAT straightforward, right? Look what happens when you try to do a joined subview on your lookup column:

The look up column adds extra information - so no match!

The look up column adds extra information - so no match!

The lookup column adds extra information – notable the ID of the item, followed by a semi-colon and a # sign.

Now, if you want to be an XSLT ninja and do some string manipulation, you can get around this issue the hard way. But, the easy way is to just go back to your original lookup column, and use the new SharePoint 2010 feature to “Add a column to show each of these additional fields.” Just make sure that whichever column you’re adding and joining is unique. I used the ID in this example, as that is always unique.

Include A Second Unique Column

Include A Second Unique Column

Now, go back to your joined subview and select your additional column. Voila!

Correctly Linked Columns

Correctly Linked Columns

Advertisements
Leave a comment

20 Comments

  1. Alan Andrews

     /  March 19, 2013

    Be fine if it did that but it doesn’t ;-/ the link ID produces some crappy formed string <a onclick=" etc .. another typical SharePoint Feature .. its a PIA
    Been trying to solve this forever now. WHAT SHOULD BE SIMPLE IS NOT

    Reply
  2. Alan Andrews

     /  March 19, 2013

    Did you solve it? Thanks!

    Reply
    • Well, I figured out a way to link the joined subview on the ID (and directions to do that are in the blog post). I’m not sure what it is that you’re asking about. Once you have the linked subview in the dataview webpart you can format the data anyway you want….

      Reply
      • Alan Andrews

         /  March 19, 2013

        Thanks your instructions were perfect per say. BUT I when I get my ID field from the lookup the ID is not a single number it displays like 10;#Project Planning therefore without some more editing (strip the text) it can never see the actual number on its own. I would I’m going crazy but Im not imagining this. I have tried various ways on a lookup field and get the same crap. Just to prove otherwise I linked ID’s from both list and it produced the result albeit wrongly misaligned ID’s as I would expect. So back to the issue. IF I create a lookup column that references another list per your insructions, the field comes back in my data source like Mileston:ID BUT the value is 10;#Project Planning so it can never be joined/ref’d to the Parent ID …

      • Does your look up column allow multiple values? You’ll get that kind of data when it does. Also, you have to pick something other than ID as your main look up value. Whatever is the main look up value will get the id#value treatment. So, it looks like your look up field is milestone, be sure that you’re pulling in the title as the main field and ID as a secondary field.

        If all else fails, you can drop into the code. If you’ve left everything as default, you should find a dvt_2 template in your xslt and it should have a variable for rows, something like this:


        <xsl:variable name="Rows" select="../../../Chapter_Documents/Rows/Row[@EIN_x002f_Chapter_x0020_Name_x003a_EIN=$dvt_ParentRow/@EIN]" />

        Your column names will obviously be different, but you can do a substring-before(@yourcolumname, ‘;’) to get just the ID. Like so:

        <xsl:variable name="Rows" select="../../../Chapter_Documents/Rows/Row[substring-before(@YourSubViewColumn, ';')=$dvt_ParentRow/@YourParentColumn]" />

        Make sense?

      • Alan Andrews

         /  March 19, 2013

        Hi Thanks Deanna, I actually have just been trying the substring approach, and even proved I got the actual number (ID value) by adding it as a displayed column but it would still NOT match up. Strange and CONFUSING!. So … I decided to add a dummy number fileld (MSID) to my list, populated some numbers in it correlating to Milestone ID’s then recreated the joined subview based MSID = @ID

        ….
        and what do ya know ..Viola it lines up. So it’s back to the drawing board, I am sure I have done everything correct, BUT DAMN if it it will work. 😦

        The original Look up is not set to multiple?? So god only knows why ..
        Thanks anyway I’m off to smash my keyboard over a beer 🙂

      • *sigh* “Work-around” is SharePoint’s middle name, I’m pretty sure. At least you came up with a solution. Now you just need to auto-populate that MSID field eh? 🙂

      • Alan Andrews

         /  March 19, 2013

        Yes SP is a hellacious Workaround system. We are using the SP Online E3 plan .. dont ask :-). Thanks again

      • Alan Andrews

         /  March 19, 2013

        I finally decided to use a Workflow on my tasks list instead. OnChange or New Task, get the lookup actual Value and added it to the new MSID field as a number. Then do the linked datasource sub data view ID=MSID. Works fine!

  3. Alan Andrews

     /  March 19, 2013
    Reply
  4. Angel

     /  April 29, 2014

    Hi, Need urgent help!!!

    Above steps are working in case we have to single value.

    Like: I have two lists List 1 and List 2

    List1 has ID column and List2 CategoryID Column (Multi select lookup of list1 ID Column).

    When i select single value in CategoryID lookup column above steps works fine in dataview subjoin but i select multiple values (1; 3) in CategoryID column it is not displaying anything.

    I tried below change as well but its not working

    Any solution for this?

    Thanks,

    Reply
  5. Trish

     /  June 17, 2015

    This worked great to get the lists joined, but now I am trying to add a field from the second list – in the original table it is a lookup to the sharepoint members and has multiple entries. I just want it to have the names, it gets pulled in with all the information associated with the name, 2;#Fontaine,,Trish,#,#patricia.fontaine@xxxxxxxx.ca,#patricia.fontaine@xxxxxxxxx.ca,#Fontaine,, Trish;#24;#Xxxxxxxx,, Peter,#,#xxxxxxxxx@ualberta.ca,#,#Xxxxxx,, Peter;
    Is there a way to pull out just the names?

    Reply
    • I think you’d either have to parse the string or run a workflow on the secondary list to update a text column based off the displayname when an item is added or updated and then pulling that column. The second approach might be easier if you’re not dealing with already populated lists here.

      Reply
  6. Da ich von klein an mit Wein groß geworden bin, habe ich auch eine andere
    Einstellung, was Alkohol betrifft.

    Reply
  7. That is a great tip particularly to those fresh to the blogosphere.
    Simple butt very precise information… Thank you for sharing this one.

    A must read article!

    Reply
  8. Excellent article. I definitely appreciate this website.
    Thanks!

    Reply
  1. Display item counts in a Data View Web Part « Blah-de-blah-blog
  2. Joining the Task List with Related Content in a DVWP « Blah-de-blah-blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: