We have been spending quite a bit of time lately working with the various ways that SharePoint can interact with a SQL Server for displaying, adding and editing rows of structured data. Initially, we were hoping to extend SharePoint’s reach and save ourselves some time by quickly developing SharePoint-based solutions for tasks that would otherwise require a hand-crafted fat-client application. After we began studying these capabilities, two trends emerged: one familiar and one unexpected.
The familiar trend involved a series of frustrating setbacks – things that should have worked simply were not working. When I say “should have worked,” I mean things that involve documented features of SharePoint, External Lists and Business Connectivity Services. When I cay “familiar,” I am referring to errors which are difficult to determine, debug and correct but which somehow have something to do with permissions. While I can’t say for sure, I’m guessing that the demonstrations I’ve seen of these features involved a solution running with the permissions that most of us would never grant to an end-user. Two other members of my team have decided to slog through these issues, so I embarked on the unexpected journey.
Since SQL Server access from a Data View Webpart using a Data source works well, I decided to proceed with our effort to replace an aging payment system. The current system is a fat-client deal that stores information about vendors, allows for the creation of check requests and, once approved, feeds a system that prints checks and updates various accounts. The process is manual, up until the point of inputting the transaction data necessary to produce the request and print the check. In addition, the approval is manual (i.e. go fetch signature), the current storage of the supporting material is analog, and just about every step of the process requires the person performing that step to be in our office. We knew we could improve this process, we were planning to improve this process, but I don’t think we were planning to improve it enough. I don’t think we were planning to give SharePoint a chance to shine.
When I drew this process out on a clean slate, it became clear that only a small amount of the data involved actually needs to be stored in SQL Server. Obviously, we need to know how much money was involved, who the money went to and what account the transaction should be charged to. The other data; the vendor information, the approval chain, the information that would support analysis and help people prepare budgets, none of that needs to be locked up with the transactions. We needed to consider what we could do if we kept that information in SharePoint. For starters, we could handle the entire human part of the transaction in SharePoint. Maintaining vendors, entering payment requests, allocating the requests to the appropriate accounts, submitting, reviewing and approving can all be performed against SharePoint list items. The good news is that if we build that process in SharePoint, we can start, review, submit and approve it from anywhere. In addition, the Data View Webparts we would use to drive this process run well and look great on our iPads, in fact, they run well and look pretty good on my iPhone. Even better, if we store that data in SharePoint lists, people can review it themselves, without the aid of someone who can write SQL or who has access the back-end server. Of course we have to take care of some details, but I think we can handle those too. As far as I can see, we have to:
- Control access to the process
- Upload, attach and store invoices and supporting documents
- Select vendors, departments and valid accounts from predetermined sources
- Provide a mechanism for requests to be submitted, approved (or rejected) and perhaps approved (or rejected) by a second person
- Notify the appropriate person (people) when action is required
- Prevent duplicate processing and/or alterations after processing has occurred
- Perform some basic math and alert people to errors
- Write specific bits of valid and approved transactions into the backend SQL Server
We can do all of those, we can do them in SharePoint, we can script it all from within SharePoint Designer, and we can give our users an experience they have never enjoyed with that fat-client solution. We can personalize the page for them. We can show them open items, let them review recent items or historic items. We can let them sort and filter those items by vendor, by date, by account and we can track the process of those open items if they like. In other words, we can leave the information people want in a place where they can easily gain access to it, and we can move the critical data where it belongs. At this point, we are working on proof-of-concept test pages. Things are going well, but suffice it to say, this topic begs a Part-2.