Archive for the 'Uncategorized' Category

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

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.

06
Mar
13

Reordering and Hiding Fields and Passing URL Parameters on a New Item Form in SharePoint 2010

Let’s say you are creating a new item form using SPD 2010 and you don’t have InfoPath because the company didn’t purchase the Enterprise version of SharePoint.  Your goal is to pass a URL parameter to the new item form from some other part of the SharePoint application you are building.  Perhaps the link that contains the URL parameter was created by a workflow (hint hint).

image

You insert the custom list form like this.

image

The goal is to move this field down to the end of the form to get it out of the way.

image

You select the row and cut it for repositioning to the end of the form.

image

Now you’ve got it at the bottom of your form.

image

Now you test your form (before adding the parameter to pass) and it "works" but it doesn’t correctly save the data in the field you relocated.

The correct way to do this is to delete the field row at the top, add a new row at the bottom and insert a new text field and bind it to the column.

image

Bind the Data field to the column and format it as a Text Box.

image

Under Options, select Parameters and add a New Parameter, giving it a name that is memorable for you.  Then, bind it to the Query String variable of your choice.

image

With your text box selected, find the text property and change it from @Fieldname to $Parametername.

image

If you don’t get the name correct, you’ll see an error displayed instead of your form.

image

If you want to hide the field completely on the form to eliminate any human intervention, add class=ms-hidden to the table row.

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

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