Share this:

Like this:

If you have ever worked on SharePoint workflows, you will notice that sometimes your task list gets crowded with many entries, and you want a way to query that task list to get some information, like number of pending tasks, tasks overdue, or any other kind of information.

For example, I may want to create a PowerShell script to query a specific task list, and send weekly report to each user about his pending tasks via email in a nicely formatted HTML table. While SharePoint workflow task actions can send overdue emails, it will do that per task, and not by aggregating all pending tasks per user.

People do not want to get email for each pending task, they want instead to have a weekly or daily email showing all their pending tasks. You can do anything once you know how to interact with SharePoint lists using PowerShell.

I will not give you a ready PowerShell script to do all the magic, but instead, I will show you how to start doing that, and how to use PowerShell to get the essential data. You can then use your PowerShell skills to do whatever you want with it (Send email, create an HTML table, aggregate data..).

First of all, you must load the SharePoint snap-in inside your PowerShell host environment.

Now that we are connected to the list using $List variable, $List is an array of all items in that list. So for example, to get the first item of the list:

$FirstItem = $List[0]

This will return to us the first item of the list, and since we are talking about task list, we can expect this item to have all the columns in a typical task list.

To get this tasks’ due date:

$FirstItem["DueDate"]

Now we can go and get task assignee for this task. This can be tricky as this field is of type (People or Group), and you may need the user/Group ID, or perhaps the email address in case you want to send them email notifications about their pending tasks:

This will give you the DisplayName and email address for task assignee. Although it says User.DisplayName and User.Email, even if the task assignee is a group, it will bring the group email and displayname also.

Finally, you can also do some cool stuff. Instead of getting all the task list items and store them in the $List variables and then looping through those tasks, it is much easier if you only get the items you want. Suppose you only want to process tasks not completed and due. To do this, you will use a CAMEL query to query only those tasks. Here is an example:

#We will set the Enddate to today
$EndDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now)
#we will define a query that will get only tasks that are NOT Completed and DueDate less then Today
$caml='<Where><And><Neq><FieldRef Name="Status"/><Value Type="Text">Completed</Value></Neq><Lt><FieldRef Name="DueDate"/><Value Type="DateTime">{0}</Value></Lt></And></Where> ' -f $EndDate
$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
#Then will only get list items matching the query we did
$ListItems=$list.GetItems($query)

Now the sky is the limit. You can do any kind of crazy thing using the above commands 🙂 Enjoy.

Like this:

I want to share with you a funny situation while writing extremely long and complex SharePoint workflow. I was creating a workflow that controls deployment requests and there is a requirement to throw an approval for 9 stages. After each stage, there is couple of email notifications and web services to call.

The code got extremely big that when I tried to publish my workflow code inside SharePoint designer, I got this message:

“The request message is too big. The server does not allow messages larger than 2097152 bytes.”

It seems I reached the maximum allowed size for a workflow code definition :). After opening a case with Microsoft, we came up with couple of workarounds:

Divide the workflow to smaller workflows

Re-write the workflow code in a way that redundant code can be removed.

Increase the upload limit of 2097152 bytes.

I worked with Microsoft engineer to apply the third workaround by applying the following PowerShell code on all SharePoint front end servers, and doing IIS reset after that:

This will increase the upload limit to 5 MB instead of the default 2 MB. When I initially try to get the default value of the above values, I got empty values, so me and Microsoft support engineer assumed that empty value is actually 2 MB.

I hope this will help someone out there. Of course, the best practice is to do smart coding for workflows so that the code never exceed 2 MB for performance reasons as per my talk with Microsoft engineer.

Tip: How you can know the size of your workflow code?

Open SharePoint designer, Go to Workflows, pick your workflow, click Save As Template. This will save the template to “Site Assets“.

Next go to Site Assets, you will see your workflow file with WSP extension, along with its size. This size does not reflect the actual size of your workflow code. Now go and click Export File, and save it to your desktop.

Now go to your desktop, and change the extension of the exported file to .CAB , then double click the file to open it:

View other parts:

In this part of the tips, I want to talk about abstraction concept. When you write a good workflow code and everything works just fine, you should not face a situation where you open the SharePoint designer to do some changes unless there is a major change in the logic of how the workflow works.

If you have a change management request list that is served by your workflow that requires three approvals, and you wrote the workflow code, you test your code, you put the workflow in production, and everyone is happy with it, then theoretically speaking, you should not have a case where you need to open the workflow code again. Even if one of the approvals is changed to someone else, this should not be a cause to change the workflow code.

This can be accomplished by introducing a level of abstraction, by not hard-coding values inside the workflow code, and by maintaining all configuration values in separate SharePoint list, that your workflow will read from in real time.

So now, you shall create a SharePoint List called (Workflow Configuration) for example, with two columns:

Configuration Name (Single line of text)

Configuration Value (Single line of text)

You then start working on populating this list with any value that you may use in your workflow. Examples are:

If you assigning tasks, then you can put the task due date and task title in the configuration list.

If you are calling web service, then you can put the web service URL in the configuration list.

If you have any static values or counter values, you put them here also.

Any switch values, for example, you may have a variable that is named (SendNotificationEnabled) and if it is true, your workflow will send notification. You can read this variable from the configuration list. That way, you can change the variable value from the configuration list without opening the workflow code.

Inside the Workflow Designer, you can read values from the Configuration List:

Just thing of this case with me. Your workflow is calling a web service, and you kept the web service variables in a confirmation list like we did here. If the URL of the web service is changed for any reason, then you can ask anyone to change it from the configuration list. No need for you to go and open the Workflow SharePoint designer, and do some changes, and hit the scary Publish button.

I usually do not keep any single hard-coded value in any of my workflow code. Usually, when I write a workflow code, I do not open the code again until there is major changes the affects the way the workflow logic is happening. Any other changes, or customization are all kept and maintained in a separate configuration list. This even include the subject of workflow email notifications.

Like this:

We talked about the workflow log types (Audit and Debug), and we agreed that using the built in “Log to History List” action inside SharePoint designer is not a preferred way from my personal point of view. So what is the better way to do logging?.

You can think of a SharePoint list as a database table. It has columns and each column has specific type. Tables in a relational database model can relate to each other using keys. In SharePoint, lists can relate to each other using a column type called (Lookup) in the same way.

Example

We have a process to log changes happening in data centers. We need to create a process to track those changes and require an approval for every change. Finally, auditors will require some kind of logs to be exported as a proof of the integrity of such change control process. You also need some debug logs for troubleshooting purposes.

Let us start designing the solution by defining couple of content types. If you are not familiar with content types inside SharePoint, I suggest you start learning about this concept. In my own words, content types are like creating a class in any programming language. You create a class, define some properties and the data type for each property, and then you can create instances of the class whenever you want.

Same thing applies here.Content Types are like classes, and Site Columns are like Class Properties. If you are still not that comfortable with content types, just create normal lists and columns, but in this example, I will use content types just because I like to do that.

We will create three content types “Parent Content Type shown in the picture”:

When you are inside the SharePoint designer, and you want to throw a debug message, choose action (Create List Item), pick the Debug Log list, as per the following:

As shown, you have to populate the (Change ID) with (Current Item:ID), and then fill the (Debug Message) with the log content you want.

The same applies when you want to create an audit message. Finally, you can go to the Debug Log List and the Audit Log List, create a view with (Group By) using the Change ID field, and you will get all debug messages and audit messages per Change ID.

You can use SharePoint Information Management Policy to apply retention policy to purge the debug messages from the Debug Log List after say one month, and another retention policy to purge the audit messages from the Audit Log List after say one year.

I hope this highlight some of the great benefits from using such approach to create and maintain logs generated from your workflows.

Share this:

Like this:

We have talked in part one that the workflow logs can be classified as Audit Logs or Debug Logs. Audit logs have long retention and are used by auditors and security teams as a proof of a controlled process, while Debug logs have low retention and are used by the people maintaining the workflow for troubleshooting purposes and to track the execution state of the workflow at different execution times.

SharePoint Workflow Designer gives you a built-in way to through some log data to a history list using an action (Log to History List). I am not fan at all of using this way of logging for different reason.

Let us talk a little bit about the Log to History List and about the History list itself. By default, there is a hidden list that get created by default when you first create your first workflow in a site called History List. You can use this history list for different workflows or you can choose or create different history list for each workflow.

One of things I do not like in such history lists is that it accept only single string at a time. There is no other columns in that history list that you can benefit from. Not that this is a big limitation, but I do not like to be restricted with only sending a string at a time. It makes it hard to filter and analyze the log data as I will described later because of the lack of other columns.

Second thing is that a workflow can be associated with only one history list to be used for logging. Take this example: You have a workflow that calls a web service, and you want to log the status code or perhaps the returned value from that web service call. The only option you have here is to use the Log to History List. You also want to log other events during the workflow execution. Now, someone came to you and ask you to give a report of all web service calls and their return code for analysis. You have to go and open that hidden history list from SharePoint designer, and then what you will see? You will see a lot of lines without any ability to filter the logs related to the web service calls and to track them back to the list item that cause the service to start. My point is it is very hard to look at the history list and track certain events, or do any kind of filtering.

Also we talked about two types of logs, audit and debug logs. Your only option here is to use the Log to History List and through logs related to auditing and other log entries (debug data) for you to troubleshoot the workflow. Now when the auditors ask you to extract a report for a certain item, the logs are mixed between audit and debug. Also, you may want to keep log entries used for auditing for longer time than those used for debugging, which you cannot do in this case because both are saved in the same history list.

Things become more interesting when you read about the “Workflow Auto Cleanup“.It is not best practice to disable this job or change its duration by the way. This job will remove the association of the workflow tasks and history data after 60 days by default. You can read more about this here. But what about the need to keep audit data for one year for example?. What will do then? People will disable this timer job, but Microsoft keep saying it will affect the performance of the product or something. If Microsoft implemented this timer job then there is a reason.

I think for a professional workflows, especially those that requires auditing, you should not use the built in way (Log to History List). I will describe the way I prefer in the next part.

Like this:

If you are responsible of writing SharePoint workflows using the SharePoint designer, I want to share with you a small tip when it comes to using the SharePoint designer console.

Usually, you have SharePoint servers and perhaps Workflow Manager in your data center, and you may have installed the SharePoint designer at your machine and connect remotely in order to start coding workflows.

What I do not like in this case is the dependency on the link. Sometimes you work remotely from a hotel room connecting to unreliable wireless network, connecting VPN to your corporate network, opening the SharePoint designer console from your machine and opening a very big workflow definition code, do some modifications and hitting Publish. You do not know what will happen if the network connectivity is not reliable.

What I prefer is to have a Remote Desktop server in the data center to do administrative tasks for different thing. You can then install the SharePoint designer in that remote desktop server, and then you can log remotely to that server and open the SharePoint designer from there. That way, when you hit Publish workflow, the changes will be pushed from the terminal server to that SharePoint farm without depending on any unreliable connection. Furthermore, I also have exported the SharePoint designer as a remote web app and copy it to my desktop. Whenever I want to use the designer, I just open the RDP file in my machine, which will connect using RDP to the RDS server in the data-center and give me a great experience.

Even if you connect from your hotel room, connecing via unreliable wireless network via VPN to your corporate network, you will RDP to that RDS server, use the SharePoint designer from there, open your big workflow code, do your changes, hitting Publish, and you do not worry about anything. In fact, you can close your VPN connection and the background, the SharePoint designer will take its time publishing your workflow changes without any networking issues.

Like this:

I want to talk about metadata, SharePoint Workflow History logs, and how to use this data for different purposes. People underestimate this part when thinking about workflows, and just focus on how to do the workflow logic.

I usually classify the log data the comes out of a workflow into two types: Audit data and Debug data.

Audit data is the data that auditors ask for in order to validate the process integrity. If you have a solid change control policy in place, and you have a workflow in SharePoint to control a process, auditors usually ask for proof in form of workflow logs. For example, if you have a workflow to control the process of creating service account in active directory, auditors will come and will ask you for a proof that these accounts are created in active directory after passing through a workflow approval cycle. To do that, you can code your workflow to generate audit log data to be exported and shown to the auditing team once requested.

Let me give you another example. I had a solution that is used to track new changes in a data center. To do that, I created a list in SharePoint and anyone can submit a change request that passes through couple of approvals. Auditors and security team will come every quarter and ask: “Give us a proof that any change in the data center is logged and passed through approval”. IT Admins will then go and export the Audit logs as a proof. Auditors also require audit data to be available for a full year for any change happening in the data center.

Audit data is used mainly for security reviews and should have long retention (one year for example). The main purpose for such log is only for auditing and proof that an approval cycle is in place to control the process actions.

On the other hand, SharePoint workflow logs can be Debug Logs. Debug logs are used mainly for the team working on writing and supporting the workflow. If you write a complex workflow, you would like to through couple of logs at certain points of the workflow life cycle to track what the workflow is doing and if it is working as expected.

Suppose you have a workflow that calls a web service, sends couple of email notifications, and start approval requests. For you, it would make sense to generate a log before calling the web service, and after you got the web response back, and perhaps log the response code, to make sure the web service call is working just fine. Also, you could log that you are trying to send email notifications now, and that a task is generated for this person waiting for his approval. All these logs are only used for you as a workflow programmer to track the workflow status and action at different points of execution.

Usually debug logs will have a very short retention, as you only want to keep such logs for a month in most cases.Auditors and security team do not care about these log messages.

In the next parts, I will start talking about how to plan these logs and why I personally do not like to use the built in workflow history for this purpose.

View other parts:

Entities and flow charts

After planning the whole process with the right people, sit and start drawing flow charts of how the workflow will act and behave. This visualization will help you a lot when you start coding and testing your workflow implementation.

Sometime your solution will require couple of additional lists to host some data. Try to write down the columns names and types for such lists from the beginning and adopt a naming convention if possible.

What I do usually when I am planning a solution that requires workflows, that i draw the workflow flowcharts, and then for each list that I need to create, I draw an entity drawing. I like to think of Lists as Classes in .NET programming. So for example, If i need to create a list for auditing and one to hold some analysis information, I will draw the following entities, for each entity, I am listing the properties and types [column names]:

Do not test with Privileged Accounts

When you start writing your workflow code, do testing along the way for each step. My tip here is to test your workflow process with a normal user account and not with your admin or any high privileged account. I cannot emphasis how important this is.

I worked on a complex workflow once and everything worked fine as I was testing with my admin account. As the deadline become closer, I asked someone to test the workflow process with his account, and NOTHING worked. I figured out what was the problem later of course. The point here is never to test with your account or any privileged account.

Create testing environment

This is also a very good thing to do and it is a must in the development world. If you are coding a simple workflow for a list or document library, then try to create a test list or document library and code your workflow on that test list/library first.

This becomes handy when you later want to do enhancements or change something on the workflow after people start using it for long time. You will be afraid to change a working and live workflow because you cannot know if your change will affect current working users.

Instead, if you have a test environment, you could introduce your change there, test it, and then carry it safely to the production list/library.

I worked once on a solution that requires a separate sub-site, with many lists and workflows. So I created a test sub-site, create all lists and workflows there, I then did my whole testing there, and once I confirmed everything is working fine, I started to create the live sub-site with the same lists and workflows I had in the test one.

Later on, when I want to introduce new changes, I would test them in the test sub-site first so that I do not interrupt the live environment. Sometimes, when you did bad changes, the whole workflow won’t run and people start calling you. So test your workflow changes in test environment first.

View other parts:

Use Comments

One of my favorites features in SharePoint Designer is the ability to add notes for you and others to later open the workflow code and understand what is happening.

You can add comments inside SharePoint Designer by adding an action called Add a Comment.

It is always good practice to make sure to add comments that describes what you are doing inside your code.

Workflow Variables

When writing workflows using SharePoint Designer, you usually need to create many variables.

Make sure that all your variables have names that are descriptive. Adopt naming convention for your variables. If you are using web service calls, try to prefix the variables needed for the web service with something like (WebService…).

When you create an item in a list, workflow designer will automatically create a local variable of type GUID called (create) that represents the GUID of the item created by the workflow designer on that list.

What I usually do is to create a variable with type GUID, and called it (MyReports GUID) and use it instead of the automatically created on. I then go and delete the variable called (Create). This way, my variables will be more descriptive.

Sometime, when you are beginner in writing workflows using SharePoint designer, you will end up with many variables that are created and not used inside your code. Keep an eye on those orphan variables and delete them as soon as you can so that you do not confuse yourself. This will make it easier for you and others to review your code later and get an idea about what’s going on.

View other parts:

If you are like me writing complex workflows using SharePoint 2013 Designer, it is so important to take deep breath, and remember that it is not always about just writing some code. It is the whole thing from taking requirements, doing some documentation, and follow general best practices. I will do my best to share with you my personal experience when I got any requirement for a new workflow.

I guess Microsoft did a great job in creating such authoring tool (SharePoint designer). I love the tool and I spend a lot of time working on complex workflows.

Gathering Requirements

Again, it is not only about writing a wonderful code for your workflow. The last thing you should do is start writing workflow code.

One of the most important things to do when you planning a workflow for a process is to identify in a clear way the full requirements and process life-cycle. Do not underestimate this step.

I participated in a process that requires a complex workflow. I scheduled meetings with almost all people participating in the process to take their requirements, and when the workflow went life, I discovered that the security team stopped the workflow to go live because of lack of audit data and there should be clear logs that should be maintained for a year. I had to take their feedback and work with them to generate the log data with all the details they wanted before going live.

Take last feedback before going live

I worked on a workflow once and instead of using (Log To History) workflow action inside the SharePoint designer tool, I used a separate list to maintain the workflow history logs. Nothing fancy at all.

Well, it was like the whole world is against me when we introduced the workflow solution. The auditors are expecting to open the list item, see the workflow history information inside the workflow section inside the list item. When I told them that there is separate list to maintain history log data, I got a lot of change resistance from them. They asked me that they need couple of information to be added to the separate history log list to approve the workflow to go live. Lesson learned in the hard way!

Do not underestimate small changes. I think people are hard-coded to see things the way they are used to. Any small change from your side when planning your workflow solution need to be clearly communicated.

User experience

When I plan a solution that needs a workflow, I usually design the lists needed, the look and feel, I do some GUI enhancements and customization, and do some naming standard for all lists and fields.

This becomes tricky when people are already using an old solution, and you came to introduce a new one using SharePoint and its workflow engine.

Always make sure to communicate with your users or people managing the process, all your actions, from list names, fields names, and any customization.

One time I was designing a dashboard for people to see their pending tasks, and I called the web part section (Tasks Waiting Me). The immediate feedback I got that this name is confusing and it should be named (My Pending Tasks). OMG!!!

Even if you are working with a highly educated environment or even IT teams, and you are writing workflows for them, always make sure to early communicate and manage their expectations, and show them detailed draft of what you are planning to do.

Do Not Over Do

If you have simple requirement, just stick to it. Make sure everyone is happy, and start adding enhancements later.

I had a requirement once to do 5 approval workflow cycle with some email notifications. I took this task personally and I challenged my self to do the best workflow experience ever. I started to plan for dashboards, new SharePoint site to host the solution, extensive reports and logs.

When I started to communicate my design for the solution, and instead of getting some credit for my extra work, people start to realize that much more can be done, and they started to request more and more complex requirements that did not exist before. Someone told me “Well, if you can do such dashboard, why not you do a separate dashboard personalized for each user that aggregate data from….. “. That same person added this new dashboard as a new requirement that I should deliver next month. Funny right?

Share this:

Like this:

I was working on a project to implement a process for three stage approvals using SharePoint workflows.

Someone will submit a request, it goes for first approval (Stage 1), if approved it will go to the second approval (Stage 2), and finally if approved a third approval is required (Stage 3).

You can simply do this with SharePoint. The challenge is how to present such solution in a visual way and create a dashboard to present the current request state. So at any point of time, it is nice to see where the request is standing in terms of approvals, and perhaps a dashboard showing this in a nice way. After all, a picture worth a million words.

How to do this?

We need to create a list called (Submit Request). But keep in mind that we need to have some hidden columns in this list to host some metadata that we may use to create the dashboard.

The best way is to use SharePoint Content Types. Content types are reusable structures that you can attach to list and libraries to describe the columns schema from central place.

Creating Content Type

Go to Site Settings > Site Content types > Create

Name: Submit Request Type.

Select Parent Content Type From: List Content Types.

Parent Content Type: Item.

Then start adding columns by clicking (Add from new site column). You can add columns like (Request Title), (Request Reason), and any other data you need.

The most important thing is that you create a new column called (Photo) with type (Single line of text).

Now, let us move and create a new custom list) and name it Submit Request. Then opening the List Settings> Advance Settings> Enable Management of Content Type.

Then click Add from existing site content types, and add the newly created one (Submit Request).

The new content type will appear now as an available Content Type

Finally, click on the content type called (Item) and then click Delete this content type.

Finally, we will go back to the (Submit Request Type) content type, and mark the Photo column as hidden.

Creating Picture Library

The next step is to create Picture Library, and add to it a photo that describes each possible outcome from submitting the request.

Since our request consists of three stages, then we need total of 6 pictures:

HTTP 500 ID3242: The security token could not be authenticated or authorized.. bla bla bla.

I restarted every single SharePoint server, i even restarted the workflow manager server, go through event viewer on all servers without any luck. I checked if there are any new windows updates installed, and the answer is no.

The SharePoint farm is running Nov 2015 CU by the way.

Many people are talking about making sure that the Claims to Windows Token Service is started in SharePoint server. Well it is started.

After digging around, i found this event log on one of the back end SharePoint servers:

An operation failed because the following certificate has validation errors:

PartialChain: A certificate chain could not be built to a trusted root authority.RevocationStatusUnknown: The revocation function was unable to check revocation for the certificate.OfflineRevocation: The revocation function was unable to check revocation because the revocation server was offline.

So I went to certificate console on the server, Computer Store, SharePoint ,Certificates:

All these three SharePoint certificates when you open them, it shows that it cannot verify the identity of the certificate. This means that the SharePoint cannot locate the trusted root certificate for those three certificates.

SharePoint has a PKI Root Certificate called (SharePoint Root Authority). All what I need is to locate it and then import it to the Trusted Root Certification Authority node in the Certificate Console that I am already opening.

To pull that Root Certificate (Reference Link), open SharePoint PowerShell session and type: