Even though the goal for the Beagle Article was to introduce you to one of many ways you can unleash the power of SharePoint designer with DataView Web Parts, to show how you can create a business solution without custom coding and in a minimum time-frame. I originally picked this scenario just because most of you who are working in medium- to large-sized companies can relate to this topic.
As it turned out, most of you were actually looking for something to solve this particular business need.
So.... Due to the overwhelming demand for a "FULL" version of this solution.. without further interruptions.....(drums)... here it is...
The following solution will help to facilitate class scheduling and management of attendees by providing employee self-signup functionality and attendance approval administrative interface.
Let’s review the functionality required. An employee of the company goes to the training site and sees all available classes that were scheduled by trainers ( Figure 1). With a click of a button they sign up for the class of their choice and are presented with a list “My Classes”, this list allows employees to keep track of classes they have signed up for. All classes scheduled will have a limited number of seats, and people that sign up for a class will have to be approved by a trainer or a manager. Once the person gets approved, the number of seats gets automatically updated to reflect the number of people attending the class.
Figure 1
The site that this solution will be built in, is created using a team site template. Create a calendar list named “Classes”, this list will be used to schedule classes, track number of seats available, and assign trainers to a class (Figure 2).
Figure 2
Only four fields in this list need an explanation:
1. Seats – this column will hold a number to represent a total number of seats available for this class.
2. Filled seats – this column is going to hold the number of attendees that signed up for the class a have been approved by a manager, the default value is “0”.
3. Seats Increment – this column will be used to increment “Filled Seats” column. This is created as calculated field. When an attendee gets approved by a trainer, Filled seats column will be updated with this column value which is [Filled seats] +1. (Figure 3)
4. ClassID – this field will hold the same value as the actual raw ID. It will be populated by a workflow on creation of an item in the list. This field will be used to identify the raw in Classes list to increment “Filled seats” column when attendee gets approved to attend the class.
Figure 3
Open this site in SharePoint Designer and create a workflow based on Classes list, verify that the workflow starts on new item creation, set field “ClassID” to “Classes:ID”, or to Current Item ID.
Second, create a custom list called “Attendees”. This list will be used to keep track of people signed up for a particular class (Figure 4).
Figure 4
1. Title – Title of the class employee signed up for.
2. Start Time – Start Time of the class employee signed up for.
3. End Time - End Time of the class employee signed up for.
4. ClassID – this column will hold a raw ID of an actual item from the Classes list and will be used as a link between two lists.
5. Approval – this column will be a choice column with following values: Pending, Approved, Rejected. The default value in the field will be “Pending”.
These columns will be populated with information by a workflow triggered by an employee clicking the “Sign Up” button.
Open dafault.aspx page in SharePoint Designer.
Insert DataView Web Part onto the default.aspx page. Choose “Classes” list as your Data Source. In “Common Data View Tasks”, click “Edit Columns” and select Title, Start Time, End Time, Location, Trainer and Seats as displayed columns (Figure 5).
Figure 5
Once the Data View Web Part is rendered, add a filter to the view where “Start Time” is equal or greater than [Current Date], there is no, or little value in allowing users to sign up for a class that has already passed. In the DataView Web Part insert an extra column to the right of the “Seats” column. Open “Toolbox”, drag and drop “Form Action Button” control into this empty column. SharePoint Designer will then prompt you with a “Form Action” menu. Double click “Custom action” and click on “Settings”.
This action will bring you to the SharePoint Designer and create a new workflow that will be attached to the button. Create a workflow variable called ClassID as “List Item ID”. We will use it to pass the selected Item ID parameter from the DataView Web Part into this workflow.
Next, create an action that will trigger “Create New List Item” in the “Attendees” list. The title will be a value from the “ Title “ field in Classes list where Classes:ID equals workflow variable “ClassID” . Next, you will add a “Start time” field and set it to Classes list’s Start Time field (where Classes:ID equal workflow variable “ClassID”). Repeat this routine for population of the End Time field value. Add ClassID Field from the Attendee list and set it to workflow variable “ClassID” (Figure 6).
Figure 6
Save the workflow. In Tag Properties of your Form Action Button, change the value property to “Sign Up”. Go to your “onClick” event. It should read something like this:
javascript: {ddwrt:GenFireServerEvent('__workflowStart={{7696F6EA-2079-4A53-A97B-CE61D99A02AB},New,{66402E3B-C94E-4E6F-9936-10E596CC5795},}')}
Modify it and add the following (see addition in red color):
javascript: {ddwrt:GenFireServerEvent(concat('__workflowStart={{7696F6EA-2079-4A53-A97B-CE61D99A02AB},New,{66402E3B-C94E-4E6F-9936-10E596CC5795},ClassID=’,@ID,’};__redirect={MyClasses.aspx}'))}
This change will allow you to pass an input parameter into a workflow and redirect your employee to MyClasses.aspx page that has attendees list view filtered by Created field equals [Me].
Create My Classes view in Attendees list and add this to MyClasses.aspx page, don’t forget to save your default.aspx as well.
The next part of this solution is the creation of the administrative interface that will be used to manage attendees and classes.
Create another page “ManageClasses.aspx”. Insert DataView Web Part with Classes list as the data source, then insert another Dataview with Attendees list the Data Source. Right-mouse click on Classes DataView and select Web Part Connections. Choose “Send Row of Data To”, then “Connect to a Web Part on this page”. Next, select Attendees Web Part and choose “Get Filter Values From”. From the Attendees side, select ClassID and from Classes select ID (Figure 7).
Figure 7
Finish this wizard and save the page. Next go to Attendees’ Data View Properties, select “Editing Tab”, Check “Show edit item links”. Save the page.
Create a new workflow and attach it to the attendees list.
The workflow should start on an Item change event. As the next step, create a condition “Compare Attendees field”, choose “Approval” field equals to “Approved”. Under this condition create an action that will update list item in “Classes” list. Select “Filled seats” field and set it to “Classes” list “Seats increment” field value (If you remember, we set this field to a calculated value of “Filled seats” column plus one) where Classes:ClassID equals to Current Item’s ClassID (Figure 8).
Figure 8
As the next step add another branch to your workflow, set the condition and check for “Approval” field to be equal to “Rejected” value. In actions menu use send an email notification to the user who created the current item, this email will notify the attendee that their attendance for the class has not been approved. Add another action to this branch to delete the current item.
Here is how your workflow will look at the end (Figure 9).
Figure 9
Let’s review the solution and recap.
When a trainer goes to ManageClasses.aspx page and clicks on a particular Class, he/she will be presented with a list of people that signed up for this class with ability to approve/reject the attendance for chosen class(Figure 10). When attendee is rejected, attendee receives an email notification and the record gets deleted from Attendees list. If attendee gets approved the number of seats filled for this class gets incremented.
Figure 10
Both groups benefit from this solution, SharePoint users are able to sign up for a class with a single click of a button, and training managers have simple to use interface to quickly and efficiently manage classes and attendance.
ENJOY!
