We built the UI—the form—by using
InfoPath 2007, which allows us to embed
the business logic into the form so that
when an editor works on metadata for a
specific package, the only fields that appear are those needed by the appropriate outlets.
The form also makes sure the data complies
with the datatype and content requirements
of the video distribution application.
So that we don’t have to deploy the
InfoPath client on our systems, we’re using
InfoPath Forms Services, a feature of Microsoft
Office SharePoint Server (MOSS) 2007
Enterprise Edition, to render the forms in
users’ browsers. When a form is saved, the
data is stored in a SharePoint list, and a
custom application page uses a workflow
to publish the data to the XML format that
the video distribution application requires.
This application is the most heavily coded
application of the four SharePoint solutions
I’m describing in this article, and even this
application took two smart folks from Net-
Fusion, a Microsoft Gold Partner, less than
two weeks to create. It would have taken
months without the functionality that Info-
Path and MOSS provided.
I’ll create custom views of the SharePoint
list, add alerts, and create Microsoft Excel
reports linked to data in the list. These tools
will allow editors, producers, and management
to perform their responsibilities related
to the process. So if you watch any of the
Games on a device other than a television,
know that SharePoint helped it get to you.
Transportation Management
Speaking of applications that could have
taken months to create, let’s talk about the
transportation management application we
developed in just a few days, without ever
opening Visual Studio. NBC’s thousands
of employees, contractors, and vendors
need to travel all over Beijing. And you’ll
know when watching the Games what a
masterful feat it is to move team members
from point A to point B. The transportation
group consists of a handful of talented folks
who must coordinate hundreds of rides every day for more than three months and
oversee as many as 200 vehicles and drivers.
We had to provide a way to make it all easier
to manage.
The transportation management application
needed to allow any transportation
team member to enter information about a
ride request, assign a vehicle and driver (a
“transfer”) to fulfill the request, and enable
monitoring and reporting of the transportation
group’s activities. In previous Games, all
ride requests were submitted by phone. This
time, we wanted to let users submit requests
online. Because SharePoint can use alerts
and workflows to notify users when data
changes, I wanted the application to notify a
user when his or her ride request had been
assigned a transfer, so that the user would
know which vehicle to look for, when the
ride would depart, and who else was going
on the same ride.
The challenge with this application
began with the data tables. To properly
support the application, we needed tables
with vehicle information, ride requests, and
locations. The transportation team needed
to be able to easily pull up information
such as addresses, maps, and even photos
of buildings. SharePoint isn’t built to create
or support relational databases out of
the box. However, with Microsoft Access
as a client, you can create an application
that provides, through queries, the ability
to relate data in various lists and delivers a
rich, form-based interaction with that data.
Traditional Access applications had data
in tables within the database (.mdb) itself.
Many IT pros have learned that you can gain
a lot by putting the data on SQL Server or,
now, a SharePoint server instead, and use
Access only as the front-end application.
There are several ways to build Access
applications that are front ends to Share-
Point lists. You can build an Access database
and use the migration wizard in Access 2007
to move tables to a SharePoint site as lists.
Or you can create the lists in SharePoint and
pull them into an Access database application
as linked tables. I chose the latter
method.
Access then lets you create queries and
rich forms; any additional functionality you
need becomes an Access programming task
rather than a SharePoint programming task.
For example, I wanted to give the transportation
team a way to identify which of the 200 vehicles were available at a specific place and
time to fulfill a ride request. This required a
fairly sophisticated SQL query, which I built
in Access with help I found via my favorite
Internet search engine. I also wanted fields
to autopopulate data to reduce data entry for
users and transportation managers. I accomplished
this with simple Visual Basic for
Applications (VBA) code behind the Access
forms. In less than three days, I had a rich
application that will support our complex
transportation management needs.
But I also wanted to provide some of the
“nice to have” functionality for this application.
First on my list was a Web form for
users to submit ride requests. This was easy
thanks to SharePoint Designer. I created a
new Web form, dragged my SharePoint list
onto the form to create the data connection,
then modified the Insert template, which is
the form rendered by the server when a user
creates a new record. Within an hour, I had
the form I wanted.
The next step was to create alerts for
users. I created a custom view of the ride
request list, called My Rides, which filters
the list to look for items for which the Created
By field was equal to [Me], a special
token in SharePoint that translates to the
current user. So when I go to the list, I see
only my rides, and when another user goes
to the list, she sees only hers.
One of the lesser-known features of Share-
Point alerts is that when you create a custom
view, you can generate alerts, based on
changes to data, that appear in that view. So
your view can be a filter for your alerts. This
feature isn’t well known because the option
for creating a view-based alert appears only
after you create a custom view, so you might
not have ever noticed that option. After I created
the My Rides view, I assigned the view to
all users. Now, when anything changes about
a ride for a user, the user will receive an alert
with the appropriate information. We’ll be
testing the alert-based notification for a few
weeks. If it doesn’t meet our needs, I’ll use
SharePoint Designer to create a custom workflow
to achieve the same email notification.
I learned some important lessons from
building this application. Because SharePoint
doesn’t support relational data very well, I
needed to denormalize some data points.
That is, I have some redundant information
across tables. For example, when a user’s ride
request is fulfilled as a transfer, the vehicle number is entered into the transfer table and
into the ride request table. Code within the
Access form enters the data automatically, but
it’s redundant. That was necessary to give the
correct experience and information when a
user visits the site online or receives an email
notification. SharePoint has lookup fields, but
working with them can be difficult (e.g., there
are places where doing searches, sorts, and
queries based on their content isn’t so easy).
So, I cut my development time significantly
by entering information into two fields—a
lookup field, which lets users jump to related
items via a link when visiting the application
online, and a normal text field, which is easier
to manipulate programmatically and to generate
views and queries.
Using Access as a front end to a Share-
Point application isn’t the right solution for
every challenge, but since we had a limited
number of internal transportation managers
who need to interact with the data in
a rich way, we can deploy Access to their
systems. All other users will be interacting
with the ride request form and with the ride
list online, and with email notifications. We
could have created part of the solution using
SharePoint Designer and its powerful Data
View Web part, or we could have opened
Visual Studio. But we met our requirements
with an easy solution within which the only
custom code is related to automating the
process and reducing data entry.
On to the Games
These four applications demonstrate some
interesting uses of SharePoint to solve problems.
I’m writing this article a few months
before the Games, and by the time you read
this, we’ll be running full speed toward the
Opening Ceremony on August 8. We’ll have
learned even more by then, and you can
learn about these applications at my blog, share.intelliem.com/cs/blogs/danholme.
Register
