SharePoint RSVP Tool - Using a Joined Data View
Posted by Allyis Blogger on Mon, Nov 09, 2009 @ 01:58 PM
By Justin Botchek
Sometimes when rendering SharePoint list data there will be a need to show related information from multiple lists. In those cases a single data source isn't enough. While building an RSVP tool for our company's internal SharePoint site, I was in need of a way to display events and links to individual employee RSVPs from separate lists. Using a joined data view, I ended up with something similar to this:
The first three columns are populated by an out-of-the-box SharePoint calendar list with an "RSVP By" date field added, and the last two are populated by a separate custom ‘RSVP Details' list filtered by the current user and the event ID. For this example, the RSVP Details list contains two custom columns: one choice column ("Answer") with radio buttons for "Yes" and "No', and a lookup column (Event ID) using the IDs from the calendar list.
To begin with, I created a standard multiple item data view using SharePoint Designer and the three columns from the calendar list shown above. Then I added the additional header for my RSVP status and a new empty column in the row template to contain the data.
To create the joined data source, there is a Related Data Sources button at the bottom of the data source details window. Clicking on it will present the option to link to another data source. There will be a warning about form functionality not being supported for linked data sources, but since this view is read only it can be ignored.
|
Next a window will appear that allows you to choose which data sources you want to link. You can use anything as a data source here (web services, lists, xml files, etc), but for this example I chose to add the RSVP Details list that I created earlier.
|
|
|
After selecting your data sources, it will ask if you want to merge or join them. For this example I joined the data sources so that they could be referenced independently.
|
|
|
After joining, the data source properties will now show data for both lists in their own query node. (Note: it helps to have some test data in both lists so there is something to work with here.)
|
|
|
|
|
Now to actually get the newly joined RSVP data in the view, I created a second XSL template with a reference to the parent row it is called from. Before placing a call to this template however, I needed to define the parameter for the UserID at the top of the data view code to allow filtering by user. I've added the line in bold here:
Then I added this directly under the closing template tag for dvt_1:
After that was in place, I added a call to the new template in the previously created empty cell of the dvt_1 row view. I used table tags to help with the separation of the links and RSVP status.
The bracketed code in the row variable declaration of the template is a combination of two filters that tell the data view to only include rows where the lookup column Event ID is equal to the ID of the event displayed on the parent row, and where the author of the RSVP item is the same as the logged in user. This is how I linked the appropriate item from the RSVP list to the event from the calendar list on each row.
When a user hasn't yet submitted an RSVP, the status row will be empty. The dvt_2.empty section of the new template is used here to provide a link to create a new RSVP item.
The href should be a relative URL to the RSVP Details list and at the end I've added the query string ‘EID' and passed in the ID of the event for that row. This query string can be used on the RSVP newform.aspx page to automatically configure the Event ID lookup column for the appropriate value. I won't cover the script used to set this value here, but it is detailed on the Microsoft SharePoint Designer team blog. In my project, I used a custom form with this field omitted from the view so that the user could not manipulate it themselves.
Once the user has submitted an RSVP, I used the row view of the new template to provide an edit link to it. Again this is a relative link to the RSVP Details list, but this time the ID value is relative to the row of the RSVP list instead of the ID of the event from the parent row. This allows the user to edit the RSVP item they've already submitted for the event on that row.
On my project I also filtered the data view to remove events from the list with RSVP dates that had expired, replaced the RSVP link with "RSVP not required" if the date was left blank, and added an invitee field to the event items to allow events to display for select individuals. There are many other things that can be built into this behind the scenes on a sub site (reporting, validation workflows, waiting lists, etc), but this simple joined data view serves as a convenient interface for the user.