4 Minute Screencast: Join View of SharePoint Lists
Have you noticed that in SharePoint, there is no obvious way to join two lists or libraries, using a common field? For those of you who have ever used Microsoft Access or any other database, you know that you can just create queries to display joined tables. In this screencast, I show an example of how you can show data from 2 different lists, as ONE.
There is a contact list of vendors on your site, and there is a list of appointments that you have with those vendors. You want to display a list of upcoming appointments, and also display that associated vendor’s phone number in the appointment list. Here’s how!
There are so many uses for this “Join” functionality! Another example that I’ve used at work (in healthcare) is a list of all of the room numbers in the hospital, with the phone number for each room. This is a static list. There is also a list of patient appointments, and each patient has an associated room number. NOW, I can display the list of patient appointments, and also include that room’s phone number along with the patient… data from two different lists!
Author: Laura Rogers, Birmingham, AL
Laura Rogers is currently a SharePoint Administrator at HealthSouth Corporation in Birmingham, Alabama. She has ten years of experience with Microsoft’s messaging and collaboration systems. This includes four years in SharePoint implementation, training, and customization. Laura has been a MCSE since 1999, with her most recent certifications being MCSE 2003 + Messaging, MCTS in MOSS and MCTS in WSS 3.0 Configuration. She also wrote a chapter in the Microsoft Office SharePoint Server 2007 Administrator’s Companion by Bill English.
- New Article Series: Laura Rogers on Data View Web Part Basics
- Data View Web Part, The Basics - Insert a DVWP on Your Page
- 6 Minute Screencast: Insert a Data View Web Part onto a SharePoint Page
- Data View Web Part, The Basics - Folders
- 3 Minute Screencast: Use DVWP to display all files, even those stored in folders!
- Data View Web Part, The Basics - Add a Hyperlink
- Data View Web Part, The Basics - Keep it Clean
- Data View Web Part, The Basics – Multiple Edit Forms
- 3 Minute Screencast: Data View Web Part, The Basics – Multiple Edit Forms
- Live Online Workshop - Data View Web Part Solutions: Part 2
- 5 Minute Screencast: SharePoint Web Services for Non-Developers
- 3 Minute Screencast: Merging SharePoint Document Libraries
- 2 Minute Screencast: Permissions Dashboard
- 4 Minute Screencast: Join View of SharePoint Lists
- Get it Together: Groups and Totals in the Data View Web Part (Screencast)
- SharePoint: Open Links in a New Browser Window (Screencast)
- How to Create a SharePoint Data View Web Part “Rollup” (Screencast)
- SharePoint: "New" Icon (Screencast)
- Display a SharePoint List on Another Site (Screencast)
- Customize the "no items" Display Text in SharePoint (Screencast)
This is something I had never seen before, thanks for sharing. This opens up many opportunities.
This great information that I may be able to use and I also work in a hospital (military).
Awesome!!! Thanks for showing me this. I needed this exact functionality today! Thank you, thank you, thank you!
Blessings,
Jim Bob
PS: Oh, and did I mention? THANK YOU!
Would it be possible to do this with lists that are in a subsite of this team site?
Sharepoint Server 2007
Thanks for the clarity Laura!
I have List A and List B with a one to many relationship. How can I get the resulting layout to NOT GROUP by List A?
In other words, I need the List A item to repeat on every line with the corresponding items from List B on the right.
Example:
Item A1 + Item B1
Item A1 + Item B2
Item A1 + Item B3
Item A1 + Item B4
Item A2 + Item B5
Item A2 + Item B6
Item A2 + Item B7
Item A2 + Item B8
Thanks-
Charlie Epes
Buffalo, NY
(I think Sahil Malik deserves some credit here for posting about this back in 2007: http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx )
Awesome Laura, I know just how I want to use that…. and hey keep building that shed :-)
Hi Laura
Thanks for the information, but is there a way to do this when creating a new document?
In your case, you bring in the Vendor Name and in a calculated field, it brings in the phone number automatically.
Is there a way of filtering the main rows by that field? I need a main select that does that..
Thanx in advance
Laura,
I am unable to add the parameters to the view now. Any clue?
Nice video to share with my users. Thank you!
Marc’s way to create a join here http://mdasblog.wordpress.com/2009/07/24/the-right-way-to-join-two-lists-in-a-dvwp/ will handle joins based on multiple columns or calculations
Thanks Laura, this is very helpful. Is there a way to do the join and have it show in the calendar view?
How do you sort/Group a linked Data source DVWP? I’ve been able to link a list with a library, but can’t sort/group/filter the data?
I’ve also been trying to figure out how you group/filter by the data in the joined list.
I can filter/group by the main list just fine, but nothing works for the second list.
i am using 4 DVWP on my page.
my requirements is that when the the user makes selection on first DVWP rest of the three show data accordingly so i used conenction and it is working fine.
by default the fisrt item in my first DVWP is selected which is fine BUT i have another requirement that if i pass a variable using query string when calling that page, i want the second or the third item to be selected (depending on whose id i have passed).
is it possible?