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:
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 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.
Now, go back to your joined subview and select your additional column. Voila!