Supporters of End User
Web

4 Minute Screencast: Join View of SharePoint Lists

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.
Categories:Data View Web Part; SharePoint Designer; MOSS; WSS; 2007; 2010; Data View Web Part Basics; Screencast; Libraries and Lists; Site Manager/Power User

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!

Join View of SharePoint Lists

Comments

Caleb

4 Minute Screencast: Join View of SharePoint Lists

This is something I had never seen before, thanks for sharing. This opens up many opportunities.

Posted 23-Jul-2009 by Caleb
Frank

4 Minute Screencast: Join View of SharePoint Lists

This great information that I may be able to use and I also work in a hospital (military).

Posted 23-Jul-2009 by Frank
Jim Bob Howard

4 Minute Screencast: Join View of SharePoint Lists

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!

Posted 23-Jul-2009 by Jim Bob Howard
SharePoint Daily

4 Minute Screencast: Join View of SharePoint Lists

Cool New Features in Office 2010, New Microsoft Developer Tools, A Microsoft Reality Check... Top News Stories 10 Cool Features to Look Forward to in Office 2010 (Tech Republic) Microsoft kept the...

Posted 24-Jul-2009 by SharePoint Daily
Andrius

4 Minute Screencast: Join View of SharePoint Lists

Would it be possible to do this with lists that are in a subsite of this team site?

Sharepoint Server 2007

Posted 24-Jul-2009 by Andrius
Charlie Epes

4 Minute Screencast: Join View of SharePoint Lists

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 )

Posted 24-Jul-2009 by Charlie Epes
The

4 Minute Screencast: Join View of SharePoint Lists

[...] I saw this video from Laura Rogers that showed the way you are probably “supposed” to do a “join” in your [...]

Posted 24-Jul-2009 by The "Right" Way to Join Two Lists In a DVWP « Marc D Anderson’s Blog
Graham Innes

4 Minute Screencast: Join View of SharePoint Lists

Awesome Laura, I know just how I want to use that.... and hey keep building that shed :-)

Posted 25-Jul-2009 by Graham Innes
Iain Munro

4 Minute Screencast: Join View of SharePoint Lists

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.

Posted 25-Jul-2009 by Iain Munro
mad-tie

4 Minute Screencast: Join View of SharePoint Lists

Is there a way of filtering the main rows by that field? I need a main select that does that..


Thanx in advance

Posted 27-Jul-2009 by mad-tie
Tahir

4 Minute Screencast: Join View of SharePoint Lists

Laura,

I am unable to add the parameters to the view now. Any clue?

Posted 04-Aug-2009 by Tahir
Calculated Column Formula Tips - Aggregating Calculations Over 1000 | End User SharePoint

4 Minute Screencast: Join View of SharePoint Lists

[...] application and was able to use some tricks I learned from Laura Rogers in her screen cast, 4 Minute Screencast: Join View of SharePoint Lists, to link data from different lists. Her techniques gave me the direction I needed to display all of [...]

Posted 06-Aug-2009 by Calculated Column Formula Tips - Aggregating Calculations Over 1000 | End User SharePoint
Aziz Sori

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 16-Sep-2009 by Aziz Sori
Shawn

4 Minute Screencast: Join View of SharePoint Lists

Thanks Laura, this is very helpful. Is there a way to do the join and have it show in the calendar view?

Posted 01-Oct-2009 by Shawn
Jennifer

4 Minute Screencast: Join View of SharePoint Lists

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?

Posted 08-Oct-2009 by Jennifer
Chris

4 Minute Screencast: Join View of SharePoint Lists

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.

Posted 21-Oct-2009 by Chris
mohsin

4 Minute Screencast: Join View of SharePoint Lists

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?

Posted 25-Nov-2009 by mohsin
Iain Munro

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 10-Feb-2010 by Iain Munro
Linda

4 Minute Screencast: Join View of SharePoint Lists

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?

Posted 15-Apr-2010 by Linda
Brian Bedard

4 Minute Screencast: Join View of SharePoint Lists

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!

Posted 20-Apr-2010 by Brian Bedard
Andee

4 Minute Screencast: Join View of SharePoint 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?

Posted 06-May-2010 by Andee
ADBurnett

4 Minute Screencast: Join View of SharePoint Lists

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?

Posted 16-Jun-2010 by ADBurnett
Jim Bob Howard

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 17-Jun-2010 by Jim Bob Howard
Tharaka

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 13-Jul-2010 by Tharaka
Matt Pinkston

4 Minute Screencast: Join View of SharePoint Lists

Laura,

Thanks for the pointer, the one question I have is how can you get several lists linked in a noral list view?

Posted 23-Jul-2010 by Matt Pinkston
Karen

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 01-Sep-2010 by Karen
Ian

4 Minute Screencast: Join View of SharePoint Lists

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

Posted 29-Sep-2010 by Ian
sujeewa ediriweera

List join in SharePoint

http://sujeewaediriweera.wordpress.com/2012/02/18/list-join-in-sharepoint-2010-using-caml/

Posted 23-Feb-2012 by sujeewa ediriweera
i:0e.t|liveid|00030000b1009e8b@live.com

Join in Calendar view

I have done join in sharepoint lists by using caml query. within that I am putting where clause on projected field and it's working fine. Now my question is that thing is working on simple view... But I am not able to do it on Calendar View.. Please help me out. Thanx in Advance.

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: