Creating school websites with SharePoint part 5. Using the power of SharePoint lists.
In this final session on SharePoint websites I have used an extract from a paper I did with Chris Mckinley, one of our developers. This looks at how SharePoint lists are used to create Prospectus and Open Morning bookin and the 6th Form Application process.
The request prospectus page and the book an open morning page are custom SharePoint lists. The prospectus request form sends the data to then list then takes them to the pdf download. The page itself was created using the site actions menu. A layout was select with a content region on the left and a webpart zone on the right. This allows the text to me modified quickly from the browser. The form the end user fills out was created as an xslt data view in sharepoint designer and then exported as a webpart. Unlike the latest news where the whole page was created in sharepoint designer and rendered one list the approach of creating a form webpart was used to enable easy editing of the text on the left of the page.
The book an open morning page is very similar to the prospectus request page. One extra feature is the inclusion of a lookup column in the custom list. The field looks up date values from another list. This allows easy management of the dates as more can be added by admin staff simply by adding items to a list. The other way to do this would be to use a choice data type for the list but this approach would require the lists column settings to be modified each time a new data is added, editing the lists setting is something we wanted admin staff to avoid. Adding an item to a second list is a much easier and safer way of managing things.
We also use the SharePoint Alerts feature on the lists to notify the 6th form office staff that someone has expressed an interest in the 6th Form. This integration with Exchange 2007 means that the information is presented quickly to the relevant people without the need to constantly check another admin page for new content. By emailing out the alerts rather than relying on one member of staff to check an admin page gives us some human fault tolerance. If prospectus requests were only added to a list and the staff member dealing with the list was long term absent then data could be added to the list and not auctioned. With email alerts, an absent staff member would usually have mail redirected to a colleague, instantly bring the colleagues attention to the data in the list enabling it to be dealt with immediately.
Open morning back end
The application form opens in a new window. The application form is a custom SharePoint list containing many of the useful column types such as rich text areas for personal statements as well as date formatting to ensure reliability of data. There are also lookup fields with other lists such as the ‘Current School’, again allowing for admin staff to maintain the lookup data. The ‘Ethnic Origin’ column reads data directly from our MIS, this enables the dropdown list to be dynamic and contain only valid data. The application form was built in sharepoint designer and many of the fields could be simply dropped in. The ethnic origin field, as mentioned above, reads directly from our MIS. This is done by an SQL connection on the field to our MIS server where the data is pulled across. The data is pulled across using a stored procedure running as a specific user with very low privileges, just enough to read the required data, this ensures that the database connection cannot be used for malicious traffic.
We chose the approach of reading this data from another database because the MIS system automatically keeps the list of ethnic origins and the identifier codes up-to-data, something that would be impossible to manage with other sharepoint lists or lookup values. If the person filling out the form is a current Twynham student then we ask for their computer login number. This will then allow us to tie the person into our existing systems for viewing pupil data, this is done during the interview process.