Sunday, June 22, 2014

SharePoint Discussion Board Message Approval Workflow

If you've ever moderated a discussion board in SharePoint, you're probably well aware of how much of a nightmarish task monitoring messages is. What turns the nightmare from mere Stephen King level fright into something more Cronenberg-esque is looking for inappropriate content in discussion board messages. Thankfully with some help from Nintex you can build a discussion board message approval workflow that'll make your life easier and you'll avoid any fright.

High-level, here's what we're looking to accomplish:

  1. Have a user maintained list of flaggable words
  2. Have a list of blocked users
  3. Have email notifications sent to a SharePoint group when a message contains a flaggable word

First, create a list in the same site as your Discussion Board called "Flaggable Words." Each item in the list will be an inappropriate word. In the screenshot below, you can see that colors are flaggable offenses.

image

Next, create a list called "Flagged Users." This list will contain users who cannot post messages to the discussion board. To make this happen, enable content types on the list, add a new column called "User" and make the Title column hidden or add in a default value. Add a user or two to the Flagged Users list.

Next, the Discussion Board needs to be tweaked. Add a choice column "Approval" to the Discussion and Message content type set the default choice to "Approved" and make the column hidden. This is our column that imitates that Approval Status column. No one will see this column.

But Erik, why not use content approval?

If it's easier for you to use content approval, then be my guest. Content Approval is a valid approach but does not work in my scenario. The solution has a several custom web parts where users post to the discussion board via the web parts. Enabling OOB content approval means that responses will not be visible immediately. So what we're doing instead is spoofing the OOB content approval, assuming everything will be approved except items that are flagged.

Now that we have the assets in place, let's get to the workflow.

Create a new Nintex workflow on the discussion board list and add the following variables:

clip_image003

Then set the start and modify conditions to be conditional where Content Type is equal to Message.

The first action is to check whether the user who created this message is in the Flagged Users list. I don't want a user who shouldn't be posting to post something polemic and let it sit for any extended amount of time. So the Flagged User list is queried and if the user who created the message is in the list, the message is deleted right away. If not, the workflow continues on its merry way.

clip_image004

But Erik, why use a Flagged User list? Why not remove their permissions outright from the discussion board and/or SharePoint?

This is a valid point, but it'd be pretty apparent if functionality was disabled or not available to a particular user. This solution gives the illusion of participation. This solution was built with the intent that there would never be the need to utilize the Flagged User list, but it exists should we ever need it.

After the flagged user check, gather the body of the discussion board message; set the variable MessageBody to the Item Property Body.

clip_image005

The thing with the Body field on the Message Content is that it's an Enhanced rich text field. That means we get all sorts of gobbledygook HTML markup that needs to be cleaned up. Thankfully there's Regular Expressions.

Add a RegEx operation and have it perform an extract operation from the MessageBody variable on the pattern (?<=^|>)[^><]+?(?=<|$) and store it in the BodyCollection variable.

clip_image006

Now that the HTML is cleaned up, the workflow now needs to break up BodyCollection word by word. Again, RegEx to the rescue. Because flaggable words can be followed, or prefixed by punctuation (let's hope not), add another RegEx action. The pattern [^a-zA-Z0-9] matches all non-alphanumeric characters.

clip_image007

Once that's cleaned up, BodyCollection needs to be looped through. This is where every word will be looked at and assessed if its flaggable or not. To do this, add a loop action that loops through BodyCollection and store the result in MessageWord.

Within the loop, the first action is to query the Flaggable Words list where Title is equal to MessageWord. Capture the output of Title and log it to IsThisAFlagWord.

clip_image008

Then an operation will check if the current word is an objectionable word. This includes all possible permutations of capitalization, meaning you don't need n-many capitalizations of a single word in the Flaggable Words list. If it's not objectionable, the loop will run through the next word. If it is objectionable, break the permissions on the item and start content approval. If the message is rejected, delete the item. If the message is approved, set the field Approval to Approved, re-inherit permissions and end the workflow.

clip_image009

And that's it!

There’s one advantage and one limitation that I want to call out here. The limitation with the solution as presented in this blog is that it doesn't search for phrases of words. To do so, you'd probably need to set another collection variable to equal BodyCollection and loop through each phrase in that collection. This action would probably be done best in parallel when looping through the individual word. While this may be a limitation for some, I suggest just using single words to flag posts as it'll make the Flaggable Words list easier to maintain.

One advantage I haven’t mentioned already is that this workflow will search the entire message body of a post. The Body field in a message contains all of the previous replies in the thread. So even if a crafty user replies with a flagged word in the last line of a discussion board message, this workflow will find it. Your posts can run, but they can’t hide from this approval workflow.