Business – not SharePoint Solutions

Two recent projects have caused me to realize that SharePoint has finally arrived in our small organization. I don’t mean that it’s here and in use, it’s been here since 2006. I don’t even think I’m talking about “adoption” the way that word is often used with respect to SharePoint. It’s arrived in that it’s now part of the permanent landscape and that’s a good thing. It’s good because people aren’t fighting the idea of SharePoint. On the other hand, SharePoint has only managed to shove itself into the mix. It isn’t the dominant player. It isn’t calling the shots. It’s on the team but it has to play by the same rules as everything else.

One of the projects we are close to completing has SharePoint in the leading role. The application is a portion of our payables process and people are now creating payment requests in SharePoint. Other people are reviewing those requests, adding comments and still other people are approving those requests. If all of this lived in SharePoint, SharePoint would rule the day. However, the back-end of this process is a desktop application that takes those approved requests and prints checks. That application also creates ACH transactions and wire-transfers. Eventually SharePoint will be the starting point for all those transactions, but everything SharePoint does has to feed that system.

Other processes are involved too. For example, we can’t present a payable for payment without making sure that the person / company we are trying to pay isn’t a terrorist. In that case, the back-end process is actually the starting point. We check vendors before we authorize them to be paid and we continue to check to make sure they don’t become terrorists. I suppose the back-end stuff could be done in SharePoint but it’s easier the way we’ve done it.

Note: All of those processes involve data that is stored in SQL Server and my crew had to battle with every imaginable issue (all of them permissions) to get those connections working reliably.

The second system we are working on is a storage system for some very important information. In order to make sure this stuff is available when we need it, it will exist in SharePoint on premises, some of it will be replicated in SharePoint Online and some will be replicated on a bunch of iPads. In this case, SharePoint is cast in the boring supporting actor role. Yes, SharePoint is holding all the stuff in house and holding all the stuff online, but the iPad app is the cool kid. Accordingly, SharePoint has to try to fit in.

We decided that the way the content is organized in the iOS app will determine the way it is organized in SharePoint. In other words, the list and library structure in SharePoint will correspond to the structure of root categories and detail topics in the iPad app. The app design is intuitive, something that SharePoint struggles with out of the box. The design is simple enough that it won’t take much work to make SharePoint look and feel consistent with the iPad. Still, a few years ago, this wouldn’t have been a consideration.

SharePoint and SQL Server was an arranged marriage and like many of those, it works, but it’s weak on love. We are making the connections work, the connections do work, but they all seemed to have taken more effort than should have been required. SharePoint and iPads? I’m pretty sure that was never part of anybody’s plan, but it has to work. We have to build a solution that spans those platforms and looks like it was meant to be.

Welcome to the real world SharePoint.

Doing Battle Again

Every time someone comes to me with a system that they have built in Excel and asks me for help making it better, I feel like Steve McGarrett in his perennial battle with Wo Fat. Inevitably, that “system” is a list of stuff loosely arranged in row-column order. Also inevitably, when I suggest moving the system into SharePoint, I get major push-back. I have written about this before, in fact, Excel vs. Custom Lists is one of the top-5 most popular SharePoint Stories of all time. This time, I thought that I would share some of the questions and comments I normally receive, along with my answers:

“So, you really push SharePoint don’t you?” – No, I don’t “push” SharePoint, I recommend the best technology we have available to meet the business requirements. When those requirements include tracking a list of items that are characterized by custom but common attributes, I am prone to recommend building a Custom List – I’m just funny that way.

“I think these things should be stored in a database.” – I usually avoid saying “then why did you put them in Excel?” I also try to avoid going into the weeds to explain SharePoint from the ground up, and how it really is a fancy way of sticking stuff in SQL Server. I’ve learned that the way to win this battle is to ask “why?” Sometimes, people don’t know why they think stuff should be in a database. These are the same people who buy all sorts of storage systems for their garage because the garage in the picture looks so good. The people who do have a reason for wanting their list of items to be in a database usually want one of the following three things:

• Relationships – whether it’s one to many, or many to one, they want to relate a few things to one or more other things. This is a great reason to put stuff in a database. It’s also a great reason to put stuff in a series of connected SharePoint Custom Lists. Why do I often choose SharePoint instead of SQL Server?…I mean we have both. Well, SharePoint offers all the features we normally need to meet these requirements, programmers are usually not required and it’s easy to share the lists on a web page and out to a mobile device.

• Ease of use – When people say that they want their storage mechanism to be easy to use, they are almost always still talking about relationships. They want people to be able to pick from a list of standard terms, or they want to select a facility name and automatically find the policy in force and the underwriter for that policy. Perhaps you can appreciate how hard it is for me to not say “seriously, why did you put this in Excel?” I am a huge fan of vLookup(), but I’ll buy a beer for every Excel user at the bar who can tell me how it works. SharePoint, by contrast gives us multiple ways of adding Pick-lists and lookup columns and they are all pretty easy to build and dirt simple to maintain.

• Sorting and Filtering – I understand that Excel has the ability to sort and filter, but these functions in Excel involve hiding what you don’t want to see, particularly if you don’t want to see the same columns for every sorted and filtered set. Views in SharePoint are real views. Sorting and filtering in Excel is like putting virtual duct tape over certain rows and columns. But, if you want to make the point to an Excel fan-boy, build a view. Sit them down, and build a view right in front of them and dare them to do the same thing in Excel. Better yet, build three views in less than 5 minutes and sit there switching between them. Even better, put two Data View Web Parts on a page and use one to drive the sort and filter parameters of the other. Granted, that last one takes a bit of what some would call programming, but still…

“It’s easier to print from Excel” – OK, you may have me on this one, but that’s OK, I have a secret weapon – SQL Server Reporting Services. I know, there are ways to print from a Custom List, and there are List Print utilities. In fact, I think we own one of those utilities, but I like SSRS because my coworker can slap Excel upside the head with a report from that service.

When people push against moving to SharePoint from Excel, don’t argue the details, don’t try to convince them with logic and don’t make fun of them – bury them. Find every single thing they like about Excel and show them the superior thing in SharePoint. Trust me; it won’t be hard to do.

Communication Failures

I’ve been following a communication disaster on LinkedIn this week that could have been avoided so easily, and yet, as easy as it is to say that, I might just make a similar mistake. Toastmasters International released a new dashboard for district officials to review their district stats, and lots of people had a lot of problems. The new version was built in Silverlight, and wouldn’t run on Linux systems, older Macs, and most mobile devices – #fail. After over 100 comments were added to the discussion, it was announced that an HTML version is coming “soon”. After the comment count climbed even higher, it was announced that an iOS version is also “in the works”. When you combine those and other answers, you have a really good story. The problem is: they never actually told the story. Communication failures occur for a variety of reasons, but the big three include:

I Never Told You – Information is like money during periods of high inflation. The longer you keep it, the less it is worth. Unlike money, information’s value can actually turn negative if you hold onto it too long. If someone ever earns the right to say “when did you know that?” – you are in trouble. Companies have lots of reasons for withholding information, including not wanting to disappoint investors or give a head’s up to the competition. Internally, there is no reason to withhold what you know from your customers (I’m trying to not call them users). We are currently telling people what our plans are, and we are letting them know what is/may be coming in the future that will impact those plans. We are talking about SharePoint 2013, about Windows 8 and about the next version of Office; we are even offering to let people look at our test installations. We are also talking about ECM and why the things that make SharePoint different from the K:-Drive really matter. There’s very little downside for us in sharing what we know, when we know it. Notice that I didn’t mention the next version of SQL Server – see the next point.

I Didn’t Translate – The folks at Toastmasters started out talking about Silverlight requirements, Intel chipsets, screen resolutions and monitor size, long before they said “oh wait, there is an HTML version coming.” The only thing that those comments did was add to the confusion. People outside of our department don’t want to hear anything with version numbers, hardware/software requirements and service packs. In addition, there’s a list of words I really try to avoid, including: platform, content, repository, database, Data View Web Part, JavaScript, HTML, web service and many, many more that only serve to make their eyes roll back in their head. We are trying to remember to talk about SharePoint in business terms, or in terms of the business process we are working with. Not only do our customers understand those terms, they appreciate the fact that we do, too.

I Told You My Problems – Those of us working the IT side of the fence always have a new language to learn, a new version of something on the horizon and a new bug we can’t crack. Newsflash – nobody cares! In the old George of the Jungle cartoon series, there was a cartoon called Super Chicken. When his sidekick would complain, Super Chicken would chide him with “You knew the job was dangerous when you took it, Fred!” Well, we all chose to work in this field. Droning on and on about how hard you are working, all of the things that should be but aren’t working or even how awesome your last accomplishment was, doesn’t count as communication. Save those stories for the next user group. Your customers want to hear about the progress you are making on the new feature they asked for. They want to see a demo and they want to hear you describe things in a way that tells them you really do understand the business requirements they trusted you to meet.

The Toastmasters discussion is still raging on LinkedIn. People are getting defensive on one side and angry on the other. Messages are being written in UPPERCASE. It’s reached the point, like all long discussions on LinkedIn, where newcomers who haven’t read the earlier comments are repeating complaints that have already been aired. There’s no way to stop this; there’s no ‘Undo’ option on a communication failure. These are mistakes you simply have to avoid making in the first place.

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.

Letting SharePoint Shine – Part 1

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.