Customizing a Survey

You boffs out there will recognize the pains of providing a quick POC for a customer requirement without developing a fully fledged solution. Ok so here goes. You wanna provide customization to a survey without going full code. For example, in my requirement (Office 365 Implementation), I wanted to add a ‘response’ column to a survey which does not behave like a normal list (naughty naughty!) . This column would store a response to the answer to the survey by means of a workflow on that survey. (There was only one question in the survey as the answers needed to be anonymous and this is near impossible without full code to any list because of the default columns: Created by and Modified by). So the user answers that one question, the workflow kicks off to collect feedback from the manager or approved person who can respond to it, and then the workflow fills the response column of the survey with the response . Simples hey? lol

Ok So here are the cheats for customizing a survey:

Create New View:

  • Open the new view page of any list in your SharePoint site, and copy the URL to a notepad.
  • Open  survey settings page, and from the URL copy the List ID. (List=%7Bxxxxxxxx%2Dxxxx%2Dxxxx%2Dxxxx%2Dxxxxxxxxxxxx%7D).
  • Replace the list name to the last of the URL with the survey list name.

Copy the modified URL to a new browser window, and you are done, you will be now in the ‘Create View’ page of your Survey List. Create the view as you wish here……….

Create A New Column:

Same the way you did for survey view, copy the new column page url (/_layouts/fldNew.aspx?ListID)

  • Replace the ListID with the survey list ID and you are done.

 

Modify View:

  • In order to perform the modify view of the newly created view as per the steps above, just add your list as a webpart to any SharePoint page.
  • In the modify shared webpart option, select the view which you want to edit in the Selected View and  select the option ‘Edit the current view’.

As with all hacks, save a copy in case you break anything and regularly back up your servers. If you’re on Office 365, Microsoft got your back!

 

Using A SharePoint Designer 2010 Workflow to export an excel sheet into a sharePoint List

In SP 2010 there is an option to create a list from importing an excel sheet. I have a requirement to automate this process but I was not sure how to start or even if my proposed solution is the correct fit.

I have a sql database which dumps 2 excel sheets into a file location every night, with graded sales stock data and quantities. The data in the excel sheets changes every night as new stock is made ready for purchase. This excel sheet is manually copied and edited into an html editor and copied into an html page with the data for display on a website. This data allows users to see what graded stock is available for purchase. The main requirement is to automate this process with minimal human effort and store the table of information in a list in a SharePoint 2010 Intranet site.The fields of the excel sheet are item number, description, quantity and price.

My initial thoughts are to have the 2 files dumped into 2 seperate document libraries and then have workflows initiate to ‘export’ the contents into 2 seperate lists (One for each file) which will be written over everytime a new file is added to the libraries. there are no workflow actions out of the box to export an excel sheet to a sharepoint list. The reason I would like to use a list to display the data is so that I can create an order form in InfoPath to allow users to order items from the list of stock, which would involve the quantities being reduced everytime and order is submitted.

Perhaps there is a way to directly dump the excel file into a SharePoint list and negate the need for a document library inbetween?

I am using SharePoint Designer for my workflows due to time constraints and My Intranet is SharePoint 2010, I posted this to a Microsoft forum and this was my reply:

SharePoint Designer workflow can’t work here, because SharePoint workflow can start manually or a change occurs in SharePoint list/library automatically, but Excel data changes can’t start the workflow. So instead, we can create a custom timer job and set it start daily to import data from the excel file.

Here is a blog about creating custom timer job.
http://dotnetfinder.wordpress.com/2010/07/24/creatingcustomsharepointtimerjob2010/

Then we need to read data from excel file and import them to SharePoint list in the custom timer job. We can use the code from codeplex.
http://spreadsheet2splist.codeplex.com/
http://spreadsheet2splist.codeplex.com/SourceControl/changeset/view/25678#418495

I will be blogging about my experience with working with custom timer jobs to fulfil this request!

Catch 22 with SPD workflow and checking in documents

I recently had to build a workflow in SharePoint Designer to model a fairly lengthy approval process for policy documents involving multiple stages of review, each of which has to be passed before the draft document moves to the next review stage. The workflow itself was quite long but essentially boiled down to a series of repeated steps. Each step involved assigning a task to a reviewer (or reviewers) and then updating the status of the document to show what stage it was at. The Policies document library has enforced check in/out, version control, and content approval all turned on. I also needed a field called Review Stage to show what stage the document was at – that was a simple choice field with 7 different stages.

Version History

The obvious thing was to add the Review Stage field to the document library.  After some playing around with the workflow, however, I decided to do things a more complex way.  I created a new list called Document Review Stages with two fields, one was the choice field Review Stage and the other was a number field called DocumentID.  For each policy document in the document library, there is a corresponding entry in the Policy Review Stages list.  The DocumentID holds the ID (the built-in column that SharePoint populates automatically) of the policy document.  In other words, I effectively created a one-to-one relationship between the Policies document library and the Policy Review Stages list.  Of course, this being SharePoint and not a full-blown database, this isn’t a proper relationship – there’s no enforced referential integrity, no cascading updates, etc.  But the basic principle is the same.

So why did I do it in this convoluted way?  Well because the Policies document library has enforced check-in/out and major/minor version control.  So suppose I had the Review Stage column on the Policies document library itself.  That would mean any change to the document, even just changing its metadata, would create a new version.   And that means that the workflow, if it wants to progress the document to the next review stage, would have to first check out the document, then set the Review Stage field, then check the document back in again.  And you’d then have 2 versions of that document, both exactly the same in content, but with different Review Stage metadata values.  This would result in a confusing version history for each document, with some genuinely different versions of the document (edited by different reviewers) but also many identical copies of each document created automatically by the workflow as it moves the document through the review stages.  By moving the  Review Stage column to a different list, the workflow can update the Review Stage field without creating a fresh copy of the document in the version history. That makes the version history much cleaner and more readable.

Approval Catch-22

This being a SharePoint Designer workflow, not a Visual Studio one, I had to attach it to the Policies document library.  I got the workflow to find the Policy Review Stages list item related to its corresponding policy document, and update the Review Stage column on the list.  So far so good, but then right at the end of the workflow, the last step was to make the document go live by setting its approval status to be “Approved”.  There’s a special workflow action to do just that, so it seems easy enough.  But there’s a catch.  If you want to make the document Approved status, you have to change it, and the workflow complains if you try to change it without first checking it out.  OK, so you change your workflow to check the document out first, and then approve it.  But now the workflow complains because you can’t approve a document that is checked out.  Catch-22!

So how do you get round this?  Well you can use the Start Another Workflow action to start the normal Approval workflow on the document.  The built-in Approval workflow can work with a document that’s not checked out.  So you end your workflow early, before the final approval task is allocated, and hand off control to a standard Approval workflow to manage the final approval step.

Content Organiser and Workflow Fix

I have been looking at using the new Content Organizer feature in SharePoint 2010. The idea being that when people upload or ‘Send To’ a document center, the document is routed to the correct location.

When I enable the Content Organizer feature on my site, the ‘Drop Off’ library is created.

I then create a rule to route the document to the ‘Documents’ library. I don’t want the document to be published and visible to all users until it has been approved. So I create a simple Workflow to do approval which should start when the document is created.

Unfortunately, the workflow never starts automatically once Content Organizer has been enabled.

I had a look at the Content Organizer code with Reflector, and it seems to be executing a SystemUpdate after it has moved the document to the correct location. I assume it is running this as one of the service accounts. (I have not checked which one).

When I look at the ULS logs in more detail, I see the following exception is occurring when I upload a document.

“Declarative workflows cannot automatically start if the triggering action was performed by System Account. Canceling workflow auto-start.”

I tried submitting the document as a user other than System Account, but the error persisted.

So I decided to look at starting the workflow programmatically. I created an Event Receiver is Visual Studio 2010.

I found this post from Tobias Zimmergren with some sample code to programmatically start a workflow.

http://www.zimmergren.net/archive/2009/01/25/starting-a-sharepoint-workflow-from-code-event-receiver.aspx

I overrode the ItemAdded event as follows:

public override void ItemAdded(SPItemEventProperties properties)
{
base.ItemAdded(properties);
//SPListItem item = properties.ListItem;
//item[“Title”] = ” ” + item[“Title”] + ” – added ” + DateTime.Now;
//item.Update();

if (properties.ListItem.ParentList.TemplateFeatureId==new Guid(“00bfea71-e717-4e80-aa17-d0c71b360101”))
{
SPWorkflowManager wfManager = properties.ListItem.ParentList.ParentWeb.Site.WorkflowManager;
SPWorkflowAssociationCollection wfassociationCollection = properties.ListItem.ParentList.WorkflowAssociations;
foreach (SPWorkflowAssociation wfAssociation in wfassociationCollection)
{

if (wfAssociation.BaseId == new Guid(“8ad4d8f0-93a7-4941-9657-cf3706f00409”))
{
wfManager.StartWorkflow(properties.ListItem, wfAssociation, wfAssociation.AssociationData, true);
break;
}

}
}
}

I got the baseid for the WorkflowAssociation using SharePoint Manager:

I run the event receiver by pressing F5 in Visual Studio and watch the behaviour.

When I upload the document, the content organizer routes the document to the folder in the library and the workflow starts.

I expect to spend some more time refining the code for the eventreceiver to make sure it only executes under the right circumstances, but thought I would share in case anyone else is experiencing the same issue.

References

http://sharepoint-sandbox.com/index.php?/SharePoint-2010/Content-Management/tip-88-sharepoint-2010-content-organizer-execution-order.html?directory=14

http://blog.ozippy.com/2010/06/starting-workflow-when-content.html

Content Organiser and Workflow Bug In SharePoint 2010

SharePoint 2010 introduced a new feature “Content Organizer” to organize your content automatically, based on custom-defined rules. However, these rules add new entity to the hierarchy of events that can be executed for the List items, and this execution order is not well documented. It’s important to understand the sequence to process documents accordingly.

For example, when the user uploads his CV to the library you want to add watermark by Workflow, update .docx metadata with event receiver and round document to “unprocessed” folder using content organizer. We have “DropOff Library” with WorkFlow, EventReceiver attached and with Content Rules. The default execution sequence will be the following:

  1. WorkFlow
  2. Event-Receiver
  3. Content Organizer Routing Rules

PS: Take into account that WF/EventReceiver sequence can be changed programmaticaly, using Sequence property of SPList.EventReceivers, but when you reconfigure Workflow it always gets the first order in execution

PPS: Content Organizer Routing Rules impersonates the Application Pool account, thus in might perform actions under “System Account” and your destination folder’s WorkFlow will not start.