Displaying data from 2 list collections

eric · **Posted:** Wed May 11, 2011 10:30 am

The title might not be very accurate to this, but I was struggling to find the right words. Here's the situation.

I've got a list called Labs with a person/group column that defines the data owner. I've got a Requests list from which one of the fields is a look up column to my Labs LabID column. I'm trying to build a DVWP that shows all the items from the requests list where the data owner is the current user. In SQL it'd be something like:

Code:

select * from labs where labid in (select labid from labs where data owner = 'user')

What I'm struggling with is how to set this up in a DVWP. I can't simply use the Created by person on my Requests list becasue requests can be input by different people. The idea is to allow a lab/data owner to see everything that has been submitted for all of their labs.

I'm wondering if it can be achieved with some sort of advanced filter or with specific CAML queries on the 2 data sources.

bhorner · **Posted:** Fri May 13, 2011 10:32 pm

Well, here's a start:
1) Open SPD and go to the Data Source library tab and create a new linked data source
2) As a part of creating hte linked data source - you will see a little 'edit' menu next to each data source that will let you alter which fields are gathered from the data source along with placing your own filters (like adding a 'filter where x person/group column is equal to current user)...by filtering this it automatically creates the CAML necessary to pull it off right
3) When you create the dvwp from this linked source you just add the ID column or a single column from the first data source
4) You then insert all the request fields you want to see as a joined subview
5) If you are familiar enough with it - go through the code and make sure that nothing actually ends up appearing in the dvt_body - just make sure that the $Rows parameter passes through and - in the dvt_rowview where it's bringing in the joined subview and you won't have to display anything from the main list

eric · **Posted:** Mon May 16, 2011 8:14 am

I've created the linked data source but nothing is coming through when I know there are items that should be. This leads me to believe there's an issue with the CAML or that the DVWP can't interpret data like in the above simple SQL query example.

bhorner · **Posted:** Mon May 16, 2011 10:36 am

Did you filter in the CAML (aka via the setup in the data sources) or did you add filters via the UI later?

eric · **Posted:** Mon May 16, 2011 11:04 am

I did it later since through the linked data source interface, you can't specify [Current User] as a filterable condition on a person/group field.

eric · **Posted:** Mon May 16, 2011 11:40 am

Ah, I see what you mean now. Honestly I think this is the first time I've had to create a joined subview. It works so long as I don't try and filter the data ower equals the current user. I need to do that or there isn't a reason to do a joined view, I'd just show the full list instead.

sympmarc · **Posted:** Mon May 16, 2011 2:18 pm

You can definitely filter by CurrentUser. If you create a simple DVWP and add the filter for the current user, you'll see what SharePoint Designer does in that case. Then you can apply the same logic to your DVWP.

Fr instance, I just dropped a DVWP for an Announcements list onto a page and filtered for items created by the current user:
<DataSources>

Code:

   <SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;Created&quot;/&gt;&lt;Value Type=&quot;Integer&quot;&gt;&lt;UserID/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="Announcements1">
      <SelectParameters>
         <WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{29F0100D-C922-4573-BFFC-4525873444D4}"/>
      </SelectParameters>
      <DeleteParameters>
         <WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{29F0100D-C922-4573-BFFC-4525873444D4}"/>
      </DeleteParameters>
      <UpdateParameters>
         <WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{29F0100D-C922-4573-BFFC-4525873444D4}"/>
      </UpdateParameters>
      <InsertParameters>
         <WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{29F0100D-C922-4573-BFFC-4525873444D4}"/>
      </InsertParameters>
   </SharePoint:SPDataSource>
</DataSources>

Note how the selectcommand is now set up.

SPD always creates a parameter for the CurrentUser called UserID.

M.

eric · **Posted:** Mon May 16, 2011 2:24 pm

I know that, and it works when you don't use a linked data source. As soon as you configure a linked data source, stuff gets basically unsortable/unfilterable.

The goal is to get all the labids from my Labs list (which is a lookup column) that a user is defined as a data owner and display all the results from my Requests list which came from those labs (hence the SQL query as an example).

I've tried using bhorner's suggestion which didn't work as I thought it should. I've tried replacing the CAML on my Lab list reference with CAML generated from U2U that returns proper data, but neither of these return any data. It's just an empty web part.

sympmarc · **Posted:** Mon May 16, 2011 2:34 pm

Then you'll need to do filtering in the XSL rather than just in the DataSource CAML.

For example:

Code:

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[substring-before(substring-after(@Author, 'ID='), '&quot;') = $UserID]"/>

M.

eric · **Posted:** Mon May 16, 2011 3:04 pm

Reading over information I thought might have been my problem. I've tried it at the row level, stripped of all the HTML wrapper and not. As soon as I apply filtering, the data view returns nothing. I'm really at a loss at how difficult and unintuitive the process is to show data for lists that Sharepoint itself links together via lookup columns where you need to do more than just display data.

The link on your blog post to Laura's web cast is fairly basic and I, like many of the other commenters there, see a ton of issues when trying to sort/filter the data.

I'm about ready to to try pull the data into SSRS and do it that way. Please tell me this is better in 2010.

Stump the Panel: SharePoint Q&A

Forum rules

Displaying data from 2 list collections

Who is online