clip_image002One of the things that I like most about managing a small technology shop is the agility with which we can operate. We are constrained by the usual suspects, i.e. the limits of technology, budgets and time; but most decisions are easy. If we like something, we can look at it. If we like it after we look at it, we can buy it (we usually don’t look at the stuff we can’t afford) and if we have to install something to a server or change a database, we can do that too. Long term readers of this blog know that I like to write about the good people we work with, whether they have developed an add-on product or have rolled up their sleeves and helped us to build something. One of the companies I have wanted to write about is HarePoint, but I wanted to wait until we could use their product to solve one of our most vexing problems – that day has come!

I learned about HarePoint several months ago when I was looking for better ways to work with dates in SharePoint workflows. While poking around their website, I discovered this list of Workflow Extensions. Some of the extensions looked pretty cool, including the ability to work with arrays, and those for manipulating lists, libraries and individual documents and images. By the time I read about the ability to execute a SQL Query from within a workflow, I was starting to drool a little. All of the extensions seemed cool, but one that really piqued our interest was the ability to move a document to a different library. That feature may sound like kid stuff, but it’s not as simple as it appears. We wanted to copy a document, along with some of its metadata from a document library on our internal farm, to a library in a site on our Internet-facing farm. We asked the people at HarePoint if their extensions could do that and they thought that they could. Unfortunately, our first attempt failed.

Some of the best vendors we work with have distinguished themselves when things didn’t work – HarePoint is now a member of that club. We told them what we were trying to do. We told them that it didn’t work, and we anticipated being told that, in retrospect, the feature wasn’t designed to move documents between farms. Instead, the support crew at HarePoint told us that they thought this would be a good feature to have, and they worked with us to make it work. It took a couple of attempts, but last week we were able to create a workflow that moves a document from a library in our engineering site to a library on our Internet-facing SharePoint server.

This isn’t just an interesting technical challenge; this is the final piece of an intricate puzzle that was mostly assembled over a year ago. You can read a ton about that project by searching this blog for ‘inspection’, but the short story goes like this: When our engineers write an inspection report, a series of SharePoint workflows marshal the reports through various reviews, updates a variety of metadata and stores a final copy of the report in a Records Library. The final step was always supposed to have been to create a copy of the report for our customers to access in a SharePoint site we provide for them, but that has remained a manual process – until today. We successfully tested moving one of these inspection reports from our library to a target library on our test farm. The problem seems to have been solved, and the implementation couldn’t be easier:


I want to publically acknowledge the technical support and development groups at HarePoint. The company has a great product, and these people went the extra-mile to make it even better. I’d also like to acknowledge the members of my staff who did battle with the ever present nemesis in SharePoint (permissions) to put these awesome capabilities to work. If you’re looking for ways to extend the functionality of your workflow-driven SharePoint solutions, you might want to take a hard look at HarePoint.

No Train Wrecks Allowed

clip_image002Earlier this week On several occasions this past week, we were trapped inside a Mobius loop of inactivity due to failures of network equipment that resulted from the failure of some AC equipment as well as ill-timed changes to our DNS servers. Having tossed most of our technology eggs into the network basket, we limped along without phones, email, SharePoint and even access to our shared folders. Of course, we have contingency plans, but at what point do you pull the trigger on a change that requires relocating people? I can’t answer that, but I know it isn’t an amount of time that is measured in hours, unless something has been destroyed – enterprises, even small ones, just don’t move that fast.

I am counting on Microsoft to understand this fact about enterprises. Consumer pressure to respond to social, mobile and usability issues aside, they can’t expect to drag corporate America behind them at warp speed. The enterprise will turn about as quickly as the Enterprise. As we sit here today, looking at Windows 8, SharePoint 2013 and the next version of Office, we are in no hurry to adopt any of them. Sure there are machines here that are already sporting the Metro soon-to-be-named interface, but they are R&D items. In the past, we have been among the early adopters of new technology, but this time, we are likely to drag our feet – here’s why:

Mass – Despite the recent wave of annoying technical problems, our current configuration works pretty well. SharePoint, Lync, Exchange, SQL Server, and a whole bunch of systems that use those technologies are humming along. Those systems are talking to each other, and people are getting work done. Most of the job descriptions in my department include references to “staying current” and “researching emerging technologies” but the core requirement of every position’s job description is one that deals with “maintaining technology that is critical to company operations.” Watching over the past week as people lost phones, Internet access and email, I was reminded as to just how important that requirement is. The next generation solutions don’t only have to be released to manufacturing, I need to know that they all work, that they all work together well, and that they support our installed base of software and solutions. Given the speed at which Microsoft is remaking everything, I’m thinking we might not get to that one-big-happy-family state until a bunch of Service Pack 1’s are released.

Motion – Projects don’t stop when new releases are announced, even when the new technology will impact the solution being developed. For example, we are working on a SharePoint-based solution that is using the Mickey-Mouse method of enlisting workflows associated with multiple lists to iterate over the items in one list. SharePoint 2013 is said to support loops in workflows, but that doesn’t mean that we are going table this current project until we are on SP2013. One reason is that I don’t know if the way SharePoint will support loops will do us any good. A second reason is, I wouldn’t be surprised to see the feature get yanked out at the last moment. Finally, I have users waiting to use this solution. We may tweak our design to make it easier to change our workflows once looping is available, but for right now, it’s full speed ahead.

Here’s an example of how mass and motion combine to create momentum: prior to Microsoft announcing their upcoming slate (no pun intended) of tablets, we standardized on iPads. By standardize, I mean we put one in the hands of 2/3 of our employees, so that’s the mobile platform we have to work with through 2013. We have promised those same people a very specific app later this year, and I am still planning to build that app in xCode.

Planning – Microsoft might be reluctant to give me a roadmap that extends past the end of this year, but I have to give my boss one that stretches well into the future. In addition, I have to complete my 2013 budget request within 45 days. It was this time last year that we decided to buy those iPads, and when I think back, I didn’t see any reason why I should have made a different decision. So, looking ahead, I see continuing progress on using SharePoint to help us manage digital content and to improve the effectiveness of certain business processes. Our plans are based on what we know we can deliver during 2013. I stopped writing lists of accomplishments that begin with “due to the later than promised release of…” a long time ago. Our hardware, software, storage and bandwidth budgets will all be predicated on what we know (in September 2012) will happen in 2013.

Avoid the Excel Trap

clip_image002Earlier this week, Marc Anderson called attention to an article in the Boston Globe about a company that is requiring all of its employees to learn JavaScript. That spawned some comments on Twitter which led to a blog post by Marc, which led to a comment by me. Before I start singing “there’s a hole in the bucket dear Lisa…” let me jump to the point. Systems development, of any kind, by anybody, for any reason, should always follow a few rules.

In my comment on Marc’s blog, I called attention to my three favorite development rules:

  • Design your solution following generally accepted principals of systems development
  • Test your solution thoroughly
  • Document your solution adequately

Now, I am going to add a fourth rule:

If your solution is even moderately complex, don’t build it in Excel!

Excel has been biting me in the hind parts, all week long. I have written several blog posts about the fact that SharePoint is a much better choice than Excel when building solutions that primarily store and lookup information. My suggestions sometimes fall on deaf ears, but when things go south, the problem-child spreadsheets often end up in my lap. Here are two that landed there this week:

Seek and Ye Shall Find – One powerful feature of Excel is the ability to write formulas that gather data from other parts of your sheet, other tabs, other spreadsheets, and even data sources outside of Excel. The problem is that these formulas work even if the lookup data is out of date. Earlier this week, we suffered a curious error due to the fact that someone left a 2010 version of a lookup list in a spreadsheet that had been updated to 2012. This is precisely why I prefer building these solutions in SharePoint and using External Lists or Data View Web Parts to access back-end data. These are live connections to the data and they don’t require the person using them to push “Refresh All” to make sure the list is populated.

It Should Work – One of the most powerful functions Excel has is vLookup(), the ability to find a value associated with an item by finding the item in one column of a table and retrieving the item of interest in an adjacent column. You specify the place to look, and the offset to use when retrieving. The place to look, is a Range of rows and columns either in the a1..c17 format or a defined Range Name. Unfortunately, when you add rows to the data you are looking things up in, you also have to make sure that the range gets expanded. If you don’t, you have the other error we experienced this week, the item isn’t found. Even more unfortunate is the fact that vLookup() doesn’t return “Item not found” by default, it returns the value associated with the last item it checked, which is the last item in the range. Here again, I find myself longing for a solution where one SharePoint list looking up stuff in another SharePoint list. I just have to point to the right list; I don’t have to define the top, bottom, left and right sides of the list.

I know that spreadsheets were the first “killer app”, and some say that nothing has come along on the PC that can rival them; I disagree. Spreadsheet use is out of control; people trust them to do too much work, and they ignore all the rules while building them. Every really complex spreadsheet that I’ve ever seen, was written while one or more people were “in the zone”. They weren’t really designed; they simply morphed into shape as needed. In addition, those solutions are undocumented or poorly documented, poorly tested and they remain in the same fragile state they were born in. Version control is performed by pressing “Save As” and appending a year, event, or people string to the file name. On top of that, I’d be willing to bet 50% or more of the spreadsheets in use today include at least one formula that has been overwritten with a fixed value.

SharePoint offers us an escape from these traps. Rather than try and force Excel into pseudo-database mode, we have a product that is actually is capable of supporting a solution that requires the power and reliability of a database and the control of a content management platform. On top of that, SharePoint gives us a wide variety of ways to extend solutions through workflows and programming. We can automate, protect, distribute and decentralize functions and features without exposing critical elements to random, unauthorized and undocumented changes. I know that it’s easy to reach for Excel. I know how tempting that row-column blank slate can be, but it’s a trap, pure and simple.

Form vs. Function

imageWhen we get firewood delivered, I usually poke around the pile to see if there are any interesting bits that might work better in my woodshop than the woodstove. The piece pictured below seemed to want to be a bowl. Unfortunately, as you can see it was difficult to expose the natural features and make it round. In turning it, I had to decide where to stop, as I was opening up more of the missing 80° gap with each pass. I stopped just before plowing through the undercut side, and ruining the piece. That left me with something between a functional piece and an artistic statement. Of course I’m sharing this because I see parallels to the discussion we are having at work.

Our current challenge is to decide which systems or parts of systems are going to be hand-crafted fat-client desktop applications (the stuff Microsoft has labeled ‘legacy’), which are going to end up in SharePoint and which will land on a phone or tablet. This is heady stuff, but like my bowl, I think our results will be defined by balancing our goals between form and function.

imageDesktop vs. Browser – Despite the legacy stamp, I can’t imagine anyone choosing to run transaction processing systems in a browser. I might enjoy the novelty of processing a transaction in a browser, but I can tell you from my experience with our expense reporting system, performing multiple transactions in a browser is painful. On the other hand, viewing certain reports in a browser as opposed to on paper has definite appeal. Of course we still need printed reports in some cases, but fewer than we have had in the past. Fortunately SharePoint and SQL Server Reporting Services (SSRS) lets us give that choice to the user. In between those extremes will be the functional elements that we can break away from the full-blown transaction. For example, allowing an underwriter to mock-up and request one of a few changes to a policy would make sense for several reasons:

  1. To add at these features to our rating system would be difficult
  2. These types of transactions occur less than 1/10 as often as complete rating transactions
  3. The component transactions appeal to several people and those people travel

Browser vs. Mobile – Notice that I’m not starting with Desktop vs. Mobile and for clarity, when I say mobile, I mean native mobile apps, not simply exposing a browser-based application on a mobile device. Until we get well beyond the point of the tablet replacing the laptop, I don’t see tablets or smartphones being the domain of transaction processing. I’m sorry, as much as people are all hyped up about being able to run full-blown Windows on a tablet, they aren’t going to replace laptops or desktops for processing transactions. You might be able to get everthing running, but who wants to work like that? For the foreseeable future, there will be a distinct difference between apps and applications. Apps will be rifle-shot accurate programs that allow for handling specific tasks from anywhere. Applications will remain shotgun style programs that cover a lot of ground from the comfort of a desk. Starting an automobile claim from an accident site, with a picture and a minimum of on-scene information is easy. Processing a complex cash receipt covering multiple transactions in multiple currencies isn’t going to happen while I am:

  • Using a handheld device, or any device that prevents simultaneously viewing several legible windows/widgets
  • In the absence of a horizontal work surface
  • Using a tenuous or unsecure Internet connection
  • More than 50’ from a printer

As with browser-based solutions, there are tons of opportunities to break out specific functions to mobile apps. The approval process is a great example. Approval is a rifle shot function, and it has to happen when it has to happen – a perfect match for a mobile device.

As I have mentioned in recent blog entries, we have been investigating SharePoint’s capability to fill the “in browser” circle of the Venn diagram I am imagining, and I am very happy with those capabilities. The key to SharePoint’s awesome potential is its ability to connect to the back-end data and the myriad ways it can manipulate and render that data. The person marshaling the transactions may want to be at a desk, the person approving a single transaction can use their phone, but the person who has an interest in the initiation or the results of those transactions will be very happy in a browser.

Baby Steps

clip_image002What do SQL Server, Social Media and SharePoint have in common? They are all things that we are learning, or still learning and they are all places where we just might make some mistakes. Earlier this week, one of my team members had to make a change to the structure of a table that we are using while testing our connections between SharePoint and our backend database. The change triggered that queasy-feeling-generating message about having to drop and recreate the table:

This young woman leans toward cautious; she started exploring the options for altering her approach. I’m not sure she appreciated my advice, which came wrapped in a question: “what’s the worst that can happen?” After all, it’s a test database. I tried to sweeten the deal by adding that “we might as well see if this works” and that even if it failed, it would “give our system administrator a chance to test his backup and restore process”.

While she was struggling with SQL Server, I was learning more about using social media in our business. I spent a good part of this past week at the Enterprise 2.0 Conference – Boston, now officially known as E2Social, listening to people talk about effectively using social media to promote or grow your business or using it to improve communication and collaboration within your organization. In all these sessions, one message came through clear – mistakes are going to be made. After reading this far, you probably won’t be surprised to learn that I’m not bothered by that fact; we’ve been making mistakes with SharePoint for years. The important thing to know about those mistakes is that none were very large, none cost our company any much money and every mistake was a learning experience. Nathan Bricklin, Head of Social Strategy at Wells Fargo summed it up in his keynote presentation at E2. I’m paraphrasing here, but the quote I loved was:

“if you take baby steps and fall, you can learn walk. If you plan a project for a year and fail, you won’t be able to figure out what you did wrong”

The other feel-good moment in his presentation was when he reminded us that: “…it’s like baseball, you can’t win or lose the game in the first inning” that quote might not have been all that well received at this point in Boston, but we all understood the underlying message.

In our office, we have been playing this game all year as we have been experimenting with the various ways we can connect SharePoint to our backend data. We realize that if we can tie structured data to unstructured data, we can automate, or support through automation, a much more significant part of the target business process. Unfortunately, trying to make these connections has been underscoring why the word “frustrating” in in my tag line. We have managed to assemble successful proof-of-concept pages, parts, lists and workflows for almost every feature that we need to use. We have run into almost every error you can generate (they all seem to be related to permissions) and we have solved or worked around most of them. In short, we have fallen, but we are learning how to walk.

I never have much luck when I predict what next week’s post will be, but if things go according to plan, I’ll be sharing some of the specific experiences we have had with our connection quest. If things go according to history, I’ll be talking about something totally unexpected that will happen during the next few days.

By the way, if you want to know how to make it possible to let SQL Server drop and recreate those tables, see Geoffrey Emery’s excellent blog entry from 2009. I discovered that shortly after we began our migration from DB2 to SQL Server. DB2 warns you when a table has to be dropped and recreated, but it lets you decide whether or not you want to make the attempt. Unless you change your settings in SQL Server Management Studio, you get the warning shown above, but you can’t actually proceed. I will add for the faint of heart, that once you make the changes that Mr. Emery outlines, the drops and recreations just happen, no muss, no fuss, but no warning.

Automating People

iPhoneRecently, I was discussing process automation with a few friends – yes that is the kind of life that I have, at least during the NFL off-season. One subject that came up during the discussion is the problem that is caused by automation that goes into a holding pattern. When processes were manual and routing and storage were analog, something was physically on a pile on someone’s desk, an obvious constant reminder of the work that needed to be done. Once we suck the content into SharePoint and wrap the process up in a few workflows, the constant reminder is replaced by a single email – do you know how easy it is to ignore an email? Of course, the just-in-time nature of workflows means that while person A is ignoring the task, persons B through G don’t even know the task exists.

Since I am frequently cast in the role of person A, I might be a good example. One of my tasks is to approve expenses added to a cloud-based expense tracking system. This system notifies me of every charge I make, every charge I have to approve and every subsequent status change during the lifespan of an expense. This system has proven beyond doubt that the only thing easier to ignore than one email, is 100 emails on the same subject. Not only do I ignore the emails I receive, I’ve gone so far as to create a rule in Outlook to ignore the emails automatically on my behalf. Based on this unscientific study, I’ll conclude that having SharePoint send more notifications isn’t the answer. OK, what about a dashboard?

Despite not liking the buzzword, we are rapidly becoming fans of building meaningful dashboards around SharePoint managed content. It doesn’t take very long for list items or documents to pile up and turn a list or library into an unreadable mess. Since we have a few of these pages up and running, we have decided to add a personalized Data View Webpart to one of the pages that will show “Stuff you need to do”, but I’m not sure that’s going to help much. I say that because the rule that I created in Outlook wasn’t designed to ignore notifications, it was actually designed to help me pay attention to them. Based on the subject line, the rule puts the notifications into one of two folders for follow-up. The problem with that rule/folder combination is that it is a Pull operation – I have to go to the folder. Dashboards or status pages are also Pull operations, so those tasks will only get done if I go looking for them. Pull operations are forgotten, push operations are ignored – what’s a process to do?

As I think about this, I realize that there are only two types of reminders that I always respond to: calendar alerts and direct requests from people that I like. I recall Marc Anderson saying at the recent AIIM NE event, that he is more likely to respond to humorous notifications. I would agree with that, but there’s no guarantee. If I know that I need to stop for donuts on the way to work, I create a calendar item, set an alert time so that my phone beeps while I am driving; it works, I stop every time. When the nice woman from accounting calls or sends me an email reminding me that I have (n) expense reports to approve, I login and take care of them.


On the other hand, I told my wife that I was planning to stop at the ATM earlier this week; she wrote my planned withdrawal in the checkbook, but I forgot to stop. I also forgot to tell her that I forgot to stop, putting our checkbook out of balance – my bad.

I think a combination of push and pull solutions might help. Something like an alert that says “You have to do something” where the link takes you to a DVWP that includes actionable items. I.E. if you need to review a document, the link will open the document for you. If you need to approve a process step, the Approval button is right there. Maybe calling a person’s attention to an item coupled with an easy-to-use option to act on the item, will be enough to even get busy people to respond. We are even looking into VPN on Demand, so we could send these notifications as directly actionable items to an iPhone. If I add a bit of humor, maybe I can even increase the success ratio.