Head Bone Connected to the Neck Bone

imageRemember 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.


We are working to recreate a time tracking system that we moved into SharePoint earlier this year. The SharePoint version solved several problems, but as is often the case with new ventures, it also surfaced a few new issues. I am going to parse our lessons out over a short series of posts; today, I am going to revisit an old enemy – Lookup Columns.

With SharePoint in its third major release, the fact that the out-of-the-box incarnation does not support banging a Lookup Column against a filtered view really makes me scratch my head. Actually, when I think of all the things I can do, the fact that I can’t do this kinda makes my head hurt. Before you send me to all the blogs that explain how to do this, all the things I can configure in SharePoint Designer and all the products I can buy, STOP! I have been to the blogs, I know how to do this in SD and I own at least one of those products. It still bothers me that this isn’t O-o-B behavior, because if it was, more of my users would build SharePoint solutions on their own. This is not a feature that should require heavy lifting!

The list at the center of this effort is a list of projects that our attorneys charge their time to. The full list of projects is large and too unwieldy to plop into a drop-down. They want to drive the lookup from a constantly changing list of “Active Projects”. In the opposite corner are the accountants who want to see any project that has had time charged to it in this fiscal year. SharePoint’s new ‘Restrict Delete’ feature doesn’t help me because the lawyers want to delete items from the list driving the Lookup Column and the accountants say “no!”

The simple approach is a Yes/No column in the list called ‘Active’ – would that it were that simple. I found several solutions that allow me to put a filtered drop-down list on the New and Edit forms using SharePoint Designer. I was planning to use that approach until I discovered two things. One, judging by the comments on those blogs, these solutions all had some sort of problem. Two, one of our attorneys works out of the Data Sheet view and never uses those forms. In addition to those problems and other potential problems, I realized that the attorneys might want to reference Active Projects elsewhere; I needed a much simpler solution.

We opted to go old school on this. We created a second list called Active Projects. When projects are added to or edited in the master list, a workflow updates the Active list accordingly. To appease the accountants…ok, that’s not a fair way to start this sentence. To meet the accounting requirements, when a time entry is made, another workflow copies the relevant data out of the Active list and stores it in Text columns where it will remain forever. This approach seems to make everyone happy, and it has several benefits:

  • It works – Attorneys only have to pick from active projects but accounting never loses any details.
  • It scales – The attorneys can build more solutions off that same Active Project list and they can do that without IT’s help.
  • It works everywhere – The Active Project lookup works in the Data Sheet view.
  • It is more useful – All of the solutions that created a filtered lookup list left us unable to fully sort or filter the resulting Time Entry list on that column. Having the detail information in Text columns allows full sort and filter capabilities in the resulting list.

I have to admit, I was pretty proud of the option using a Filtered Lookup drop-down list in the New and Edit forms. Those forms would solve the problem in an elegant and technically interesting manner. The only problem was the fact that the user didn’t want to use the forms. I also like some of the more elaborate options that provide a Filtered Lookup Column, but they result in a less useful end product. Again, the people using the list that we are trying to build want to be able to sort and filter that list. I’ve always said my job would be easier without users, but I guess that is ultimately self-defeating. I learned a lot working though the early stages of this small project. I’ll be sharing some of those other lessons next week.

Take Two

clip_image002On Monday, my small development team will take on our first SharePoint project. Whaaaaaaat? I know, I have been writing this blog for over a year and a half, and I have been talking about SharePoint since 2006, but most of our work to-date has been the result individual efforts. More accurately, our success has been the result of a combination of loosely aligned individual efforts. Maybe a group of people decided what we needed, but one person usually ended up with the “you’ll set this up in SharePoint?” task. Fortunately, SharePoint has matured, and our efforts to get it accepted as a platform for business solutions are starting to pay off. Our understanding of SharePoint has also matured and, not surprisingly our first team effort is going to be to redesign and rebuild of an existing solution.

What We Did Right – Every time you rebuild a “system” you need to consider what worked and what didn’t work. I like to start with what worked. The main thing that we did right was to move this system into SharePoint. We are collecting attorney time entries so we can better allocate expenses to individual projects. Seems simple enough for SharePoint, and since almost every other technical thing our attorneys do involves SharePoint, it was certainly a good place to put the application. Apparently, the only other thing we did right was to decide up front that we would collect time entries for one year and then start a new list. Initially, that was to accommodate SharePoint 2007’s limits, but it works now that we realize we really do have to start over.

What We Did Wrong – In fairness to me, we only made a few mistakes, but since this is such a small process, they are obvious. The first mistake was getting a little bit ahead of the curve. Our little process made use of several Lookup Columns before Microsoft added the ability to prevent people from deleting values that are in use. If you delete the item from the supplying list, the column in the consuming list goes blank. Our second mistake was not understanding why they would want to delete these items in the first place. Our third mistake was forgetting the myriad ways SharePoint solutions can fail. I want to talk about this third mistake, because it highlights the major difference between building a fat-client or web-based system and building a system across SharePoint – decentralized control.

In a fat-client solution, the stuff I’ve been building for thirty years, you collect transaction data through a well controlled user interface. The supporting data, lookup tables, references and validation rules, are tucked neatly behind the scenes, out of harm’s way. In SharePoint, all of that data is visible to someone. No matter how well you set things up, if you are building a solution on a user’s site, they have access to it – more access than you would give them to SQL Server. Of course, that’s the whole point, it is their data; they want to be able to use it and they want you to use it. We built this solution partially to eliminate the need for our attorneys to maintain duplicate lists. The challenge with SharePoint is that I can break a Lookup Column by deleting an item, by deleting a list, by changing the permissions on a list or by moving a list to a new location. All of these actions are valid things for a user to do in SharePoint, so they all have to be dealt with. I could build a separate solution in SharePoint, where all of this was locked-down, but that would defeat the purpose; that would be simply putting a fat-client inside SharePoint.

We need a better solution, and that’s why I am bringing two more people onto this team. I built this solution from the point of view of the attorneys, so one of the people I am adding to the team is going to represent the accountants. It’s not that I ignored the accountants the first time through, but it’s safe to say I could have done a better job implementing their requirements. The second person I am adding to the team is the guy who is going to document this process. Part of the reason things went wrong in the first version is that people didn’t understand how changing something in one part of SharePoint could affect something located somewhere else. In building this revision, we will document everything everyone needs to know, and we will put that documentation in front of them. We have a pretty good working solution today; next week, we are going to make this a great solution!