Head Bone Connected to the Neck Bone

Remember that song, Dem Bones? All those connections, from toes to head and back again; it was a fun way to think about our skeleton, and as long as I don’t hear my doctor humming it, it’s a fond memory. As it turns out, connections have continued to play an important role in my life. With respect to my skeleton, it’s the connections that caused the problems that sent me to physical therapy. With respect to people, it’s the connections that allow me to enjoy life, and get my job done. With respect to SharePoint, well, sometimes connections in SharePoint help us do remarkable things, and sometimes they send me looking for therapy.

Recently, we have been experimenting with the various ways we can connect SharePoint to the structured data in our SQL Server. There are two primary reasons for wanting to exploit this capability. One, we can enhance the value of our content-centric applications on SharePoint when we can add bits of data to the picture. Two, sometimes, processes in SharePoint grow until they reach the threshold of being an application; nudging them over that threshold saves us the effort of developing another application. Here are two examples of the connections we are working on right now, and why:

One of our most complex content-centric applications is the solution that helps our engineers manage the documents associated with their loss-control inspections. The solution relies on a few supporting custom lists. For example, one list includes, among other things, the URL of the document library in the customer’s site on our Internet-facing server so we know where to put their copy of the report. Recently, a manager in our underwriting department asked if the underwriters can be notified when inspection reports are distributed. Of course, they could set alerts on these libraries, but they only want to know when one document reaches its final stage in the process. Since there is a workflow running, and a lookup-list, we could easily add the underwriter to that list and shoot him an email. Then again, underwriter assignments change, and those assignments are already being maintained in our policy rating system. Instead of manually duplicating that information in the look-up list, the workflow, with a little help from a function library that we are testing, can look up the underwriter in the rating system. If this library doesn’t work, the workflow can look up the underwriter in an External List rendered from the table in question; that would be duplicate data but not duplicate work, SharePoint would be doing the heavy lifting.

The second example features one of our earliest (and ongoing) workflow driven success stories, a time tracking solution that our attorneys use. Our first step was to automate the time entry, which relies on a series of related custom lists. For example, we use a custom list to let the attorneys identify which projects are active. By toggling that status, the pick lists in the entry forms are modified to only show valid choices. The evolution of this solution included a hand-crafted set of Data View Webparts to let the attorneys enter their time from their iPads and integrated SSRS reports to provide the summary data to the accountants. Of course, the accountants need to turn that summary data into journal entries. The time-honored method has been to read the report and type the entries into our General Ledger system. Starting next month, SharePoint will be making those journal entries, courtesy of some fancy script work by Marc Anderson, aided by the woman on my team who has guided this solution through its various stages. The last stage was not without a certain amount of drama. We ran into trouble trying to update an External List from the script. Perhaps the capability doesn’t exist, perhaps the feature is broken, perhaps we still didn’t manage to get the permissions right. However we can update the External List from a workflow. So, Marc put his journal entries into a SharePoint list and a workflow puts them into SQL Server via the External List. It sounds like a kludge, but it works well and it doesn’t require any additional input during the process. I do hope that at some point, Marc describes the details behind his middle-tier magic over on his blog; I’ll leave it at “it works, and we love it!”

Web Parts, Data sources, External Lists, related SharePoint lists; it’s not as easy as “the toe bone connecting to the foot bone”, but it’s actually not that hard. As I mentioned last week, we keep these solutions moving forward by taking small steps – sometimes we fail and learn, but sometimes we succeed.

Interoperability

How many lessons can you draw from a single 3-hour event? Hopefully, enough to make an interesting blog entry. I was going to write about an amazing last minute twist to our Inspection Report processing project, but I am not sure where that is going to take us. I know one thing; it will have a significant enough impact to cause me to update my presentation for AIIM Expo in three weeks (see sidebar). As exciting as that news is, I am even more excited about the event that I just returned from. The AIIM New England chapter sponsored an event on March 2^nd in Waltham, MA on the subject of Interoperability. In the interest of transparency, I’ll start by saying that I am a board member of AIIM NE. On the other hand, I was at this event because I was interested in the content.

The first speaker was Scott Jamison, CEO of Jornata. I missed the opportunity to hear Scott speak at SharePoint Saturday in Hartford, but I got lucky today. I was even luckier, because Scott’s topic was something close to my heart lately, SharePoint BCS and the ways in which you can expose and interact with back-end data in SharePoint. Scott talked about several concepts that serve as validation for the way we are approaching SharePoint, including the benefit of having everything a user needs within easy reach. Hmm, I think I’ve written about that before, oh yeah, proximity, that’s the tag. One of Scott’s customers joined him on the stage to show off a pretty cool application they have built. If the event had ended at that point, it would have been worth the ride; but it didn’t end there.

Next up, Cheryl McKinnon, CMO Nuxeo talked to us about broader interoperability concepts, efforts and capabilities. She introduced us to the Content Management Interoperability Services project (CMIS) and she talked about the work that has been done to help move that project forward. I know, this is SharePoint Stories, I’m a SharePoint guy, but wouldn’t it be nice to not have to worry about things like “what if my successor chooses a different content management system?” or “what if we wanted to share information with a client?” I suffered through the days of proprietary structured data, and I saw some relief through conventions like ODBC and XML so I know that work like this is important. I am grateful to companies like NUXEO, IBM, Microsoft, etc. and organizations like AIIM that take on these massive projects that result in benefits for everyone. I loved the part of Cheryl’s presentation where she sought to “remove the stigma around silos”. She talked about having grown up on the prairie and how silos were beneficial, how they protected the fruits of your labor. She made the excellent point that  silos aren’t bad, if you can access all of them.

Finally, it was great to sit through an event that wasn’t all SharePoint. I mean, I do love the product, but there is more to life – just sayin. The reason I mention that, is to highlight the benefit of AIIM. If you want to know more about SharePoint, there are at least a thousand SharePoint events a year. Some SharePoint expert is probably giving a SharePoint presentation right now, somewhere. If that’s not enough, there are more SharePoint blogs than there are stars in the sky (by the way, thanks for reading this one). OK, there are actually more stars, but only because they just discovered that there are three times as many stars than they once thought there were. If you want to know more about the things that SharePoint does – collaboration, business process management, content management, records management, etc. – you can’t find a better place to go than AIIM. Go to AIIM Expo and see all this stuff in one big room. Listen to experts and end users talk about all these fascinating subjects. If you just can’t stand to go a day without SharePoint, don’t worry, there is a ton of SharePoint at AIIM Expo. If you can’t make it to Washington, DC in three weeks, join your local AIIM chapter. The amazing event I was at today was a Chapter event, and we have four or five of these every year. Great events, with great speakers like Scott and Cheryl sharing their thoughts on important topics that you really should know more about.