Liking Nintex

imageSummer is a funny time in most small shops. Projects start and stop as people take time off and attentions get diverted to the periodic crisis or to fill-in for an absent coworker. The downside for someone trying to craft a blog entry at the end of each week is that there isn’t much to work with. Those of you that come here don’t come to hear about the meetings I attended or the fires I put out. The upside is that this post will be short.

One of the interesting things I did this week was to build a SharePoint Workflow using Nintex Workflow. I’ve used Nintex before, but this time, I was teaching someone else how to build a “proper” workflow. The workflow is another part of our Payables process and it allows our accountants to delete a payable that has been submitted and approved for payment. Yeah, sometimes people make mistakes. To prevent someone from making those mistakes worse, this workflow needs to verify that a series of conditions are true. The payable hasimage to be at a specific status. The person running the workflow has to be a member of accounting. A bunch of people have to be notified, the activity has to be logged and we also have to let people know if any of those conditions aren’t met and the workflow has to be stopped.

All of those things can be done using a SharePoint Designer workflow. So, why does the title point out that I like Nintex Workflows? Well, that’s the point of this post. Here is my list of the things I like about Nintex Workflows:

I can add multiple logical conditions into the kick-off point of a single “Run if” block. You can see that illustrated in the image at the top.

I can act against multiple list items in one step. For example, I can say “go find all the allocations that have the same payment_request_ID as this imageitem and delete them.” You can’t do that in SharePoint Designer in SharePoint 2010

I can give an intelligent name to each workflow step. So, the above example step can be called “Delete Allocations” – I like that.

I can copy steps. We need to create a log entry regardless of the imageend-state of the process. Since some of those states cause the workflow to stop, I need to have the “Create Log Entry” step in multiple places which is very easy to do. Copy. Paste. Configure. Done.

I can drag and drop steps in and out of Condition blocks and Impersonation blocks which is very helpful when you realize that you have the right action but that it’s happening in the wrong place.

I can export and import entire workflows. In fairness, I think I can do this in SharePoint Designer, but I have had problems with that process and this worked like magic. I built the basic structure of this workflow from an earlier workflow that lets the person who submitted the payable to delete it before it’s approved.

And, my favorite thing – you work in a browser as opposed to a somewhat finicky, somewhat unpredictable and somewhat predictably bad stand-alone product.

That’s it for today. Not much of a product review, but I think you can understand why I like Nintex. And, I said it in fewer than 600 words.

Divide and Conquer

imageWhen I was making the coffee table that sits in my office, I learned a lot about bending steel. Specifically, I learned that bending square steel tubing isn’t easy and often doesn’t end well. Some bends were wide enough that I could negotiate the turn and repair the damage, but others were just a bend too far. For those, I took it in steps: cut, bend a little, cut again and then weld back together. I thought about that process this week, as I struggled with a workflow issue. I’m not sure if this is a common issue, but it seems to come up for us quite often:

“Is it better to build one big, ugly, complicated workflow or is it better to create a few additional lists and spread the work across them?”

We almost always tend to spread the complexity around. In the solution I’m working on today. I need to accomplish three major steps in order to reduce a 12-15 page Word document into a series of entries in a custom list:

  • Parse all of the recommendations out of the Word document
  • Determine if the recommendations are New, Open, Pending or Closed as of the date of this report, and
  • Create entries into either a custom list of recommendations or in a custom list of updates to existing recommendations.

All of this can be done by a workflow, but it’s a complicated task. A single big ugly workflow would need to keep track of individual recommendations, whether they are new or some other status and look up the details about the inspection report metadata and copy it into the other lists. I opted for a process that involves two additional custom lists:

One is a list of what I call “splits.” Splits are the big chunks of parsed text. All the new recommendations are in a split. Likewise, all the pending recommendations are in a split, and so on. A workflow that runs when the splits are created doesn’t have to determine what kind of recommendation it’s dealing with, they are all new, or pending, or some other status.

That workflow parses all the recommendations into individual entries in the second list. That list is then built up of individual recommendations which have a status (new, pending, etc.) and all the metadata that they need to be transformed into an item in the ultimate destination list. A final workflow that runs as these items are created takes care of dispatching those items.

The cost of this solution is the creation of a couple additional custom lists and a bit of duplication of data (although the items in these intermediate lists can safely be deleted). The benefits include:

Easier to plan – The overall process is easier to plan and stage. The process becomes a fairly simple routing exercise. Big boxes of the same thing arrive at one list where they are opened, separated and distributed to a second list. It’s like a wholesale-to-retail operation.

Easier to build the workflows – Rather than a complicated workflow with lots of variables and lots of steps, I have three single-purpose workflows that are each of a manageable size. I built the process in discreet steps, only moving forward once things are working at each level.

Easier to control new vs. update action – By the time I need to know whether to create a new recommendation or an update to an existing recommendation, the list item I am working with includes that information as well as everything I need to create those items.

Easier to debug – This is a big deal for the developer (me). As I am configuring these workflows, there’s really only one way any of them can fail. That is so much easier to deal with than a workflow that can fail any of three or more ways, especially given the current state of SharePoint Designer’s debugging tools.

Easier to rerun – Some of the workflows trigger alerts, some set metadata in other lists and change the status of other processes. Working in smaller segments means there are fewer things to fix if something ultimately goes off the rails.

It might be tempting to just keep on grinding out steps in a single workflow. However, dividing the steps into logical groups and taking advantage of storing partially processed items in interim lists is a very powerful concept. This approach makes design and development easier. By the way, it worked for that coffee table too. If you want to read more about that project, take a look over here.

I Didn’t Know That

clip_image002Next up in my series of summer shorts is an interesting lesson learned while completing a “tiny” SharePoint Designer workflow. This started over on my AIIM blog where I was explaining how I was fixing a contact list that had been built backwards. Instead of setting all the contact and address components, people were simply entering a mailing address in complete form. That approach works well for preparing a label, but not so well for writing the letter, unless you like starting out by saying “Dear customer” or “Hey you.”

Fixing the contact list was supposed to be an easy task. A guy on my team wrote a small program to snag the address block and bust it into all the little address components. We pasted those into the contact list (after adding back in the missing component columns) and to keep the mailing label users happy, a short workflow would reassemble the multi-line column continent. In addition, I was demonstrating SharePoint and SharePoint Designer to a woman who will be a pinch-hitter on our team. Of course, this is SharePoint so everything didn’t go according to plan, but that’s a learning experience too. Below is a quick summary of the things we learned along the way:

Microsoft is in Washington – I used to live in Seattle, and there are people on the left coast who are surprised by the fact that Zip codes in the northeast begin with zero. When we parsed the composite addresses into components, we saved them as a CSV file. When I double-clicked to open that file, 06033, the Zip code string for our office turned into 6033, the number. Pasting that into SharePoint failed because the column was looking for text.

There’s an interesting twist to this. If you open Excel first, you can tweak the text settings and then import the CSV. That works well for the zip codes, but we discovered a different problem. The multi-line text fields export from SharePoint well, and import nicely if you double-click the CSV file. If you open the CSV file as an import target, expect a few problems with the multi-line fields. Fortunately, we don’t need the multi-line fields, but we are researching this for the day that we do, but I digress.

String Builder is a Tease – Building a composite string from component text variables is pretty easy in String Builder. For example, you can type:

     [City], [State] [Zip]

which will result in a single line, with comma and nicely spaced. You can also type:

     [Address line 1]
     [Address line 2]
     [Address line 3]
     [Address line 4]

which will also result in a (rather unreadable) single line string.

No problem, Google and all the wonderful members of the SharePoint community came to the rescue. There were a couple of solutions offered for adding line breaks to multi-line text but the one I liked was simply adding the html break <br/> at the end of the line. There’s an interesting and understandable twist to this too – this only works if the multi-line text column is configured for Rich Text. That compounds the earlier twist, because the import problem doesn’t affect plain-text multi-line columns, only rich text.

I Can’t Run This Workflow on Every Item in the List – Actually, I can but only after applying a little client-side script magic. If you want to learn how to do that, jump over to the king of how-to-make-SharePoint-do-the-hard-stuff blogs and read one of Marc Anderson’s SharePoint Services Stories (clever name) – actually, it’s the very first SharePoint Services Story. Marc’s blog is my go-to resource, and his solution is elegant and reliable, but I didn’t really need it today. As you may recall, I already had the composite columns. I just had to make sure that those fields would get created and updated when new entries are created and old entries are changed – I think that’s called testing.

In case you’re wondering, we wrapped each of those address line thingies in an If-statement so we didn’t end up with blank lines.

Have a great weekend!

The Market Will Save Us

clip_image002I just finished reading a blog post by Sahil Malik which refers to a blog post by Marc Anderson and other posts and comments on the subject of the soon-to-be-gone-but-not-forgotten design view in SharePoint 2013. I have followed this story from the beginning when Marc was hinting that the design view was missing, up to and now well beyond, the seemingly official death sentence that was published by the Microsoft SharePoint Team on their blog. That the design view is going, going, gone seems certain at this point, but whether that will result in a wave of power users learning the art and craft of development or just skyrocketing demand for existing developers is not so clear. I can only say with clarity what will happen in my little world – nothing.

I should rephrase that “nothing will happen right away” and by “nothing” I mean SharePoint 2013. It won’t land here at least until this time next year because activity is underway that relies on the work that members of my team are performing in SharePoint Designer, using design view. Those projects will be completed and an annual event that depends on some custom Data View Web Parts that we assemble will be supported on SharePoint 2010 as it has for the previous two years. Switching canoes in the middle of this stream isn’t appealing and it won’t happen. This isn’t going to hurt Microsoft, (unless SharePoint 2013 marketing success depends on posts in this blog) because we have SharePoint under Software Assurance.

While people are lining up to prognosticate as to which way companies will go (developer of just plain SharePoint) I have faith that good old marketplace economics will save the day. Somebody will build a tool or an add-on tool for something like Dreamweaver, to once again empower the end-users and designers to push SharePoint beyond the limits of its dumpy box. If that doesn’t happen, then other market forces will bear down on the situation and other platforms may benefit from this decision. Again, I can’t speak for the market, or the broad content management / collaboration community, but if I have to turn developers loose on an ECM solution, I’m going to take a hard look at Nuxeo before I start buying more copies of Visual Studio.

Of course, I probably don’t even have to go that far. Before watching Marc Anderson deftly build a Data View Web Part using j-this, j-that and SPServices, I was using Ruby and Dreamweaver to put the information I wanted in a Web Part. I certainly prefer the approach Marc taught my crew, but I can always revert to other options. In fact, I may not have to revert to Ruby; much of what we display in Data View Web Parts can be rendered by SQL Server Reporting Services, another technology my crew is adept at using. Then again, the market may save the day in other ways. When I was using Ruby, I coupled it with Dreamweaver because someone had written a Dreamweaver extension for working with RHTML. I’m willing to bet that there are way more SharePoint power users than there are Ruby developers so I wouldn’t be surprised to see a solution become available for building the kind of solutions that we continue to need in SharePoint from a different development starting point.

Microsoft may want SharePoint to look like SharePoint, and as I have said before on this blog, I support that to a degree. The more SharePoint solutions share a common look and feel, the easier it is for people using SharePoint to move from one solution to another. Still, sometimes what you need isn’t in the box. When intelligent and enterprising people are confronted by the absence of the things they need, they find them elsewhere or they figure out how to build them. When enough people start building things, vendors bring tools to the market. Maybe Stanley will come out with the Fat Max Editor for SharePoint – I would buy that in a heartbeat.

HarePoint

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:

clip_image004

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.

Letting SharePoint Shine – Part 1

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