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
Hi Karen,
Struggling with similar issue. I have two lists with 1:1 relationship and a common field (text). Am not understanding how to relate the two “joined” lists such that I can display rows of records with ListA-FieldA, ListA-FieldB, ListB-FieldA…and so on.
Any wisdom?
Tim
Hi, it’s been a while since I watched the video so some of what I note here, might be in there, but here’s what worked for me (and I’m running thru it again to make sure I have it right)…
1. Setup the Linked Sources with your two lists, make sure you pick join the contents, give it a name, blah blah blah.
2. Create a new dataview, choose your linked source that you just created, show data.
3. I chose a field from my parent list, Insert selected field as multiple view…I do my own styling once the base is setup so I pick one field on setup and add others later.
4. click on the wysiwyg’s first row that it gives you of your parent’s data, at the end of the field.
5. right click, add column to the right.
6. click in that first new column’s row and in your datasource, choose the field you want from your child list (again, I choose 1 field, add others once it’s working). Insert field as a joined subview.
7. The join subview window pops up, pick how your lists are supposed to join, click ok, and from there your child list data should appear in the right column cell that is related to the parent.
In the end, if I recall correctly the problem I was having was all related to the positioning of my cursor when trying to select/add fields to the view. If you’re in the wrong spot, it won’t let you see that join menu, don’t ask me why b’c I can only figure that it’s all by design with MS’s infinite wisdom :)
HTH, let me know if you need other details that I might’ve glazed over too quickly.
Karen.
Karen,
You’re a lifesaver! I’ve got it now! It’s amazing how non-intuitive that was…Infinite MS wisdom, indeed!
Many thanks for expanding my world.
Tim
Hi Laura,
Great article! Would I be able to export the web app and use it on another site? If possible, what changes will I need to ensure that I won’t get the ” Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator” ?
Thanks, Ian
Hello,
I just saw this video that showed how we can join view of sharepoint List.
now, can i join a view of external sharepoint list to a sharepoint library ?
thanks for your help
Hi Laura,
Thank you for your post.
Is it possible without using sharepoint designer?
ThankYouThankYouThankYou
That’s all I can say. This is exactly what I’ve been searching for and it worked *beautifully*.
Thank you so much.
This is extremely informative; I am, however, having an issue. I’m trying to join using two fields: Created by in one list and a “Person” field created in another. What I need to be able to lookup a rate (numerical) associated with a specific person and automatically attach that rate with the person who created a record in another list.
When I try the method described in the video – I get no data, same as you would if you chose two columns whose data types didn’t match…
ANY help would be greatly appreciated!
Thank you for the awesome resource!
Kevin
Dear Kevin,
It will be hard to trouble-shoot your situation here on the blog. Post it over on Stump the Panel (http://www.endusersharepoint.com/STP) and our trained SharePoint snipers will help walk you through a way to make things work the way you need them to.
Blessings,
Jim Bob
I have two list joined togther with a calculated field in one of the lists. The calculated field returns a unique value. but when I try to join them up in SP designer, I notice that the lookup field as added ‘1;#’ infront of the unique value – any idea why sharepoint designer would do this
Thanks for pusting the video. Very helpful for basic joins…
I’m having trouble with a comlex join. I am using SP Designer 2007 to try and make a data view of 4 lists. The lists were imported from Excel tables (an old database had to be destroyed, so the data was preserved in 4 sheets of a xslx spreadsheet), and each stand as their own list in my site collection. I can look at them, and the behave just fine as lists. They all have a column called “ATCC_NUMBER,” but they are not identical in their records. For example, list A might have records 1-50, list B might have records 5-52, etc.
I am concerned that this is preventing me from creating the join between the lists. I am able to complete the join wizard, and the new linked data source is there. I click show data, and I see all the fields from A-D, but have to click through the 4 lists to see any data show up for them. When I try and select a field from each and insert selected fields as a multiple item view, all of the columns show up, but only data from one list shows up.
Any ideas?
Nice walk through!
I’ve been there done that, problem is when you start building these views for users you now have code to maintain when they go add new columns.
SharePoint is great.. but it would be awsome if it incorported a few features into their core product like this:
-view joins
-column level security
-form level security (at least a webpart to stop users for accessing a page)
-some relational integretity between lists
-form validation
sure we can build all this, but so much value in being able to just have it vanilla and working with all of the UI.
Thanks.