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)
- SharePoint: Displaying Calculated Column SUMS in a View (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 needed to do this as well. Luckily it was easy to implement. If you’ll notice in the XSLT, the dvt_1 template that has the headers that control sorting and filtering. Notice the joined columns from the second list don’t not have a call to the dvt_headerfield template. Just replace whats in the tags with a copy of that call-template node. Be sure to include all the tags. After you copy it, replace the @fieldname with the name of the column you want to sort on. Just follow the pattern. You can get the fieldname by looking above at the DataFields before the tag, your joined columns should be listed probably on the end. Use the @fieldname identifer for the fieldname with-param tag. It should work as long as you have those joined columns defined in DataFields. If not, thats a more interesting problem.
Good luck!
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?
Hi Laura
Now I have a need to use this – the problem is that when i join the lists, I am getting a
The server returned a non-specific error when trying to get data from the data source.Check the format and content of your query and try again.If the problem persists,contact the server administrator.
Any ideas?
Iain
This works really well on small lists with not much data, but I find the JOINs are really slow on large lists with more than 2,000 records and lots of column data.
Do you have an suggestions on how to get better performance of JOINs using very large lists?
Hi Laura,
I’ve been trying to follow your instructions, however the Join Subview dialog box does not pop up. Have you ever heard of this issue?
Dear Laura,
Thanks for the informative post and great screen video. I have done what was shown, now I would like to do a simple function as so:
Part Name | Required Qty | On Hand Qty | <<<<>>>
No matter what expression (+, -, >=) I used…. EVERYTHING in the On Hand QTY is handled by the math as a “1″ even though it displays correctly as “1250″
Required 23 + On Hand 890 = 24
What am I doing wrong?
What does your statement look like for trying to add these?
If you add them the other way (On Hand 890 + Required 23), do you get 891?
You may have to force one to be a number instead of a string. Search for my post on the number 1000. It might give you a hint on how to solve this issue.
Blessings,
Jim Bob
Hi Laura,
I joined 2 lists and then clicked show data, but it failed to show data fields. I got the following error message instead.
The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again.
One of the lists has attachments in it. Could that be the reason for failing to show data? Please let me know what you think.
Thanks,
Tharaka
Laura,
Thanks for the pointer, the one question I have is how can you get several lists linked in a noral list view?
Similar question to Andee from above. I’m not getting the Join Subview popup window where I select the common columns. All it does is put the data from the second list into the field.
Both lists are in the same site and they’re pretty much identical with column names except the 2nd list has a “parent id” field that links it to the first list.
Any ideas what the trick is to that?
environment: MOSS 2007