clip_image002I began driving in the era when Interstate highways were beginning to replace the convoluted network of US and State highways interconnected by local roads. One of the first major bits of Interstate to open up in western PA was I-79 between Bridgeville and Washington, PA a.k.a. “little Washington” so you didn’t confuse it with Washington, D.C. I-79 was an alternative to, but did not replace US Rt-19. Unfortunately for me, my father stood by Rt-19 as his first choice for any destination between our house and little Washington. I can remember his arguments, which varied between: “Rt-19 is actually shorter” to “by the time you deal with getting to and from the ramps, the highway isn’t that much faster” – I call results like that a Failure Due to Marginal Improvement (FDMI).

I have recently been reminded of those conversations with my dad, as I try to replace an Excel spreadsheet with a series of SharePoint lists. The issue is that we aren’t talking about replicating data; we are talking about changing, and hopefully improving the process that the data is associated with. The question is, are we doing enough.

The spreadsheet works – The current “system” in Excel doesn’t work well, but has the advantage that people know how it works. This is often the reason why people want to just move what they have into SharePoint, the thought is that they will still understand it. When we are replacing a list, we are often changing the way items are created, maintained and viewed. The result should be a better overall experience, but will it be good enough to offset the change?

Several weeks ago, Marc Anderson raised the question of whether form or function was more important. I suggested that the form-function ratio should never be less than 4:3, and this little project is an example why I think that is true. SharePoint lists are about as close to a database solution as you can get without invoking SQL, but we’re still talking rows and columns, and rows and columns are Excel’s forte. When you start moving Excel data into SharePoint, you create one or two (in this case three) Custom Lists. If you show the user an All Items view of their data in SharePoint, it’s going to look worse than it did in Excel. SharePoint doesn’t use its screen real estate as efficiently as Excel, and people who like Excel will pick up on that immediately.

The Excel-based solution I am replacing is a collection of observations and recommendations. Each row has details about the observations on the left, a huge column of text containing the observations in the center, and a series of recommendations on the right side of an A-X layout. The text is nearly unreadable and it is hard to stay on the same row as you scroll right to left, but the process is easy to understand. Read the details, read the text, write the recommendation.

Moving to a data view web part of items, with options to expose the details in a second DVWP, the observations in another and the previous recommendations in a third, give us a composite view of each item in an easy to read format.

I am planning to add an entry part to write the new recommendations, but that can wait. What we have so far is based on the initial discussion, and it’s time to check-in and show the results.

I’ve got one more concern as I get ready to touch base with the future owner of this solution. Since the stuff isn’t in the same “row” (it’s not even in the same list) he needs to have an increased level of trust in the underlying process. Sometimes it’s hard for people to accept that everything on that page is everything there is. Again, you gain that confidence in the spreadsheet visually; it’s not a pretty sight, but you can see it. I plan to walk the new owner through the process of building an entry from the ground up. By the time we come back to the composite page, he will know exactly what he should see. I’ve made that trip eight times, so I know it’s going to be a sweet ride. In addition to making it work, the user experience needs to be clearly superior to Excel if we’re going to avoid FDMI.

Doing Battle Again

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

Since the Mayans Were Wrong

clip_image002I guess I need to write another blog entry. Actually, this is an easy one, unless I want to try my hand at interpreting the signs around me. I’m not good at recognizing the obvious, let alone the future, but I can predict one thing; this is the last blog entry for 2012. The world didn’t end, but I am giving myself and my editor (wife) a week off. So you can check back on January 5th 2013 for the next update. Coincidently, the post on January 5th will be the 200th SharePoint Story – that’s pretty cool. I may not be a prognosticator, but maybe I can make some sense of history.

When I started this blog, I was planning for others to contribute the stories. I thought that might be interesting, and I didn’t think I had 200 stories to tell, I wasn’t sure I had 6 stories to tell. Apparently, I underestimated myself, and the interest level in ordinary stories about SharePoint in use. As I look at the statistics about this blog, the #1 story of all time remains SharePoint of My iPad, and it represents just under 5% of all the stories ever read! Ironically, the second most popular post is No iPad For Me, a.k.a. the worst prediction I ever made. The timing of those two posts, (Feb 2010 and April 2011) would indicate that Microsoft was about two years too late to the market with Windows 8 (I bought my iPad in Oct-Nov 2010). My Top-5 posts include another curious pair, one deriding the value of email (Task vs. Email vs. Task) and one of several posts ridiculing the use of Excel as a means of tracking a list of items (Excel v. Custom Lists). I have attacked email and Excel multiple times, and I still think SharePoint will ultimately defeat those two stalwart competitors, but I think I will be retired before that day arrives. Rounding out the Top-5, sitting comfortably at #4, is a story about our first formal encounter with Marc D. Anderson (Symply the Best).

I guess if I really wanted to ramp up my stats, I’d make that 200th post “Why Marc Anderson thinks Excel and email will ultimately be replaced by SharePoint running on an iPad.” Fortunately, I don’t care about stats (but if Marc ever said that, I’d pay attention). Every now and then, someone says something nice about this blog, and that’s usually enough to keep me writing. If you are wondering what you can expect to find here in 2013, the answer is: “more of the same.” I once described SharePoint as an empty building next to a highway ramp, and in our case, there is still room for a lot of new tenants. During the last quarter of 2012, we made plans and promises for several new projects and we are receiving some very good feedback on the initial activities around those solutions. I will continue to share the things we learn from our attempts to bring those projects to fruition, including the misadventures when we or Microsoft get things wrong.

You can also expect me to mention the people, products and organizations that we like. Most of those can be found on the “Who We Like” page, but I realize that I left BA Insight off that list. We use their Longitude Search product and it was a pleasure dealing with them when we made that decision. I hope to make that page a little more attractive (that shouldn’t be hard) because people do ask me about products, services and vendors, but I am still trying to avoid advertising on this blog.

I will periodically make a promotional comment about AIIM, and I will blatantly support, endorse and encourage you to participate in the events of the New England Chapter of AIIM. As always, I want to be transparent here. As you can see at the top of this page, I am the Program Director for AIIM New England. What you may not know is that as of January 1, 2013, I will also be serving on the Board of AIIM International. I’m psyched about that opportunity because I strongly believe that the educational opportunities provided through AIIM, the AIIM Conference, the AIIM Community and the network of Chapters is the best and most cost-effective way to build a proper foundation for the things we build on SharePoint.

Thank you for visiting this blog and for sharing your comments here and on the various social media outlets where we interact. I hope you are having a wonderful holiday season and I wish you the very best 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.

SharePoint vs. Excel – Round 2

imageIn December 2009, I pitted Excel vs. Custom Lists and Excel lost that battle. Recently, Excel asked for a rematch. When Marc Anderson was teaching my team about developing in SharePoint’s Middle Tier, he asked if he could work with some of our data and I pointed him to a survey we had run in 2010. Marc showed us a few techniques, but everybody agreed that analyzing a survey was a task probably best handled by Excel. We moved our attention to a different source for the more advanced examples.

After Marc left, I wanted to begin my own middle-tier journey, and the survey was a good-sized set of test data and the Excel-based reports served both as specifications and test results. I wired up a few Data View Web Parts in what I labeled a “Summary View” page. The survey had been for an off-site customer meeting; I was showing: the number of attendees; the number bringing guests; the number of hotel rooms needed, the number of people coming to dinner, breakfast, and various events and breakout sessions. I showed this fledgling analysis page to a few people; everybody had the same reaction – they wanted one for the 2011 survey. Why? What made the promise of a set of browser-based analysis pages more appealing than the reports we had in Excel?

Live Results – The SharePoint to Excel connection is easy, but somebody has to make it. Somebody has to download the results, refresh all the Pivot Tables and then print the pages for others to view. If you’re thinking that we could let everyone access the spreadsheet, read the next paragraph. SharePoint Data Views are live, they change when the survey changes and they are available 24/7. In addition, the summary page DVWPs link to deeper views of the same data. For example, the Web Part that summarizes the main dinner and reception shows the total number of attendees and guests coming for dinner. If you click on the title, we bring up a page that shows the name of each attendee, that of his or her guest and any food allergies or restrictions either may have.

No Errors – Excel is a great product and a fantastic analysis platform, but unless you go to a lot of trouble, it’s fairly fragile. It is remarkably easy for someone to forget to press Refresh All, to unclick a filter in a Pivot Table or worse, overwrite a formula. In prior years, when any of these things happened, we had to find the Excel expert who had written the spreadsheet. By comparison, the XSL behind the DVWPs is protected by SharePoint permissions; they can only be modified by people who know what they are doing (hey, I had two days’ worth of training).

Easy Access – We run this survey off of our Internet-facing server so our customers can get to it. The analysis spreadsheet was on our file system, behind our firewall. This meant that you could check those results in the office or over VPN, on a laptop with Excel installed. Of course this assumes that someone did the download, refresh and save operation first. Now, if you can access the survey, you can view the results. The people who run this event like the fact that they can check the survey results from anywhere, at any time, and the page even looks good on an iPhone. There’s an additional benefit to this last fact. During the event, our people have to know who is supposed to be at certain places, at certain times. Who is supposed to be at dinner, in a breakout session, on a bus to an event, etc? Now, any one of our employees can check any of this information from the event venue without carrying a bunch of paper around. Those last minute changes, the ones that were recorded after we boarded the planes; they will be accurately reflected on the result pages.

So far, I have completed the Summary View, and several detail views. I have figured out how to dissect all the bits and pieces of data held inside a survey response and how to spit them back out to a web page in an acceptable manner. Also, as of yesterday afternoon, the most important user of this information, the person responsible for the event, told me that he “really likes the fact that he can access these results whenever he wants to.” He realizes that the Excel Pivot Table reports looked better, but he wouldn’t go back if it meant giving up these new features – it doesn’t get any better than that. Of course, if Excel wants to go another round, I’ll bring Marc back and we’ll make those pages look amazing!