Archive for the 'SharePoint Workflow' Category

30
May
19

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.

image

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.

image

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.  🙂

image

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.”

image

For the Value I use this expression:

body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Value’]

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:

body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’]?[0]?[‘Ethel’]

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.

image

This is what the expression looks like:

first(body(‘Get_items_Filter_by_AdminFinalApprovalEmail’)?[‘Value’])?[‘Value’]

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.

image

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.

image

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’].

image

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.

image

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:

items(‘Apply_to_each’)?[‘Value’]

image

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.

image

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

image

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

image

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

image

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

image

image

Advertisements
11
Feb
15

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.

image

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

image

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

image

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

image

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

image

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.

image

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

image

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.

image

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

image

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

image

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

image

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

image

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

image

Insert an Update List Item action.

image

Here you can set each field to your variable values.

image

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

image

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

image

20
Jun
12

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.

20
Sep
11

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

image

and here are the results.

image

08
Sep
11

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.

image

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!

03
Aug
11

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.

clip_image002

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!

clip_image004

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

clip_image006

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.

clip_image008

clip_image010

clip_image012

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

clip_image014

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.

clip_image016

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}.

clip_image018

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.

http://portal.awbikes.local/SiteDirectory/bd/_layouts/ViewEdit.aspx?List=%7BBAA912CA%2DD4D4%2D4786%2DB2B8%2DB33DA8691CA4%7D&View=%7B35F16EC8%2D64E9%2D4BCE%2D8C8A%2DCD3D142AD894%7D&Source=http%253A%252F%252Fportal%252Eawbikes%252Elocal%252FSiteDirectory%252Fbd%252FLists%252FTime%2520Slots%252FAvailableTimeSlots%252Easpx

clip_image020

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.

clip_image022

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

clip_image024

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.

clip_image026

clip_image028

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.

clip_image030

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.

clip_image032

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.

clip_image034

clip_image036

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.

clip_image038

clip_image040

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

clip_image042

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).

clip_image044

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.

clip_image046

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.

clip_image048

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.

clip_image050

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.

clip_image052

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.

clip_image054

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

clip_image056

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.

clip_image058

clip_image060

clip_image062

clip_image064

clip_image066

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.

clip_image068

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.

clip_image070

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.

clip_image072

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.

clip_image074

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.

clip_image076

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

clip_image078

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

clip_image080

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!

17
Oct
10

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. 

image

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.

image

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.

image

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

image

 

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.

image




Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

October 2019
M T W T F S S
« Jun    
 123456
78910111213
14151617181920
21222324252627
28293031  
Support Wikipedia
Advertisements