Archive for the 'SharePoint Workflow' Category


Retrieving Single Item from SharePoint List in Microsoft Flow

This seems like such a simple thing to do.  Select a single name/value pair from a SharePoint list used as an app configuration source.  I do this all the time with workflows so I can externalize email addresses, workflow participants, etc.  But in Microsoft Flow the rules have changed.  Here are a couple of design templates that can be used to get those name/value pairs storing your workflow configuration data.

In MS Flow, you’ll find two SharePoint actions:  Get Item and Get Items.  If you have an ID for a SharePoint list item your problem is solved.  Simply use Get Item and supply the list item ID.  This is handy when you’ve created a new item and need to reference it (update or retrieve it) during the flow execution.  It’s not quite so handy when you need to reference an item from another list and you don’t have the item ID.

Now on to Get Items.  Get Items returns an array of all the items selected from the SharePoint list, even if you narrow down your selection to a single item.  Once you get this “collection” you can iterate through it using an Apply to each loop to select the item you want.  I would argue, however, this is less efficient than selecting a single value and either referencing it with a first() function or by its index of [0].  You can actually see this difference in the execution of the workflow, as the Apply to each loop takes significantly more time to set up and execute.  Here are some examples.

Initialize your variable.


Using the Get Items action, select your site and list and create a Filter Query to select the item from your list.  This assumes the Title (the name part of the name/value pair) is unique.  My filter query: Title eq ‘AdminFinalApprovalEmail’, will select a single item but, just for grins, I also add the Top Count of 1 just to make sure.


Here’s what my underlying SharePoint list AppConfig data looks like.  It contains name/value pairs.  The Title contains the name and a column called Value contains the value.  I guess I could’ve been more imaginative on the column names, like Fred and Ethel, but this works just fine.  🙂


Now I add a Set Variable action.  I’ve selected my variable I initialized earlier, AdminFinalApprovalEmail. Notice how I rename my actions to something long and descriptive.  That’s from years of programming and trying to make my code self-documenting.  My new name is “Set variable AdminFinalApprovalEmail.”


For the Value I use this expression:


The body() function references my renamed SharePoint Get Items action.  Note that flow replaces spaces with underscores:  Get_items_Filter_by_AdminFinalApprovalEmail.  Now comes the somewhat confusing part.

The first ?[‘Value’]?[0] says to get the zeroth [0] indexed item from the Value portion of the body of the action Get_items_Filter_by_AdminFinalApprovalEmail.  There is only one item retrieved from the SharePoint list and this object contains all the fields retrieved for the item identified by “AdminFinalApprovalEmail.”

The next part ?[‘Value’] says to select the field named Value from the SharePoint results.  This is a bit confusing because I named the column Value which is the same name that Flow uses for its “Value.”  If the field name in SharePoint was “Ethel” it would look like this:


Hopefully you get it.

Now for the next way to do the same thing.  In this case we’ll adjust the expression to use the first() function.  I’m using a new variable called AdminFinalApprovalEmailFirst in this example.  I am still referencing the same SharePoint results retrieved using Get Items with the filter applied.


This is what the expression looks like:


We reference the same body() data but apply the first() function to the first [‘Value’] on the SharePoint data.  This essentially is referencing the [0] index, since it’s the only one that was retrieved.  Note the position of the parenthesis for the first() function!  We then select the SharePoint Value field from the SharePoint results using the second ?[‘Value’] selector.

You’ll find these to be very fast, as a single item is retrieved from SharePoint and assigned without a loop.  Now to show the for-each method.  Here’s an overview of the process.


In this example, I’m using Get Items without any renaming of the action.  Note there is no Filter Query applied, so all SharePoint items are retrieved from the list.


An “Apply to each” loop is used that iterates through the Value data.  The “value” object from Get_Items contains all the SharePoint list items; in this case there are eight items in the list.  It is essentially the body function:  body(‘Get_Items’)?[‘value’].


A condition action is used to filter the results.  In this case we are looking for items where the Title is equal to the string “AdminEmailCC.”  There is only one in the list.


Assuming it’s found in the list, the “yes” branch sets the variable value.  The SharePoint item field named “Value” is selected (it has the email address).  Note the code that goes along with this:



Now let’s look at the execution of all these options in a Flow test.  Note that the “Apply to each” loop is the time hog.


The “Apply to each” has to cycle through eight items in the list.


Seven of the eight results do not satisfy the condition.  Notice the Expression result=false.


The seventh item is the one we’re looking for, so Expression result=true.  At this point we can set the AdminEmailCC variable.


And to wrap it up, I send myself an email with the results.




Updating a SharePoint List with Login Name, Email Address, Display Name and User ID with a 2010 Workflow

Here’s the situation.  You have an email address and you want to display the login name (domain\username), display name (Russell Wright) and perhaps the user ID from the SharePoint user list.  Here are the steps to accomplish with a SharePoint Designer 2010 workflow.

We start by creating a custom list and add the following fields.


The main field is Email PP (email people-picker) that we will populate with an email address.


Using the people-picker for an email address can be problematic if you have multiple user IDs with the same email address.


However, you should be able to select the correct one you want to use.


If you pick an email address that is related to a single AD account, you shouldn’t have this issue.


You’ll notice the workflow named Set Fields executed and populated several other fields.  See the Completed link under the Set Fields column?  This link will take you to the workflow history list.  Let’s see how this is done in the workflow.

We’ll start with a simple list workflow created on our custom list.  The start options are set as shown.


Begin by creating all the variables you’ll need.  We will create string variables for each attribute we are dealing with.


Using the action Set Workflow Variable, we’ll read the Email PP field and set a variable for each variation of the field we want to set in a text field.  In this example we create the variable named LoginName and set its type to string.


We then set its value to Email PP from the Current Item.  The important thing is to return the field as Login Name.


Repeat this process for each variant of the people-picker field you want.


As a matter of good practice, log the fields to the workflow history list so you have a record of what they look like.


This action will give you an entry in the workflow history that will display the values of your variables.


Now, on to the step to set the fields in the list, using Update List Item.


Insert an Update List Item action.


Here you can set each field to your variable values.


Here is an example of setting the LoginName Text field with the LoginName variable.


And the final result should display multiple attributes of the person in the people-picker field.



Workflow Status Field Name/Value Pairs

If you need to check the status of a workflow within another workflow, you can check its status value.  These values can also be used to create filters within views.

  • Not Started 0
  • Failed on Start 1
  • In Progress 2
  • Error Occurred 3
  • Canceled 4
  • Completed 5
  • Failed on Start (retrying) 6
  • Error Occurred (retrying) 7
  • View Query Overflow 8
  • Canceled 15
  • Approved 16
  • Rejected 17

Not sure what the values 8-14 are.


Date and Time Formats in a Workflow

This is just so I can remember what the date and time formats look like when you reference the current date in a workflow variable.

Here’s a short workflow


and here are the results.



SharePOint Designer 2010 Workflows Still Process Out of Order

One of the issues with SharePoint 2007 workflows was the actions within a workflow could process out of order due to the asynchronous nature of the workflow processing.  In order to work around these “race” conditions you had to put a checkpoint or commit in your workflow.  This usually means you put a pause in your workflow to make the workflow serialize and de-serialize in the database, thus causing a commit to occur.

The same issue exists with SharePoint Designer 2010 workflows.  I just had an “update list item” action update the incorrect list item, even though it was specified with a unique key.  In many of my workflows I create a name/value pair list that stores values I need to pass around.  In this case I had a “LastMonthProcessed” entry and a “MonthlyControlsListURL” entry.  Each time I updated the “MonthlyControlsListURL” key the value would end up in the “LastMonthProcessed.”  After putting a 1 minute delay (pause for duration) action after the update of the “LastMonthProcessed,” all of the updates occurred correctly.


You’d think some clever MS programmer would fix this issue once and for all…or they would a least give you a commit action in SPD.  Arghhhhh!


Give Blood to Your Workflow – A workflow Scheduling Application

Republished from original post in 2009.

Give Blood to your Workflow

Give the gift of life.

What does this have to do with SharePoint workflows? Last week I was teaching our Mission: Automation class where we cover InfoPath and workflows created with SharePoint Designer. One of the things we like to do in these classes is take some ideas from our students on business problems that they are trying to solve using SharePoint workflows and attempt to outline the solution and partially implement it during class (if it doesn’t become overly complicated). This particular idea was generated by Janice and here was her problem.

Janice is the blood drive coordinator at her place of business. She wanted to figure out a way to use SharePoint and InfoPath to allow people to register for times to give blood. I thought this sounded like a great example of a scheduling application that many people could find other applications for, so I started down the road of creating the “Blood Drive Scheduling Application” during the class. It uses an InfoPath form that an employee fills out to reserve a time slot for donating blood. In this case, there are two donating stations and each one is scheduled at 15 minute intervals during the day. In other words, for each 15 minute interval during the day, we can schedule a maximum of two people to donate at one time.

If someone changes their mind about the time slot they’ve reserved, we want to give them a way to make a change. However, we don’t want to give them the ability to change the original form…they will simply request a cancellation and submit another form for a new time slot. Some of this has to do with who has security to the different lists that we’ll be using in SharePoint to solve this problem.

Now, before we get started let me say that there are a number of ways to solve this problem. I was going for a solution that could be implemented during the class and illustrated some of the features of InfoPath and SPD workflows. This is only one solution. Much more elegant solutions could be implemented!

In this solution we’re going to use several lists. Blood Drive Reservations will be a forms library where we’ll store the completed InfoPath forms. Departments contains a list of the departments at the company. Time Slots contains a list of all the available time slots during the day when a donation reservation can be made. We’ll have a Cancellations list that we can use for someone to request a cancellation of their reservation. Finally, we’ll use the built-in Tasks list to assign tasks when a cancellation is requested.

First we’ll start out by creating a custom list that has two columns. One is the time for each of the 15 minute time slots during the day that we want to schedule and the other column is for recording how many reservations have been made for the time slot. Here’s what the table looks like in datasheet view.


I created all the time slots for 8:00 AM thru 5:00 PM.

Next up is to create the InfoPath form and library in which the completed forms will be stored. The form is pretty simple for our example. Greg suggested we add the department field so there could be some competitive statistics compiled between the different departments. Nothing like a little competition during blood letting!


The four fields defined in our form’s data source are DonorName, DonorEmail, DonorDept and TimeSlot.


The DonorName and DonorEmail are populated using default values obtained by calling the GetUserProfileByname method in the web service, UserProfileService, referenced at http://server/_vti_bin/userprofileservice.asmx. This web service will return name/value pairs from the user profile store that is part of SharePoint and normally configured to synchronize with Active Directory. The PreferredName and WorkEmail are used in this example.




The DonorDept field is populated by creating a data connection to a SharePoint list containing departments. Nothing really complicated here.


Now, the time slot field is populated in a rather unique way. The idea here is to filter the available time slots based on the value in Counter field in the Time Slot list. We’re going to be updating the Counter field with a workflow whenever a new reservation is submitted. We need to create a view on the field that is filtered to only show time slots when the value of the counter is less than 2. I created a view called AvailableTimeSlots and added the simple filter as shown.


Now we’re going to use a trick to populate the DonorTimeSlot field. We’re going to create a data source to an XML file and reference the SharePoint URL protocol supported by OWSSVR.DLL. This is documented in the WSS SDK and is referenced as follows. http://server/path/sitename/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={ListGUID}&View={ViewGUID}. The exact URL for our example is http://portal.awbikes.local/sitedirectory/bd/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List={BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}&View={35F16EC8-64E9-4BCE-8C8A-CD3D142AD894}.


The GUIDs for the List and View can be found by selecting the Modify View link in the view selector and decoding the GUIDs in the URL that is displayed.



The List GUID and View GUID are URL encoded, and you can “un-encode” them if you want to (but you don’t have to) by replacing %7B with a left curly brace { and %7D with a right curly brace }. %2D can be replaced with hyphens. In the example above, %7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D becomes {BAA912CA-D4D4-4786-B2B8-B33DA8691CA4}.

In the next dialog on the Data Connection Wizard, choose the option to Access the data from the specified location.


Give the data connection a name and make sure you automatically retrieve data when the form is opened and click Finish.


You can see the completed URL n the Summary section. I like the way the URL looks when the GUIDs are un-encoded…just easier on the eyes.

On the list box entries for the DonorTimeSlot field you need to reference this data source, OWSSVR, and select the entries as shown.



This will populate the DonorTimeSlot list with the filtered list from the AvailableTimeSlots view.

When we publish our form we will promote all of our fields so they appear as metadata columns in SharePoint.


Now, on to our first workflow.

The first workflow is fairly simple. When a new form is added we want to increment the value of the counter in the Time Slots list for the time slot selected. If two people select the same time slot, the value will be incremented to 2 and the time slot will no longer show up as an available time slot on the drop-down list in our form (because of the filter we created for our AvailableTimeSlots view). Within this workflow, we can also send a confirmation email to the donor. If we really want to get fancy, we can send a reminder email 24 hours before the scheduled appointment. Here’s the workflow in SPD.

First, let’s calculate the new counter value by retrieving the value currently in the time slot and adding 1. We’ll save this calculated value in a variable called IncrementedTimeSlotCounter.


To retrieve the current value of the counter we perform a lookup which reads as

Select Counter from Time Slots where Time Slots:Title equals Blood Drive Reservations:Donor Time Slot.

We are matching up the Title in the Time Slot list (e.g. 8:15 AM) with the time slot selected by the donor on the form from the drop-down list. Then we simply add 1 to it and store it in a variable.



Next we update the value of the counter in the Time Slots list. In this example, we’ve created another step in the workflow to do this.



Here’s the screen shot of selecting the Counter field to update in the Time Slots list.


In the “Find the List Item” section, here’s the lookup. Again, we’re matching up the Time Slots:Title field with the Current Item:Donor Time Slot field (Current Item being our item we’re working on in the Blood Drive Reservations list).


Now let’s create a workflow that sends a confirmation email and a reminder email. We’ll send the confirmation email right away and pause until early in the morning on February 3rd and then send a reminder email. We can do this with a new workflow that also starts when the item is created. It is okay to have multiple workflows running. Here’s what it looks like (nothing fancy). We can reference the Donor Time Slot in the email by clicking the Add Lookup to Body button.


The second step we’ll use the Pause Until Date action and put in a pause with a hard date of February 3rd and a time of 4 a.m. Just a quick and dirty reminder email.


We’re all set on the reservation side. Now, on to the cancellation process. Here’s the way the cancellation process will work.

We really don’t want end users to be able to delete reservation in the list and we can control that by creating a permission level that is similar to Contribute but without the permissions to edit and delete. We can alter the security settings on the reservation list so that our Visitors have this permission level for the reservation list.


If someone wants to cancel, they can insert an item in the Cancellations list. They can select themselves from a drop-down list of people who have reservations and click OK to create the request. I chose to do it this way so we can get a good match on the Donor Name. There are other ways to handle this, but this is quick and dirty.


Once they create a cancellation request, a task is assigned to Connie via a workflow. In this workflow I Iogged some variables to the workflow history list for debugging purposes and then assigned a task to Janice, er, I mean, Connie using the Collect Data from a User action. Logging information to the history list is a very valuable technique for troubleshooting and validating information in the workflow. I normally use three actions in sequence to do this: Build Dynamic String, Set Workflow Variable and Log to History List.


Tasks are very important to understand in workflows. They are so important they get their own categorization in the Actions selection list.


Collect Data from a User allows you to assign a task and, when that task is edited, a custom form is displayed with fields on it that you define in the workflow. In this case we are going to ask Connie if it is okay to delete the reservation with a Yes/No drop-down. Another workflow, Delete Reservation, was created to start on a change to the Tasks list. It’s job is to determine what the response was (Yes or No) and lookup and delete the reservation. One of the reasons this method was chosen was because the workflow runs with the credentials of the person who started it. Connie will have the permissions required to delete the item in the reservations list, while the donor does not. It also allows her to verify that the person has requested to delete their reservation and not somebody else’s. Here’s what the Collect Data from a User action looks like.






Next we’ll take a look at the Delete Reservation workflow. This workflow is created on the Tasks list. It has three steps. In the first step we set some variables that we’ll use in the workflow. These are very important because we are looking up the IDs of the Blood Drive Reservation we need to delete as well as the Time Slot we need to decrement so it is again available. The key to these lookups begins with the fact that workflow tasks hold some very important information. The contain a “foreign key” back to the ID of the item in the original list.


The first thing we need is the ID of the reservation we might be deleting. To get this we choose to lookup the ID field from the source Blood Drive Reservations. We are going to find this by first matching the name selected in the Cancellations list with the Donor Name in the Blood Drive Reservations list. To find the correct item in the Cancellations list, we need to reference the Workflow Item ID in the Tasks list (our current list) and match that to the ID of the item in the Cancellations list. It is very important to understand that you have access to the Workflow Item ID in the Tasks list. This can be very confusing if you are new to SharePoint Designer workflows, but with some patience it can be understood without having to be a programmer.


Once we have the ID of the reservation, we can find the ID of the time slot that is referenced on the reservation. We’ll use this to update the counter on the time slot. We want to find the ID of the time slot so we match the Donor Time Slot on the Blood Drive Reservations list (e.g. 8:30 AM) with the Title on the Time Slots list. We can find the Donor Time Slot by matching the ID of the Blood Drive Reservation with the ID that we looked up in the previous action and stored in the variable ReservationID.


The next step is just some more logging of messages and variables to the history list for debugging purposes. In this example I’m logging the IDs I looked up to insure that I have the right ones.


The final step is to actually delete the reservation. First we look up the current value of the time slot of the reservation to be deleted and subtract 1 and store the result in a variable called DecrementedTimeSlotCounter. This is easy to do since we have already looked up the value of the ID of the time slot that we need to update in the Set variables step.


We can then update the counter value, again using the ID of the time slot that we previously set in the variable TimeSlotID.


Finally we can delete the reservation by using the ID of the reservation that we previously stored in the variable ReservationID.


I know this has been a long post, but hopefully it helps some folks out there become more familiar with SPD workflows. This solution is far from perfect and there are several other ideas I would probably incorporate, but it provides a good demonstration of creating a scheduling application with workflows. I hope you’ve enjoyed it and learned at least one thing (always my goal).

I’m on my way to bed so I can get on a plane tomorrow to do some training in San Antonio, Texas…a short flight from Dallas. Y’all take care!

Give the gift of life…donate often!


Due to heavy load, the latest workflow operation has been queued. It will attempt to resume at a later time.

I’ve been doing some deep diving into globally reusable workflow creation with SPD 2010 and am beginning to hit a number of errors and "notes."

In this case, I have been attempting to copy and modify the OOTB Publishing Approval workflow.  I’ve added a pair of parallel approval tasks prior to the standard approval task and have modified the start-up form to display my country approval groups in several country drop-downs. 


Whenever I start the workflow I tend to get this "note." 

Due to heavy load, the latest workflow operation has been queued. It will attempt to resume at a later time.


I can only assume this means it has been serialized in the database and is now subject to the timer jobs.  My biggest problem at the moment is that I’ve been unable to approve either of the tasks that were assigned in parallel.  If I use the task email that was sent, I get this nice error.

An error occurred while retrieving the workflow task details.  This may be caused by:

  • Not having connectivity to the server
  • The task no longer exists
  • You do not have permission to access the task.

I don’t think contacting my system administrator is going to make this any better, so I’ll go directly to the task list.


UPDATE:  Found that the SharePoint site needs to be in the Local intranet sites in order for this to work.



Oops!  That’s an error.  Not really sure what to do about it and I don’t have the time to mess with it at the moment.



Deleting a Globally Reusable Workflow that was Created by Copy and Paste

Here’s a good thing NOT to do.

  • Open SPD 2010 at the site collection level and go into the workflow assets.
  • Make a copy of the Publishing Approval workflow using copy/paste.
  • Decide that you really didn’t want to do it that way and delete your copy of the workflow.
  • Amazingly, all the Publishing Approval workflows are deleted.
  • Close SPD, thinking that this must be a user interface glitch.
  • Open SPD and verify that the workflows have indeed been deleted.
  • Start working on a way to get the Publishing Approval workflow back.
  • If you don’t have a lot of customized WFs yet, deactivate and reactivate the Publishing Approval workflow.
  • Use "Copy and Modify" in the future.







Getting failed emails to send in SharePoint 2007 custom workflow

We experienced a very frustrating issue with sending emails in SharePoint 2007 custom workflows.  The error we received was

The e-mail message cannot be sent.  Make sure the outgoing e-mail settings for the server are configured correctly.

First, a little background…

We had assigned a number of tasks upon employee  termination, such as “Collect badge”, “Revoke UNIX access”, etc.  There were about a dozen of these tasks assigned when the termination was entered.  When each task was assigned, a separate workflow was initiated to send the email to the person responsible for completing the task.  For example, someone in the Facilities group was responsible for collecting the employee’s badge.  The majority of the time, the email was sent and the logging indicated success.


… but sometimes, we received the error and could not determine why.  The issue was sporadic.  Sometimes all 12 emails would be sent successfully.  Other times, maybe 2 or 3 emails would fail – never consistently the same workflow processes.   


Is it possible that with this many emails we had a conflict issue of some sort?  Maybe… but 12 concurrent emails doesn’t seem to be an unreasonable number!

A quick look at the SharePoint logs during the time of the issue did not point us to an obvious problem with the workflow logic or the email settings. 

We stumbled upon a work-around quite by accident…

One of our workflows worked a little differently.  We had it set up to pause for some time after the initial email attempt.  It was in this process where we found that if the email was not sent properly, then within 30 minutes or so after the pause, the SharePoint timer job kicked in and took action. The timer job determined there had been an issue and restarted the workflow from the top, rerunning the logic to send the email.  With the delay built in, that workflow never had an issue sending the email.

This prompted us to add a 2 hour delay at the end of all of our email workflows to ensure we had plenty time to get the email sent appropriately.


The 2 hours is probably overkill, as it seems to consistently require only 30 minutes or so to eventually send the email (the timer job gets kicked off in our shop every 30 minutes).  Since there is no additional processing after the pause, it doesn’t hurt to include it in the logic.

A simple delay fixed our issue!  Since we have added the delay, all emails have been sent.



Setting the URL and Hyperlink Description Simultaneously on a URL Field in a SharePoint Designer Workflow

If you need to update a URL field in a links list (or any other list) from SharePoint Designer, you need to know this little trick.  Create a string that contains the URL and description like this:


URL, Description


and then update the field with this string.  Notice there is a comma AND A SPACE after the URL.  There are no brackets, curly braces or octothorpes (# signs) involved in the string.


Using the string builder, you can see the "input" and the "output" of the workflow.





Asif Rehmani’s SharePoint Videos


Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home


Posts by Date

June 2023
Support Wikipedia