Performing joins between SharePoint lists

Sound off but keep it civil:

Older comments..

On 10/22/2007 8:45:25 PM Steve Schapel said ..

I only know of one Sahil Malik, so that's safe. But I know two people named Andrew Connell (true story!). Can this trick be done by joining on the basis of a numerical ID field, rather than using the person's name?

On 10/22/2007 10:04:00 PM Sahil Malik said ..

Hey Steve -

Yes you can use IDs as well. But I bet Andrew will still be buying Mansions and Mercedeses!!

Sahil

On 10/28/2007 6:29:01 PM Mark said ..

This is cool! Do you know if it's possible to aggregate values from the subview; for example to display only the number of orders for each customer?

Mark

On 11/19/2007 3:45:30 PM Vivian said ..

Great. I have same question as Mark. I see that it is done at dashboard.aspx at the budget and trace for multiple project template to get the count from the subview. I cannot figure it out how it is done. Do you have any idea?

On 12/2/2007 4:43:48 PM John Haigh said ..

Hi,

Do you know if there is a way to filter the data in the aggregated datasource..i.e. by a query string value (ID=2) whereby you would only show a Customer with the ID of 1? I have tried but have been unable to pass filter values to the aggregated datasource.

Thanks,

John Haigh

On 12/4/2007 10:46:29 AM MARCOS ROJAS said ..

PLEASE I NEED HELP
ALWAYS PROGRAME IN VB5, AND NOT WANT TO CHANGE THE VERSION.
BUY NOW AN LATOP THAT HAVE WINDOWS VISTA, INCOME AND WHEN A "COMPONENTS" I DO AND CLICK ON "MICROSOFT FORM 2.0 OBJECT LIBRARY" CLICK AND DO IN OK ME HE DENIES PERMISSION.
I DO THAT?

On 12/5/2007 11:48:36 AM Eric Sammann said ..

That is a great feature. I actually discovered that a couple of months ago, but now I need to do a join to accomplish a lookup from one table in order ot update another table. If you go to Data View -> Insert Data View, then you get several options when showing data from one table. the options you get are:

Single Item View
Multiple Item View
Single Item Form
Multiple Item Form
New Item Form

if you do the same thing on a joined datasource then you only get the first two, no form options. Do you know if something like that is possible?

On 12/7/2007 6:29:11 PM maria said ..

What is the benefit to joining the two lists when you can achieve the same results by just creating a lookup from one list to another, grouping the customer name and then selecting a total to display?

On 12/17/2007 11:28:13 AM John said ..

Great post. I now have the aspx file that I can browse from my sharepoint site. Is there a way to add it as a webpart? Maybe I could have three webparts, the customer list, the order list and then the aggregate (customer-order) as a third view in a custom page with 3 columns. I'm off now to view you dnr tv sharepoint shows!

On 12/17/2007 11:30:53 AM Sahil Malik said ..

John -

It *is* already a webpart. :)

If you are asking, if this can now be packaged, and dropped via a browser. The answer to that unfortunately is No :(. It has to be a sharepoint designer thing.

SM

On 12/19/2007 1:34:49 AM Steve said ..

Thanks for your great share. May I ask if I could post this joined data as one of my customized views on MOSS site. Or I just need to package it as a web part and insert it on MOSS site.

On 12/20/2007 2:54:26 PM Steve Sherman said ..

I'm following your instructions word for word, but the graphic representations are not showing. I would like to be able to see those as I think they will help in concluding the project I have going.

Any way I can get those?

Thank You,

Steve Sherman

On 12/20/2007 8:13:18 PM Bill said ..

I've created a view with a joined subview as described. It works great. I do have a problem applying conditional formatting though.

I am using an html view style available in SPD (second selection).

I can set up the filter conditions for the formatting (I am showing or hiding a pic based on a field value), but the pic just disappears (wether it is supposed to or not).

I tried applying font and cell background changes but they don't work either.

Any ideas that might help me?

Thanks

On 1/10/2008 7:20:15 AM Antonio Calvo said ..

Thank you very much for the post. Im using this tip to show a table with results from two joined list, for example "project" and "customer". One of my lists ("project") contains a lookup field to the other one ("Customer"). In order to avoid integrity problems I have configured that column to use de ID of the item instead of, for example the title, as you do in your post. That solves the problem but it generates another one: you have to develop custom forms for each list. I mean, you cannot present the user the default EditForm.aspx for the list "project" because the dropdown control to select the corresponding customer will show the list of IDs. As you can imagine it will be great to show here the "Title" field from the customer (or even something like ID-Title"). ¿Can anyone point me in the right direction to solve this problem with the minimun work?

Actually is it any way of acomplish this using two webparts on the form connect somehow?

Im a bit lost trying diferent things so, any tip you could provide will be a great help.

Thank you!

On 1/16/2008 11:10:18 AM Matt said ..

Is there a way of using the context menu (View Properties, Edit Properties, Check out, Workflow etc) like seen in standard document libraries for each row?

I can't seem to get this working with a Data View web part (without converting from a Lits View web part to a Data View web part, but when I add another data source it stops working).

On 1/16/2008 11:45:09 AM Sebastian Soanca said ..

Hi Malik,

Nice post. I have the same situation but i want to use the join feature in Add New Item/Edit Item mode. It is possible to linked 2 look up list and filter the child list after item selection in parent list ?

Thank you.

On 1/17/2008 9:11:08 PM Leren said ..

good post. I got it working immediately

On 1/28/2008 8:40:36 AM Michael said ..

I am trying to perform this operation but everytime I try to access the data (show data)I receive the following message:

You do not have permission to do this operation. Ask your web site administrator to change your permissions and then try again, or log on with a user account that has this permission.

My server admin states: "There was a service pack released a very good while ago that caused the data view web par, and some otherst to not work for administrators of sharepoint sites as it should. I opened a ticket with MS on this, they confirmed it was a bug, were able to recreate the scenario in their lab, and said they were addressing it, right along with the “we don’t know when the hotfix will be out” statement." I am able to set up sharing data between lists through the sharepoint site interface and a lookup field will pull data from the other list but I am unable to get the data related to the lookup field to pass. What field should I be setting up to display this information? A text box? In other words if I select a customer in the selection field, I want other fields to populate automatically with that customers info. We are on server 2003. Any ideas?

Thank you,

Michael

On 2/11/2008 7:04:00 AM Adam said ..

I'm accustomed to joining tables in a query via SQL Server and Access and am looking to replicate the results of a simple SELECT statement, whereby the results are returned in a simple tabular form. Is it possible to replicate this via SharePoint?

On 2/11/2008 1:09:24 PM Sahil Malik said ..

Adam - Yes you can. Look into SPQuery and CAML.

On 2/14/2008 2:37:36 AM Nitin said ..

Hi Sebastian,

Showing data in Parent/Child Mode is possible if you use ListViewWebParts and then link them. I have done it and it works well.

Thanks,

Nitin

On 2/28/2008 9:23:23 AM Tritata said ..

Hi.

For example, there're two lists, one of them has a lookup field referenced to the other list's field. Is there way to retrieve information from two lists by one query? Something like this SQL statment does: "SELECT List1.field1,... List1.fieldN, List2.field1,... List2.fieldM FROM List1, List2 WHERE List1.Lookup=List2.Id".

On 3/1/2008 6:42:13 AM Dean said ..

Salik, this is very helpful, do you know how to take this a step further by having lookup fields in a list/library that are related? e.g., After the State field is choosen a subset of Cities is available to choose from when entering metadata for a document

On 3/10/2008 4:09:48 PM Tomasz Szalaj said ..

Is it possible to make some example of using controls from Ajax Control Kit with Sharepoint Webservices to create cascading filters for lookups to Sharepoint lists?

Greetz!

TSz

http://www.it-dev.pl

On 3/11/2008 6:17:53 PM Thia said ..

Just what I have been looking for - only When I click "Joined Subview" instead of giving me the Join Subview menu, it just ads the information from the second list in front of the information from the second list - onthe same line.

How do I get the Join Subview Menu to pop up?

Thanks!

On 3/12/2008 10:18:15 AM Thia said ..

OK - I was able to get the "Joined Subview" to come up sometimes, but even when it came up, it was only on 2 lists. What if I want to join 3 lists?

Such as:

List A

List B

List C

Where List C has a lookup to List B and List B has a lookup to List A.

On 3/27/2008 6:14:26 PM doctor research said ..

Hi, Do you know if there is a way to filter the data in the aggregated datasource..i.e. by a query string value (ID=2) whereby you would only show a Customer with the ID of 1? I have tried but have been unable to pass filter values to the aggregated datasource. Thanks, John Haigh

On 4/4/2008 3:03:57 PM Xurgum said ..

Hi,

Thanks for an awesome post Sahil. Do you know if this would also work for lookup fields with multiple selection?

E.g. what if customer column in Orders list could hold both Scott and Andrew for the order of banana in the same record. (hey Andrew could also have a banana =)

Xurgum

On 4/5/2008 6:10:09 AM girlgeek said ..

Can I create an editable form with joined data? I want to create a editform.aspx that allows me to provide a way for the user to provide child items to a parent item (somehow grabbing the ID of the parent for the user).

thank you for posts like these.

On 4/27/2008 11:40:20 PM Alejandro Lebrero said ..

Can do the same but using two lists placed into different sites?

On 4/28/2008 4:06:35 AM drTheory said ..

I'll repeat the question Tritata wrote...

Did anyone managed to join tu lists in to one as we're able to do with SQL statements?

Something like Tritata already wrote: "SELECT List1.field1,... List1.fieldN, List2.field1,... List2.fieldM FROM List1, List2 WHERE List1.Lookup=List2.Id".

On 5/2/2008 4:36:03 AM Zuber Agwan said ..

this is a great post i just wanted to ask that i want this to be done in the sps 2003 site using spd 2007

but i m not able to get the "create a linked source" hyperlink when i open sps 2003 site in spd 2007

sujjest something ????

On 5/23/2008 1:40:57 PM akash said ..

hi,

I used the same approach, only thing is i had "thumbnail"column of a picture column as a second list, which i wanted to use.....

now when i join the thumbnail dosent show up,,instead it gives a numeric value.....

why is that and what is the work around?

On 5/28/2008 12:40:54 PM tgsims said ..

If I understand your solution, you are sending the join to a SharePoint page (URL). In a sense this is just a virtual join. How do I send the join to a new or existing SharePoint list?

On 6/8/2008 9:37:27 PM Jeremy said ..

I got lost at step 6. [Great, now while in the <form> tag in your ASPX, click on Data View --> Insert Data View, your UI should now say "Current Data Source - Customer - Orders" - "next insert fields to create a view".] I don't know what you mean by "in the <form> tag of you ASPX". When I Data Source Detials tab I have my Customers - Orders as the Current Data Source and I have but the "Insert selected fields as..." button is greyed out. What am I doing wrong? Am I supposed to have something in particular displayed the main part of the SPD gui? Sorry I am not hip to your nomenclature, but "my ASPX" is referring to linked sources that I just made in steps 3-5?

On 6/22/2008 8:35:43 PM pradeep said ..

Hi,

My developers have been trying to do this for months... but with your article it just took 10 mins to have this up and running. very valuable indeed.

thanks.

On 6/22/2008 11:34:21 PM Sahil Malik said ..

Ok - you owe me Chai Samosa then!

On 7/23/2008 4:27:12 PM JulieAllyn said ..

How do you get the formatting results as displayed? The customer name appears at the bottom of each grouping for me. How to place it at the top? ALSO: How to display the label Orders just once in each grouping?

On 7/23/2008 7:58:45 PM André Rentes said ..

Hi

Can I join a sharepoint list and a database table!? Do you have samples?

Thanks!

On 8/19/2008 4:22:11 PM Fabio said ..

Hi,

can we use the ID of an lookup field instead of the value?? Because, there are some special characters that does not work with your example. If you replace "Andrew Connell" by "Johnson & Johnson", it says that could not find any order for this customer... the problem is the "&" character.

Any ideas?

Thanks,

Fabio

On 8/26/2008 4:40:35 AM KALASH said ..

see the tables below.

I want to create db schema in lists. means lists should be linked to each other as RDBMS work. can i use the lists instead of rdbms db. actually i dont want to use db.

these lists will hold thousands of records and i also want to perform re-search and Tracking on created lists.

------------------------------------------------------------------------------------------

USE [parameter_DB]

GO

/****** Object: Table [dbo].[search_details] Script Date: 08/26/2008 14:34:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[search_details](

[order_search_detail_id] [bigint] IDENTITY(1,1) NOT NULL,

[search_id] [bigint] NOT NULL,

[county_field_id] [bigint] NOT NULL,

[timestamp] [datetime] NOT NULL CONSTRAINT [DF_search_details_timestamp] DEFAULT (getdate()),

[Value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[record_order_flag] [bit] NULL,

CONSTRAINT [PK_order_feature_junction] PRIMARY KEY CLUSTERED

(

[order_search_detail_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [parameter_DB]

GO

ALTER TABLE [dbo].[search_details] WITH CHECK ADD CONSTRAINT [FK_order_feature_junction_county_field] FOREIGN KEY([county_field_id])

REFERENCES [dbo].[county_field] ([county_field_id])

GO

ALTER TABLE [dbo].[search_details] WITH NOCHECK ADD CONSTRAINT [FK_order_feature_junction_order] FOREIGN KEY([search_id])

REFERENCES [dbo].[search_history] ([search_id])

GO

ALTER TABLE [dbo].[search_details] CHECK CONSTRAINT [FK_order_feature_junction_order]

USE [parameter_DB]

GO

/****** Object: Table [dbo].[search_history] Script Date: 08/26/2008 14:35:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[search_history](

[search_id] [bigint] IDENTITY(1,1) NOT NULL,

[orderTimestamp] [datetime] NOT NULL CONSTRAINT [DF_search_history_orderTimestamp] DEFAULT (getdate()),

[user_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[company_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[county_fips] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[state_fips] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[station_id] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[outputtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_type_flag] [tinyint] NOT NULL,

[order_name_value] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[title_officer_value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_comment_value] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[keep_order_open_value] [bit] NULL,

CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED

(

[search_id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [parameter_DB]

GO

ALTER TABLE [dbo].[search_history] WITH NOCHECK ADD CONSTRAINT [FK_order_order_type_flag] FOREIGN KEY([order_type_flag])

REFERENCES [dbo].[search_type_flag] ([order_type_flag])

GO

ALTER TABLE [dbo].[search_history] CHECK CONSTRAINT [FK_order_order_type_flag]

GO

ALTER TABLE [dbo].[search_history] WITH CHECK ADD CONSTRAINT [FK_order_user] FOREIGN KEY([user_code])

REFERENCES [dbo].[user] ([user_code])

------------------------------------------------------------------------------------

On 8/28/2008 2:41:33 PM Sunder said ..

We have a similar situation as Kalash. We would like to use Sharepoint lists as database tables. This has been going well for us but we have run into a situation where we would like to implement a conditional lookup.

For example in a list if a person selects a value from a lookup field (drop down), we would like to display related values from the list the lookup field is refering to.

Would this be possible in Sharepoint? Any help would be appreciated.

On 9/15/2008 9:38:10 AM Rasmus Bodin Löfgren said ..

Thanks for a great post! You can achieve more join-like nature by passing the parent to the child rows. In this example it would allow us to show Customer fields on the Orders row.

<xsl:template name="dvt_2.body">

<xsl:param name="Rows" />

<xsl:param name="dvt_ParentRow" />

<xsl:for-each select="$Rows">

<xsl:call-template name="dvt_2.rowview">

<xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />

</xsl:call-template>

</xsl:for-each>

</xsl:template>

<xsl:template name="dvt_2.rowview">

<xsl:param name="dvt_ParentRow" />

<tr>

<td>

<xsl:value-of select="@Customer" />

<xsl:value-of select="$dvt_ParentRow/@Title" />

</td>

</tr>

</xsl:template>

On 10/6/2008 10:38:22 PM Anthony said ..

Hi,

I had the same approach as akash and still trying to show some picture with the ImageThumbnailDisplay Column. it only give a 0 as result. do you know how to make it work?

On 10/7/2008 3:38:47 PM Eric Damon said ..

Hi Nitin,

You said "Showing data in Parent/Child Mode is possible if you use ListViewWebParts and then link them." Can you elaborate on this? I have the exact same need as Sebastian Soanca.

On 10/22/2008 7:59:27 AM naveen said ..

hi sahil

iam new to the MOSS 2007

that a good work done by you i just want to kmow that this join functionality can be done by using webpart

On 11/2/2008 9:49:30 PM steven said ..

Hi Sahil,

Joining between the sp list holds good only if the two lists known at design time. I come into situation that the child list is not known at design time but rather at runtime. I have a master list, for each item there's a separate subsite link to it. Im tasked to merge the two lists one from the main site (known at design time) and from the subsite (known only at runtime based on the master list item ID). Retrieve the last updated item from the subsite list and join to the master list for e.g. the query steps will be like this:

1. Loop thru the master list first

2. Get the master.item_id

3. Open the subsite by master.item_id (subsite url is http://parentweb/master.item_id/default.aspx, here you can see subsite is not even known at design time)

4. Open the subsite list (here list name is known)

5. Get the last update record from the subsite list (retrieve only 1 record which can be order by last modified)

6. Show master.item_id, subsite.list.item_id, master.title, subsite.list_remarks, etc

7. Loop ends

Pls help. Thanks in advance.

Regards,

Steven

On 11/13/2008 2:01:39 AM Kim Flintoff said ..

Hi Sahil,

Nice piece of work. Now to try to stretch it a little more...

I have followed your instructions and successfully rendered a view of the joined lists like you example. However, this example is fine if each Order has only One Customer associated with it... in your example there doesn't seem to be any duplication - Scott, Andrew and yourself have no items in common.

As is more common with most orders there are often multiple customers associated with the Order. So, to this end I used the lookup option with Multiple Values.

In this instance your solution does not display any of the common items... For example if Scott and Salih both wanted Bananas then Banana does not show on the final output.

Can anyone suggest a way to allow the multiple values option to be used in the Customer lookup?

On 12/2/2008 8:06:44 PM mark said ..

I've been running throught this process but I can't seem to get the "Joined Subview" window to show up. Any idea what I'm doing wrong?

On 1/13/2009 10:25:34 PM songmaker said ..

Filtering linked sources in a data view.

There seems to be a lot of people foxed by this one so I'll post the solution here:

Problem is with the XPath expression automatically generated by SPD - you can see this by looking at the Advanced condition dialog (Advanced...) when you're setting up the filter.

The ../../../Rows/Row/ is erroneous as we are already at that level in the XML structure.

Getting rid of this leaves you just with the @{column} and the filter should work perfectly. Similarly for sorting and grouping.

the reference is here:

http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/e2cfe2cf-aaa3-467e-881b-3998061d8c6d/

On 1/21/2009 5:08:32 PM suma said ..

I am new to sharepoint. I am stuck at step 7. After inserting the dataview, it's showing the current datasource is Customer-Orders but in the datasource details pane it's grayed out and the insert field....also. It says the server returned a non specific error when trying to get data from the datasourse. What am I doing wrong?

Any help is highly appreciated since I am totally lost in this !

Thanks in advance

On 1/21/2009 9:15:17 PM suma said ..

Update:

I am using MOSS 2007 and WSS3.o

Thanks!

On 2/2/2009 3:12:57 PM Allan Wellenstein said ..

Another approach worth considering:

SharePoint List Association Manager

http://www.codeplex.com/SLAM

On 2/9/2009 3:06:04 PM Ron said ..

Can you clarify the information from step #6 - I am unclear what you mean about being in the "<form> tag in your ASPX" "Multiple Item View"

Whatever I am doing is not letting me perform step #7 becasue the "Insert Selected Field as" button is grayed out...

On 2/16/2009 4:49:55 AM Dave said ..

I want to link several tables together for a projects portfolio. The first is a project ID table to generate a unique project number. The second is a project detail. The third is project people. The fourth is project reports and the final is project phases. I want to link them together using project Id from the first table. Is this possible or am I being too ambitious?

On 2/17/2009 12:46:35 PM SPDeveloper said ..

Hi Ron,

Did u find any solution for it? Iam having the similar problem...

On 2/24/2009 12:53:18 AM Shanthi said ..

Hi,

I am facing an issue with joined subview..

I have two lists say Skills and IndividualSkills, in these lists Employee name is common field. I am joining two lists using Linked data sources.

in designer i will perform the following task:

Insert a dataview webpart and populate the data from linked source(of Skills) and insert a row below and populate the Employee name from linked datasource of IndividualSkills.I will apply filter condition for joined subview and main view. Finally i will hide the Employee name of Skills list. Here if i apply any filter contion and search for result, if there is no matching employee name in both the lists then Gap occurs.How to avoid this Gap..Even I tried to disply all the items(there are 1600 items in list), but it throws "Request Time Out" error. Could you please help me.

thanks,

Shanthi

On 2/24/2009 4:47:55 AM Santhi said ..

Hi,

I have an issue with joined subview. I am trying to populate the data from linked datasource.

There are two Lists, Cluster and Individual skills.

These Lists have common field called "Employee Name".

Here Clusters(ex: Enterprise.NET) have some ratings likes 1, 2, 3 etc.and individual skills(ex:ADO.NET,ASP.NET,VB.NET, .NET) have ratings like 1, 2, 3..

Each Employee has some clusters(like Enterprise .NET) as well as individual skills(Like ASP.NET, ADO.NET, VB.NET, .NET).

The format of cluster and individual lists looks like this:

Cluster

EmployeeName Enterprise.NET EJava

aaa 2 3

bbb - 1

ccc 4 -

Individual Skills List

EmployeeName .NET ADO.NET ASP.NET VB.NET

aaa 2 4 2 3

bbb - 1 1 2

ccc 1 3 - -

Here the individual skills .NET ADO.NET, ASP.NET belongs to the cluster "Enterprise.NET".

My requirement is to perform the refine search for the for the architects based on Cluster as well as individual skills.

Suppose if i give Enterprise.NET as 1 and .NET as 1 then it should show result from "aaa" and "ccc" from above example. Basically the filter works for greater than or equal to condition.

This is how i am performing this task:

connect the two lists using Linked data source concept(join)

insert the dataview webpart and populate the EmployeeName from Linked datasource(Cluster list) and insert a row below "EmployeeName" and populate the EmployeeName from connected sorce of indivisual list as joined subview by passing EmployeeName as common field.

I will apply the filter condition for both the views(main and joined subview) and i will hide the EmployeeName from the Cluster List.

Here if there are no matching items between two lists then it shows nothing in the row..Just a Gap.this is the issue

How to avoid this Gap.

I tried to Display the items in following ways:

Display all the items in the dataview but it throws an "Request Timeout Error"

Display the items as sets of 100 items (Limit the paging to 100 items).

Tried to diaplay the items two columns instead of two rows.

Thanks,

Shanthi

On 3/12/2009 7:00:34 AM Ilias Tsoukalis said ..

Hi, this is indeed a great post although the taugh situation would be to create a dataformwebpart in edit mode (or newform), based on a joined source, in order to edit (and not just view) fields from different list simultaneously. As far as i have digged in, Designer uses a function "__designer:bind" inside SharePoint:FormField in order to pass the field value change, which is kind a difficult to modify (and use it for another list's field) cause it call the ID of the current list. Except that, i don't really know if that is going to work anyway if i manage to transform it. But anyway... i someone has a clue mail me!

On 3/25/2009 6:22:56 AM Andy Burns said ..

An early comment asked why not just use ListViews and grouping; we'd a scenario that demonstrates the benefit of using the dataview. We had to sum across linked sub items, and then subtract from a value in the parent list. Actually, wasn't too bad with a little XSL.

It also shows the aggregation that some folks were asking about.

http://www.novolocus.com/2009/03/25/linking-list-data-and-summing-over-it-with-xsl-and-the-dataview-web-part/

On 5/8/2009 10:55:19 AM Ron said ..

I don't see the images to the post. Is there a way to get them?

On 5/8/2009 4:46:34 PM Sahil Malik said ..

Ron - ask your infrastruture ogres to unblock imageshack, and the images will come thru :)

S

On 6/1/2009 1:10:04 PM JC said ..

On 6/1/2009 1:21:24 PM JC said ..

Very nice post for sure.

I tried this and it sort of worked for me, but for some reason if I add other columns form the first list I get data repeating to the first row of that first list. The second list is fine and the join match does appear to work though.

I'd also like to ask, Is it possible to creat a joined view, that could then be used by users using the normal View interface inside WSS? Meaning they could decide what columns to display? groupby and filter it?

Thank you.

On 6/4/2009 10:55:39 AM Will S. said ..

Mr. Malik, the picture referenced in point #9 has a broken link to it. Thanks for the great post!

On 7/6/2009 11:23:59 AM Tahir said ..

Hey,

I am unable to see the last image in the tutorial and I have hard time formatting the data. Any help?

On 8/4/2009 12:33:34 PM Jerry Bekken said ..

This is very interesting. Can this application be performed from a parent site to a child site and visa versa? For example, I have a list which contains all of my test event data (which is for my branch), I would like to have my parent site be able to pull the data from the child into the parent. There are also several other branches which manage their own unique test events, so all the data from branches (child sites) ideally need to be aggregated into the parent site. I know I can get Bamboo parts to do this but will this method work?

I also cannot see the final picture.

Thank you for your help.

Jerry

On 8/19/2009 9:38:15 AM Savin said ..

This is very interesting!!! I'm wondering if I might use a similar approach to solve an issue I'm having. Firstly, I'm using a hosted Sharepoint WSS 3.0 site (no MOSS). They have turned off the ability to use new Web parts as well. What i want to do is:

Show a list of items (poentially with columns from a couple of tables) and then allow a person to double click on one of the lines and be taken to another list that used information from that line in a filter. For example, I might have a list of products and when I click on them I want to see a list of other those documents (in the Shared Document List) that apply to that product.

The resultinglist should act like a normal list of documents in that I can check out, edit, download etc.

Any ideas how this might be done?

Savin

On 8/26/2009 9:23:58 PM Strongbo said ..

=================================================================

On 12/4/2007 10:46:29 AM MARCOS ROJAS said ..

PLEASE I NEED HELP

ALWAYS PROGRAME IN VB5, AND NOT WANT TO CHANGE THE VERSION.

BUY NOW AN LATOP THAT HAVE WINDOWS VISTA, INCOME AND WHEN A "COMPONENTS" I DO AND CLICK ON "MICROSOFT FORM 2.0 OBJECT LIBRARY" CLICK AND DO IN OK ME HE DENIES PERMISSION.

I DO THAT?

=================================================================

... U are a legend MARCOS ROJAS .... try VB 6

On 10/21/2009 11:13:18 AM Chris said ..

I've been using this method for awhile and it works pretty good, but I've been asked to create a page with a joined DVWP that is filted by a column in the joined list.

I cannot get the filter to work on anything in the second list (work fine on the first list).

There has to be a way to filter and group items from the second list. How is it done?

On 12/8/2009 3:03:37 PM Lance Taylor said ..

The image UL in Step 9 is broken, can you perhaps put image back up again so we can see the end-point solution? Thanks!

http://img207.imageshack.us/img207/7761/56273610xi4.png

On 12/8/2009 4:58:21 PM Sahil Malik said ..

Hey Lance -

To save on my bandwidth costs, I host the images on imageshack. They sometimes loose my images. To fix the one image above is just an inordinate amount of work considering the # of blogposts I have + # of images. I'm sorry :(, I wish I had a better solution for you and for everyone who misses the images (including me!).

S

On 3/18/2010 12:12:48 PM MologokoStudios said ..

XPath problem fix for a linked data source from another site:

istead of:

<xsl:variable name="Rows" select="../../../Member_Directory/Rows/Row[...

put:

<xsl:variable name="Rows" select="/dsQueryResponse/Member_Directory/Rows/Row[

<xsl:variable name="Rows" select="/dsQueryResponse/Member_Directory/Rows/Row[ @SharePointID = substring-before(substring-before(substring-after($dvt_ParentRow/@Author, 'ID='),'>'), '"') ]" />

On 5/20/2010 3:39:45 PM Pete said ..

As for the quote below, I too host images on imageshack and they are often lost. I just emailed the tech support team and they found them for me.

Thanks,

Quote

" To save on my bandwidth costs, I host the images on imageshack. They sometimes loose my images. To fix the one image above is just an inordinate amount of work considering the # of blogposts I have + # of images. I'm sorry :(, I wish I had a better solution for you and for everyone who misses the images (including me!). "

On 6/1/2010 1:36:59 PM Hamza Alshokani said ..

Thank you very much for this information!

why not have your own Youtube lessons?

ragards,

Hamza

On 7/14/2010 3:31:31 AM moncler said ..

As for the quote below, I too host images on imageshack and they are often lost. I just emailed the tech support team and they found them for me.

On 7/21/2010 5:55:43 PM Ben said ..

Great job. But I'm using SP 2010 and the link "Create a New Linked Source..." is not there. Is there a different way to do this on SPD 2010?

Ben

On 7/30/2010 1:02:13 AM Kylie said ..

If you click on your Data Sources link on the left the first item in the ribbon (top left) is Linked Data Source. I was able to follow this tute in SP 2010 up until you try to do item 8. and create a "Joined Subview" I don't get this option in my drop down. Just "Subview", "Item(s)" and "Formatted..."

Can anyone help??

On 8/5/2010 2:10:23 AM ss said ..

u will get option of joined subview only when u place ur cursor in front of any particular value .palce ur cursor in front of any vale then click on column name then option will appear

On 8/16/2010 9:44:09 AM Moncler Jackets said ..

As for the quote below, I too host images on imageshack and they are often lost. I just emailed the tech support team and they found them for me

On 9/1/2010 1:34:56 PM Perri said ..

Oh noes! It's a cliffhanger on #9's image. It would be great if you'd repost that image inspite of your bandwidth issues - heck, I'd host it for you if you'd just send me the image.

Thanks though for the run through on this. Hope it works.

On 10/4/2010 2:03:54 AM srinivasa said ..

This article is good and helped lot, but i have an issue if the lookup filed have multiple values. On joining i am not finding those values. CAn u plz help me, if u found soln plz email me

On 1/19/2011 4:14:05 PM Edward Joell said ..

As this post is so old I doubt I will hear anything back. But just in case, attached two lists. I inserted one list as a joined subview and got a horizontal multi-item table. But when I inserted the other list as a joined subview I got a vertical single item view. I have tried various ways to configure this in design view without success. I finally resorted to going into the XSLT code and configuring a horizontal table. But in working with the XSLT code I was utilizing field names in my xsl:value-of elements. However certain fields in the two lists have the same name. Yet there is only one copy of those field names in the Datafields element. How can I be sure I am getting the right field in my sub view?

On 4/14/2011 4:13:56 AM Derek said ..

One thing to note is that the join does not work on lookup colums. Sharepoint inserts some code in the field instead of the data and hence does not find the match.

On 4/19/2011 11:17:00 AM Monica said ..

Yes, Derek is right. In that case what is the solution if we want to join lookup colums?

On 6/7/2011 12:17:56 PM Jhonny said ..

Good morning everybody

I am a new SharePoint 2007 user. I am learning by myself.

It is amazing what I can do with SharePoint Designer. I would like to use this kind of joins between SharePoint Lists. I think that will help me a lot. I only need to know If I can have link to the original items so I would like to update from this new joined list. My problem is to gather items from many lists and from that new view or list, I have to update it. For example if I have an Item that comes from list1, I would be able to maintain it from the new view or list. I do not know if that is possible.

Thanks for any help or advise.

Jhonny.

On 8/11/2011 3:47:21 AM Harmeet said ..

Hi

Can we join lists in SharePoint 2003?

Waiting for reply.

On 9/11/2011 10:21:33 AM Alexa said ..

Derek and Monica may not still be around, but for anyone else with the same problem, the solution is at https://social.technet.microsoft.com/Forums/en-US/sharepoint2010customization/thread/b438e57d-fe99-439f-a052-aa86be84125a

Thanks Sahil! Four years later, and this post is still helping people out.

On 9/15/2011 5:24:16 PM Robert said ..

Can somebody tell me how to perform the join view in XLV (XSLT List View)?

On 9/29/2011 6:55:58 AM danan said ..

Hi Sahil, how to build CAML query with more than one Join ?

Thanks

On 10/6/2011 1:34:12 AM Patate Pilée said ..

Hello,

This really page worths a thounsand hours spent on the net!...

But it also raises many questions...

(Sorry to say that the last picture link in your tutorial is broken.

I would have liked to see the final result before deploying the solution)

I have my own questions ;)

1. Is this join list editable i.e. do I have access to edit most fields as with regular Sharepoint lists?

2. Will i find the same drop down menus in the merged list?

3.I need a Sharepoint solution shared/editable on two sites simultaneously. ex.: I have List A and List B. Should I have List C to merge all the data? Or should I create a third list that holds common fields only? I want to find the best solution to manage their respective branch list at a single point. What's the best to connect them together? ...Having it also updatable in Access would be a bonus...

Thanx.

On 10/13/2011 5:31:21 AM Christian Louboutin said ..

Perhaps you could write next articles referring to this article. I desire to read more things about it!

On 10/17/2011 12:16:06 PM film izle said ..

On 12/12/2011 4:12:35 PM Steve said ..

I'm curious how this is different than just joining two listviews through a connection. You select an item in List 1 and the corresponding entries in list 2 are displayed. This can be done in the browser without using SharePoint Designer.

On 2/13/2012 10:56:43 AM Deepti said ..

Hi,

I am trying to merge a KPI List with a custom list. The idea is that the custom list will have all the details of the project like the manager's name, a link to some documents etc.

The KPI List will have the project status.

I want to join these lists so that in a sigle row I see the project name, manager's name, KPI status etc..

I joined the lists by joining the lists, but I do not see the status indicators.

Please help me.

Thanks in advance.

On 2/18/2012 12:14:27 AM Puneet Banthia said ..

Hi,

I just want to know how to apply filters when data is coming from multiple lists.

I have created an application where data is coming from 4 lists(say,List A, List B, List C and List D),and created a DVWP where only SELECTED listitems from all 4 lists are joined. BUT when i apply filter and sorting on that DVWP,it actually applies to only 1 List(List A).

Please tell me how to apply filter and sorting on all the lists.

Thanks in advance.

On 7/10/2012 9:19:18 AM Ulf said ..

Hi all,

I just want to pass on a recommendation to all of you that are used to work with joins in SQL and now looking for something similar in SharePoint. There is a connector called Camelot .NET Connector that provides real joins (as one of many features) between any columns using standard SQL syntax, e.g. "SELECT * FROM list1 left join list2 on list1.column = list2.column AND .. WHERE.. ORDER BY ..".

Cheers

Winsmarts.com

Categories

Other material

Sound off but keep it civil:

Older comments..