Complex DVWP Display of Multiple Lists

Question

I've been asked to recreate an html table of links as SharePoint List data. It's a long list, with sometimes overlapping column data, and sometimes missing column data. The final result is a complex table displaying links of server environments for the developers to use as a quick reference.

I have the new data structure setup and is split between multiple lists within the same site something along these lines:

Environments list: Environment Name; Environment Status; Order("sort order"); etc.
Development list: Lookup to Environment Name; Hyperlink; Comments; Flag for same as Link as Test;
Test list: Lookup to Environment Name; Hyperlink; Comments;
Production list: Lookup to Environment Name; Hyperlink; Comments;
FTP & Logs list: Lookup to Environment Name; Hyperlink; Comments;

This allows for easy updating the Environments (add new, change status, etc) and have it "trickle" to the items that are reliant on them, as well as each environment to have anywhere from zero, to multiple, links for each environment.

Now I need to merge all of this data into one large DVWP - and so far I haven't had any luck. I've setup the data source to use all of the necessary lists, but the view never comes out right. It doesn't seem smart enough to realize that the data is related, though separate. I feel like I'm almost there, and just overlooking something simple. Or maybe I'm just going about this entirely wrong?

• I need to be able to flag one of the environments as a different status quickly and easly, and have conditional formatting that can filter or color-code to notify the users when environments are temporarily offline, etc.
• I need to be able to know that Environment A from Test is related to Environment A from Production, while giving me a blank cell where the other fields are empty.

I can do that fine without throwing this data source into the mix.

Here's kind of a mock-up of what I'm looking for as an end result:

[Environment Name] [Development] [Test] [Production] [FTP & Logs]
[Env A___________] [___________] [Link] [Link______] [__________]
[Env F___________] [Link_______] [____] [Link______] [Link1_____]
__(Env F cont...)_ [comments___] [____] [__________] [Link2_____]
[Env D___________] [Link flagged both_] [__________] [Link______]

Does that make any sense? I've seen tutorials on the basics, but haven't found anything that helps with this particular scenario. I've tried using join as well as merge on the data source, and neither seem to get me anywhere near close. If I can just get them to play nice in the same table, I can clean it up from there. Do I need to code this all from scratch, or does anyone have any recommendations that can get me at least part of the way there?

Answer 1

Figured it out! And the only code really needed was some HTML & CSS.

I found this video via this blog (which has a number of great points & recommendations in it).

Here's what I did:

1. In SharePoint Designer - created my data source using JOIN with all of the Lists involved.
2. Added a DVWP - initially only displaying the environment name (that's used as the lookup field on the other lists)
3. Added the CSS for .ms-alternating to set an alternating background color :)
4. Added a new table column to the right side of the table in the DVWP & gave it a title by entering text in the top header cell.
5. Clicked in the first empty cell below the new title and in my data source selected the URL & comment fields from my first list > Insert Selected Fields as... > Joined Subview
6. Left pane = my Environment name field; Right pane = the name of the field in this new list.
7. Cleaned up the formatting & table code that it added.

Lather, rinse, repeat #4 - 7 for each additional list! Add some flair via CSS and massage the HTML table code a little - and all done!