Approval of major version documents in Microsoft Flow

This fall I have been working with modernization and upgrade of a solution build in SharePoint Online. The solution used SharePoint document libraries and custom lists as data sources and SharePoint Workflow for approval of documents. On top of all this, a SharePoint App hosted in Azure (not really relevant in this context though).

So, apart from upgrading the SharePoint App to modern technology (SPFx), the job included recreating the approval workflows built in SharePoint Workflow - in Microsoft Flow.

One of the requirements for this workflow, was that it shouldn't start unless a user checked in a major version of a document. Now, I soon found that Flow doesn't have any triggers or actions that handle this specifically. Flow provides triggers for when a new file is added and when a file is added or modified (in addition to a range of other triggers of course, but those aren't relevant here).

Keep in mind that the document library we are working with, has content approval turned on, as well as minor versions and required checkin/checkout. The most important thing here is content approval though.

So, how do we solve this? Behold, the Send an HTTP request to SharePoint action.
The only way to figure out if a file is awaiting approval (through the OOTB SharePoint approval mechanism) is to query the ModerationStatus field for the file. This field is a semi-hidden field. You can display it in your views, but it will not appear in edit forms.
Now, this field can have the following values (enum-value in parentheses):

• Approved (0)
• Denied (1)
• Pending (2)
• Draft (3)
• Scheduled approval (4)

I have used the When a file is created or modified (properties only) action

The next step is to add a query SharePoint for the ModerationStatus.

Go ahead and and a Send an HTTP request to SharePoint action and feed it with the following settings (change the values to target your tenant):

• Method: GET
• Uri: _api/web/lists/getbytitle('')/items()?$select=OData__ModerationStatus

Next, add a Parse JSON action in order to parse the response returned by SharePoint. Use the Body from the HTTP Request action in the Content field and the following JSON Schema:

{

"type": "object",

"properties": {

"d": {

"type": "object",

"properties": {

"__metadata": {

"type": "object",

"properties": {

"id": {

"type": "string"

},

"uri": {

"type": "string"

},

"etag": {

"type": "string"

},

"type": {

"type": "string"

}

}

}

"OData__ModerationStatus": {

"type": "number"

}

}

}

}

}

You can put the value (OData__ModerationStatus) in a variable or you can use it directly in a condition. The important thing is to check if the value is 2 - which is Pending.

From here on you can do your magic with Approval actions and whatever else you need.

Hope this was helpful.

SharePoint 2010 List Event Receiver - Available properties

I find the properties model of a SharePoint Event Receiver a bit confusing, and hard to remember. When can I reach the SPListItem's fields, when is After/BeforeProperties available?

The table was borrowed from this post: http://www.synergyonline.com/Blog/Lists/Posts/Post.aspx?ID=122.

List	BeforeProperties	AfterProperties	properties.ListItem
ItemAdding	No value	New value	Null
ItemAdded	No value	New value	New value
ItemUpdating	No value	Changed value	Original value
ItemUpdated	No value	Changed value	Changed value
ItemDeleting	No value	No value	Original value
ItemDeleted	No value	No value	Null

Library	BeforeProperties	AfterProperties	properties.ListItem
ItemAdding	No value	No value	Null
ItemAdded	No value	No value	New value
ItemUpdating	Original value	Changed value	Original value
ItemUpdated	Original value	Changed value	Changed value
ItemDeleting	No value	No value	Original value
ItemDeleted	No value	No value	Null

Happy coding!

Guide to SharePoint 2013 Service Accounts

This is taken from Todd Klindt's excellent blog, and I especially like the Domain Right and SQL rights needed columns.

Account name	Role	Domain rights	Local SharePoint Server rights needed	SQL rights needed
sp_install	Used to install SharePoint binaries.	Domain User	Local administrator on all SharePoint boxes	public, dbcreator, and securityadmin SQL roles. Need to be SysAdmin on SQL when installing the Workflow Manager
sp_farm	Farm account. Used for Windows Timer Service, Central Admin and User Profile service	Domain User	Local Admin during UPS provisioning, log on locally right	None
sp_webapp	App pool id for content web apps	Domain User	None	None
sp_serviceapps	Service app pool id	Domain User	None	None
sp_content	Default account used by Search Service Application to crawl content	Domain User	None	None
sp_userprofile1	Account used by the User Profile services to access Active Directory	Must have Replicating Change permissions to AD. Must be given in BOTH ADUC and ADSIEDIT. If domain is Windows 2003 or early, must also be a member of the "Pre-Windows 2000" built-in group.	None	None
sp_superuser2	Cache account	Domain User	Web application Policy Full Control Web application super account setting	None
sp_superreader2	Cache account	Domain User	Web application Policy Full read Web application super reader account setting	None
​sqlinstall	​Account SQL is installed with	​Domain User	None	​Local Administrator on the SQL box
​sqluser	​Account the SQL Instance is running as.	​Domain User	​None	​Will be given necessary permissions when SQL Server is installed by a local administrator on the SQL box

1) See http://technet.microsoft.com/en-us/library/ee721049.aspx and http://www.harbar.net/articles/sp2010ups.aspx

2) http://www.sharepointchick.com/archive/2010/10/06/resolving-the-super-user-account-utilized-by-the-cache-is.aspx

Those are the accounts needed for a base install of a SharePoint 2013 farm. However, there are other optional features that require their own accounts too. Here’s a list of accounts to use if you install the optional components.

Account name	Role	Domain rights	Local SharePoint Server rights needed	SQL rights needed
sql_ssas	Account that we run the SQL Server Analysis Service services as	Domain User	None	db_datareader on databases
sp_excel	Excel services unattended account.	Domain User	None	None
sp_pps	PerformancePoint Unattended account	Domain User	None	None
sp_accsvc	Access Services. Used to create all Access databases in SQL and the service account running the service app pool for the Access Service Application	Domain User	None	db_owner, public, and securityadmin
sp_workflow3	The RunAs account for the Workflow Manager service	Domain User	None	None

3) Further Workflow Manager requirements are outline here, http://technet.microsoft.com/en-us/library/jj193451.aspx

These are just suggestions, they are not written in stone, just guidelines. For instance, every SharePoint farm should have its own set of accounts. Production SharePoint 2013 should be a different accounts than a dev SharePoint 2013 farm. And a production SharePoint 2013 farm should have different set of accounts than the production SharePoint 2010 farm that’s being upgraded. You don’t want any cross-contamination. You don’t want an errant dev process jumping the stream and breaking production.

Also, make sure to keep your account names to 20 characters or shorter. Some places in SharePoint use the Pre-Windows 2000 version of the account name, and that can only be 20 characters. If your account name is longer than 20 characters you’ll get weird “account doesn’t exist” message when you know damned well it does.

While you’re creating accounts, you should also create an admin account or two so you aren’t tempted to log in as sp_farm. Bad! Bad!

Override values of Created By and Modified By fields

In some cases, it could be useful (or maybe even necessary) to be able to override the values in a SPListItem's Created By (Author as Internal Name) and Modified By (Editor as Internal Name).

So, how can you accomplish this? Simple!

web.AllowUnsafeUpdates = true;

item["Editor"] = string.Format("{0};#{1}", spUser.ID, spUser.Name);
item["Author"] = string.Format("{0};#{1}", spUser.ID, spUser.Name);

item.UpdateOverwriteVersion();

web.AllowUnsafeUpdates = false;

The key is item.UpdateOverwriteVersion(). item.Update() and item.SystemUpdate() won't work here. Created By and Modified By are read-only fields and aren't meant to be messed with. However, I have been in situations where I had to modify them using code.

----
Horns up \m/

Hide SharePoint 2010 controls from anonymous users

I was recently working on a SharePoint 2010 Internet-facing website for a client and needed to hide the Ribbon from anonymous users. I tried various approaches to achieve this, but finally I came over a great blog post (http://blogs.msdn.com/b/zwsong/archive/2010/04/29/how-to-hide-ribbon-from-users-without-edit-page-privilege.aspx) which introduced an ingenious approach on this.

The idea is very simple really. Make usage of the SPSecurityTrimmedControl  control, but instead of wrapping the whole Ribbon DIV inside it, hide the Ribbon DIV and wrap SPSecurityTrimmedControl around a small JavaScript that shows the Ribbon.

More technical how-to (slightly modified from the original post to fit my needs):

1. Open SharePoint Designer and open your site and your Master Page.
2. Locate:
   <div id="s4-ribbonrow" class="s4-pr s4-ribbonrowhidetitle">
3. Change it to:
   <div id="s4-ribbonrow" class="s4-pr s4-ribbonrowhidetitle" style="display:none;">
1. >
4. Find the end tag of "s4-ribbonrow" and add following code after it:
   <Sharepoint:SPSecurityTrimmedControl ID="spTrimRibbon"runat="server" PermissionMode="All" PermissionContext="CurrentSite" AuthenticationRestrictions="AuthenticatedUsersOnly">
       <script type="text/javascript">        document.getElementById("s4-ribbonrow").style.display = "block";
       </script>
   </Sharepoint:SPSecurityTrimmedControl>
5. Save your work.
6. That's it!

Now, this approach can be used to hide/show elements based on various rules, not just the Ribbon as illustrated in this post.

Hope you found this as useful as I did!

Thanks to Dr.Z for the original post.

Visual Studio 2010 - the most exiting new stuff

I attended the MSDN Live Oslo a few weeks back where they talked a lot about the new versions of Visual Studio 2010, C# 4.0 and ASP.NET 4.0. I'll present some of the most exiting (to me anyways) new stuff.

C# 4.0

• Named and Optional Parameters (no more overridden methods?)
• Late-Binding support / Dynamic Lookup
• Improved COM Interop
• Dynamically typed objects (to be honest, this is scary. Pitfalls!)
• Visual Debugger (visualization of your objects and their hierarchy)
• Tasks/Parallels (no more threading! This is far more efficient and easy to deal with)

Visual Studio 2010

• Historical Debugging (exiting!!! Step through your code as it ran on a live server!)
• Totally rewritten in WPF
• Improved designer (yet again)
• Added a lot of features like in ReSharper
• JQuery is integrated
• More visual modeling tools

ASP.NET 4.0

• Stripped down web.config file. Mostly everything moved to machine.config
• Extensive Output Caching. More flexibility on what to cache
• You can run start-up code for the application
• Session State Compression
• Individual ViewState
• Much better control of your controls' client ID (myTextBox actually IS myTextBox!)

There is lots of other new stuff too obviously, but I just wanted to share with you the stuff that I think is most exiting.Can't wait for the full release!

Creating a custom Field with TreeView in SharePoint 2007

I was faced with the task of creating a custom Field in SharePoint 2007 (WSS 3.0) which presents (in new and edit mode) a TreeView control with some hierarchical data. The user should click and select one node in the TreeView before saving the item.
Now, my challenge turned out to be what to save in the item. I had never created a custom Field before and still, as far as I can see, it's possible to only save one value for the field.
I needed to save both the ValuePath and the textual representation of the path to the selected Node.

The solution was to save both those values in the same string, separated by, by my choice, a '#' character. Now, in order to present the textual representation of this string in the Item's List's View, I had to implement a small JavaScript on the client to split the value and return the correct part of the string. The JavaScript looks like this:

function SplitString(theString) {
  var theStrings = theString.split("#");
  var theStringsLength = theStrings.length;
  if (theStringsLength > 1) {
     document.write("<div style='DISPLAY: block'>");
     document.write("<div style='WHITE-SPACE: nowrap'>" + theString[0]+ "</div>");
     document.write("</div>");
  }
}

Now, in Edit mode, it was straight forward to make sure to select the correct Node. I just did a split on the value again, and fetched to other value of the split (which in my case is the ValuePath of the Node) and did a myTreeView.FindNode(mySplitValue).Selected = true;.

This isn't very elegant and it hurts implementing this, but it works! I wish though, that there's a better way of doing this. I actually expected to have a 'View presentation' of the saved string (where I could format the string the way I wanted) and a 'Edit presentation' of the saved string. However, I couldn't find any. So, if anyone knows that it actually is possible and how, I would appreciate the solution :-)!

Have a nice day!