09
Jun
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.

There are four examples and I think I like example #4 the best.

Example #1

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.

Example #2

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. 

Example #3

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.  Since this list is pretty small (< 10 items) it’s no big deal retrieving all the items.

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

Example #4

Now here’s another method that may be the best one of all.  This method uses a single Get Items action and subsequently filters the SharePoint results with an Array Filter action.

image

Next is the action to filter the array. Value is the list of items from Get items.  It should be pretty obvious we are filtering by the Title column to get the row where Title = ‘AdminEmailCC’.

image

Now the magic occurs.  We will initialize a new variable using the expression:

json(string(first(body(‘Filter_AdminEmailCC’))))?[‘Value’]

Like the other solutions, we get the first (and only) item from the array.  Then we convert it to a string so the json() function will work.  Using the json() function we can select the Value field.  And that’s it!

image

We get the value for our admin email.

image

The nice thing about this solution is it hits the SharePoint list only once.  After all the results are retrieved from SharePoint, it’s simply a task of filtering the array to get the specific row and then initialize a variable with the specific field from the single valued array.

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

image

image

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

29
Jun
16

SharePoint 2013 FBA Pack and CAPTCHA on Multiple Web Front Ends

Now this is an interesting problem!  We are using the SharePoint 2013 FBA pack on a project that has multiple WFEs.  Within the registration process is the ability to use a CAPTCHA image for human validation.  The registration page looks something like this.

image

In our QA system (where internal customers do lots of acceptance testing) we were having an issue where the CAPTCHA picture would not render all the time.  Many times it would render as a blank HTML picture box.  I ended up testing each WFE server in QA and found they both exhibited the same problem.  Sometimes they would be 90% good…other times they’d fail 50% of the time.  The most interesting thing about this is our PROD system did not exhibit the same issue, even though it is built the same as QA (yeah, right…we know what that means). 

If you look at the link on the picture you’ll see it is generated by a call to the ImageHipChallenge.ashx page and is implemented as shown here

/_layouts/15/FBA/ImageHipChallenge.ashx?w=210&h=70&id=16fe187603b34636addca12611d087c5

From what I can tell this is code that’s been around a while, probably since ASP.NET 1.0 or so.

So, what’s different between PROD and QA?  After stumbling around a bit, I found the Request Management service was turned off in PROD!  If you look under "Services on Server" you’ll find the Request Management service.  Here it is stopped in the SharePoint PROD instance…

image 

…and you can see here it was started on the QA SharePoint instance.

image

Well, that was interesting, so we turned it off in QA and found something even more interesting.  Our WFE1 box was working 100% (serving up the CAPTCHA images all the time), while the WFE2 box was working 50%.  We knocked heads on this for a while and one of our brilliant guys suggested we try configuring the Request Management Service using PowerShell.  Since we are using two WFEs in a "not very heavily loaded environment" we decided that Request Management probably wasn’t buying us a lot anyway.  So, using Set-SPRequestManagementSettings we disabled Routing and Throttling.

Add-PSSnapin microsoft.sharepoint.powershell
$web= Get-SPWebApplication -identity https://site.myco.com
Get-SPRequestManagementSettings -Identity $web
Set-SPRequestManagementSettings -Identity $web -RoutingEnabled $false -ThrottlingEnabled $false

Retrieving the settings with Get-SPRequestManagementSettings provides the following confirmation.

image

After doing this, our CAPTCHA images are now being served up correctly 100% of the time!  We’re thinking since the Request Management Service was never enabled in production that it is really "not running" while in QA, even when we stopped it in SCA, something about it was still lingering.  Note we did not reboot the QA WFEs during any of this configuration change.

We did have the thought about configuring the Request Management Service to not attempt to route the ImageHipChallenge.ashx requests…and we may still try that.

29
Jun
16

The server was unable to save the form at this time. Please try again.

Oh, the annoying errors from SharePoint!  At least this one didn’t say "please contact your help desk."

This error was experienced on a SharePoint site used for tracking project management tasks. 

image

There is a task list with a number of Lookup columns that was working perfectly fine for months.  Then, out of the blue, folks started experiencing this error, "The server was unable to save the form at this time.  Please try again."

A search of the web indicates that we are not alone experiencing this problem.  Some of the solutions point to a low memory issue, but our DEV server has 32GB of memory assigned to it, with only about 6-8GB being used, so it’s doubtful that’s the issue!

Some of the potential solutions:

  1. The Security Token Service needs to be recycled or has some other issue.
  2. An IISRESET fixes the problem.
  3. Enabling content types and adding "link to a document" content type fixes the issue for document libraries.

Until I got to the last potential solution I was having no luck.  Since this was not a document library, I couldn’t add the "link to a document" content type, but I certainly could try adding other content types.

I enabled content types on the list under Advanced Settings.

image

In the Content Types section, I went to add from existing content types.

image

From here I simply added the Item content type…one of the simplest system content types.

image

Voilà!  The edit can now be made to the item and saved successfully!

Now, I didn’t try every item in our list, but the one I was testing on had a file attachment.  Not sure if that has anything to do with the source of the issue, but I did capture a few SharePoint logs along the way.  Here are a couple of things I saw, but couldn’t find anything on the web that helped me much.  These messages seem to point to the RESX files in the C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\Resources folder and, as I looked inside core.resx and core.en-US.resx, I could not find any string "restricted" within the resource files.

Failed to look up string with key "Restricted", keyfile core.
Localized resource for token ‘Restricted’ could not be found for file with path: "(unavailable)".
Failed to cache field with id "{d098358c-5467-45ee-ad35-e91044fcd181}", overwrite=0

This was seen right after the EditForm.aspx link to the item on which I was experimenting.

pm/Lists/Project%20Tracking%20Tasks/EditForm.aspx?ID=171

There was also a "UserAgent not available, file operations may not be optimized." following it, but I’ve learned to ignore these.

22
Jun
16

Referencing a Custom Help Page with the Help.aspx page in SharePoint 2013

I was trying to find an easy way to navigate directly to a custom SharePoint help page and figured there must be some URL parameters I could use to get there.  However, all my searching only turned up snippets of JavaScript such as this one…which is okay, but I really wanted to understand what /_layouts/15/help.aspx page was really wanting.

var navBarHelpOverrideKey = “[Help Collection Product]_[Context Key]”;

If you look at the link provided by clicking on the help icon, you may see some links like these, once you navigate to the exact topic you want to view.

http://site.myco.com/_layouts/15/help.aspx?Lcid=1033&Key=HelpHome&ShowNav=true

Notice here you see AssetKey=.  This appears to expect some semi-colon delimited GUID on the parameter value.  I’ve found that using Key= is what you probably want to use.

http://site.myco.com/_layouts/15/help.aspx?AssetKey=/Lists/Site Collection Help/CustomHelp/LogonScreen/CustomTopic.htm;41fdd883-01d2-4c2a-9a25-4465d705c17a;

What I wanted to do was provide the exact topic in some sort of URL parameter.  What I found is you can use this format.  It is essentially the name of your folders you created in your custom help and the help page’s context key separated by underscores, e.g. Key=MyCustomHelpCollection_MyCustomHelpCategory_1.  The missing piece here is the Context Key added on the end.  This takes you directly to the page, without having to navigate through the Collection and Category.  Note that the URL parameter name you might be tempted to use is AssetKey=, while the correct URL parameter name to use for this type of navigation is simply Key=.

image

http://site.myco.com/_layouts/15/help.aspx?Key=%5BHelp Collection Folder Name]_[Help Category Folder Name]_[Help Page Context Key]

What does this mean?  This means that after you create a custom help library by enabling the feature and creating some content, as discussed in these articles, you can link directly to your topics if you need to.

Create a Custom Help Library to SharePoint 2013

Create a Custom Help Library for SharePoint Foundation 2010

It’s really possible this information exists somewhere out there, but I sure couldn’t find it after a couple hours of searching.

13
Apr
16

Remove Old Winshuttle License Activations

Trying to activate a new CENTRAL license returns the error

You cannot have Enterprise- and Workgroup-type licensing together.

image

The problem is likely due to having expired licenses.  To view these, change the view from By Products to By Activation on the Manage Licenses page.

image

Next, select View All Activations.

image

You should now be able to delete the old expired license activation.

image

Now on the Activate Licenses page you should be able to browse to your XML activation file and activate successfully.

image

 

Keywords:

Winshuttle license management

13
Apr
16

unable to cast object of type microsoft.sharepoint.webcontrols.splinkbutton

Been a long time since the last post…at least it feels like it.

Just finished troubleshooting an issue where a default page wouldn’t load.  The correlation ID pointed to an Unexpected error in the SharePoint ULS logs stating:

unable to cast object of type microsoft.sharepoint.webcontrols.splinkbutton

The site settings page would load (modifying the URL manually) as would View All Site Content.  However, clicking on any of the lists or libraries would throw the error.  We initially thought this was an issue with the default.aspx page getting corrupted, as we could create a new blank web part page in SPD, place it in the root of the web, and it would load up okay. 

What fixed this:  IISRESET!

Again, the same lesson learned, all over again.

SharePoint troubleshooting steps:

  1. IISRESET
  2. Everything else



Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

December 2019
M T W T F S S
« Jun    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Support Wikipedia