SharePoint: Set a Calculated Hyperlink Column from a Workflow

The following applies to SP 2010 and SP 2013 and should work in SP 2007.

You want a calculated column for a hyperlink, but can't create one. (you can't embed working HTML into the calculation.) So… you consider a workflow to create the data for a hyperlink column.

Some things in SharePoint are obvious, while others are not. Setting a calculated hyperlink field to a new URL from a workflow is one of the not so obvious. When you use the "Update List Item" action, click "this list", click Add and the "…" you get an Edit Hyperlink dialog box. While you can type manual text and URL entries, you can't build an expression. Your second choice is to click fx set a value from a variable. The problem is that this is not a simple string. The string in the variable needs to match this pattern, which includes a comma and one space:
    http://yoururl, your display text

 

Assumptions for the walkthrough:

• You have a list with a course ID column named ID.
• You have a course title column named Title.
• You have an empty Hyperlink column named TheLink.

 

Steps:

1. Open your site in SharePoint Designer 2010 or 2013 as appropriate.
2. Click Lists and Libraries and click your list.
3. Click the List Workflow button in the ribbon.
4. Name your workflow and in SPD 2013 set the Platform Type to SharePoint 2010 or 2013 as desired..
5. Click OK.
6. Click the Action dropdown and click Set Workflow Variable.
   
7. Click workflow variable and create a new variable with a name like "LinkText", set the Type to String and click OK.
   
8. Click value and click the "…" button.
9. Create a string that will build your URL and your description, separated by a comma and a space. Click Add or Change Lookup to insert data from other columns. Then click OK.
   
10. Click the Action dropdown and click Update List Item. (for 2013: Set Field in Current Item)
    
11. Click this item, click Add, and select your hyperlink column. (for 2013: Click field and pick your hyperlink column and then click value and pick your variable.)
    
12. Click the fx button, select Workflow Variables and Parameters and then select your variable and click OK.
    
13. Click OK.
14. Click Publish (in the ribbon).
15. Test the workflow.
    1. Return to your list.
    2. Checkmark a list item.
    3. Click the ITEMS tab and click Workflows.
    4. Click the name of your workflow and click Start.
    5. Click the URL column and confirm that the link works.
16. You will probably want to return to Designer and change the Workflow Settings to start the workflow when an item is created and when an item is changed.

 

 

.

SharePoint: This service isn't available right now. User has Empty Lookup Column

 

The following screen captures are from SP 2013. SP 2010 behaves in the same way except for the error message.

 

Scenario:

• You grant Contribute or Edit permissions to a user to a list or library, but not to the site.
• When they visit the list/library, one column is blank that should have data.
• When they edit an item the field is blank and the dropdown is empty.
• When they click LIBRARY, Quick Edit, and then click the dropdown for the column the get the following error: (Clicking the link does nothing.)
     
     Note: SP 2010 just displays an empty dropdown in the Datasheet View with no message about a "service" or other error.
  
  when they should be getting this:
     

The issue? It's a look up column, and the user does not have at least Read permissions to the lookup list. (Convenient that I named it "Lookup" huh?)

Other interesting side effects of not having permissions to the lookup list:

• If the lookup column is Required, then the user cannot edit anything in the list/library.
• If the lookup column is not Required, then when the user edits the item, the data in the lookup column is deleted!

 

.

SharePoint / PowerShell List Item Update Note

 

Sometimes it's the forests and sometimes it's the trees…

 

I have gotten so used to using shortcuts like chaining of properties in PowerShell that I forget some of my .Net fundamentals.

The Title never gets updated in this little script. What's wrong?

$web = Get-SPWeb "http://maxsp2013wfe/sites/training"
$list = $web.Lists["Announcements"]
$list.items[3]["Title"]="Test Title"
$list.items[3].Update()

Line 3 creates a new SPListItem object in RAM and sets its Title property.
Line 4 creates a new (but different) SPListItem object and calls .Update() on it.

Duh!

 

This works:

$web = Get-SPWeb "http://maxsp2013wfe/sites/training"
$list = $web.Lists["Announcements"]
$item = $list.items[3]
$item["Title"]="Test Title"
$item.Update()

Create the SPListItem object once, change a property and then save it.

And I teach this stuff! 

.

SharePoint: Two Secrets for Typing Two Letter Names

 

SharePoint 2013 took away the people picker search tool and replaced it with an autocomplete feature. Type a few letters and matching names appear.

The problem is that this only works when you have typed the third letter of the name. With only two letters SharePoint just sits there patiently and does nothing useful.

 

But there is a way…

Two secrets for two letter names...

• Type the two letters and a space. Name will pop up.
• Type the two letters and a semicolon. Name will be auto completed.

 

The semicolon trick also works when you have typed enough to uniquely identify a single user or group. In my farm I only have one "Robert". Typing "rob;" automatically finds him. Typing "sale;" finds and adds the "Sales" AD group.

 

 

 

.

SharePoint PreCancelAction

 

SharePoint does not have a PreCancelAction. I created what I needed for one project, and present it here for your creative uses and enhancements. No warranties and no support… but the price is right!

 

PreSaveAction

When you click Save from an ASPX list form SharePoint checks to see if you have added a PreSaveAction JavaScript function to the page. If you have included the function, it is called, your code run and then your code can return a True or False to allow the save to continue, or to cancel it. You can add this function directly to the page, via a Content Editor Web Part, and in 2013 as a JS Link. Do a web search to find examples of its use.

PreSaveAction sample:

<script type="text/javascript">
 function PreSaveAction() {
  // do pre-save work here: validation, messages, etc.
  alert('Thank you for your suggestion!');
  return true;  // return true to continue with the save 
                // or return false to cancel the save
 } 
</script>

 

PreCancelAction

I needed a popup message to stress "Changes not saved", so I put together some JavaScript that intercepts the cancel and displays a message to the user. 

All of the examples below look for INPUT tags with a VALUE of "Cancel". You may want to change this line to match your language requirements. The code also only intercepts INPUT tags that include "STSNavigate" in their onclick code. This is to avoid intercepting the attachment dialog's cancel button. The code stores all of the original onclick code in a globally scoped array named TTNoriginalFunctions as each Cancel button on the form may have had unique code.

 

Warn the user on cancel:

This example does not call your functions, it just intercepts the Cancel buttons and runs the imbedded alert code.

var TTNoriginalFunctions = [];
var TTNCounts = 0;
var TTNinputs  = document.getElementsByTagName("input")
for (var i = 0; i<TTNinputs.length; i++)
{
  if (TTNinputs[i].value == "Cancel") 
  {
    if (TTNinputs[i].onclick)
    { 
      if (String(TTNinputs[i].onclick).indexOf("STSNavigate")>-1)
      {
        TTNoriginalFunctions[TTNCounts] = TTNinputs[i].onclick;
        TTNinputs[i].onclick = new Function(" return function () { alert('Changes not saved'); TTNoriginalFunctions[" + TTNCounts + "]();}")();
        TTNCounts++;
      } 
    }
  }
}

 

Callable as a function:

This is the same as the above, but wrapped up in a function that you can call it from your code. You pass in the custom code to run as a string. This uses "new Function" to build the code from a string.

var TTNoriginalFunctions = [];

function TTNPreCancelAction(yourFunctionAsString)
{
  var TTNCount = 0;
  var TTNinputs  = document.getElementsByTagName("input")
  for (var i = 0; i<TTNinputs.length; i++)
  {
    if (TTNinputs[i].value == "Cancel") 
    {
      if (TTNinputs[i].onclick)
      { 
        if (String(TTNinputs[i].onclick).indexOf("STSNavigate")>-1)
        {
          TTNoriginalFunctions[TTNCount] = TTNinputs[i].onclick;
          TTNinputs[i].onclick = new Function(" return function () { " + yourFunctionAsString + "; TTNoriginalFunctions[" + TTNCount + "]();}")();
          TTNCount++;
        } 
      }
    }
  }
}


TTNPreCancelAction("alert('changes not saved')");

 

A solution that works more like SharePoint's PreSaveAction:

You could add the TTNPreCancelAction function listed below to your master page or an existing linked JavaScript library. You can then add a PreCancelAction function to forms as needed using SharePoint Designer edits, Content Editor Web Parts or JS Link, just like the PreSaveAction functions. Your PreCancelAction function must return "true" or "false".

// Add this function to a form
function PreCancelAction()
{
  alert('Changes not saved!'); return true;
  //return confirm('Are your sure? Data will be lost!')
}


// Add this code to each form, or once in the master page.
var TTNoriginalFunctions = [];
var TTNCount = 0;
function TTNPreCancelAction()
{
    var TTNinputs  = document.getElementsByTagName("input")
    for (var i = 0; i<TTNinputs.length; i++)
    {
      if (TTNinputs[i].value == "Cancel") 
      {
        if (TTNinputs[i].onclick)
        { 
          if (String(TTNinputs[i].onclick).indexOf("STSNavigate")>-1)  // ignore the Attachments Cancel!
          {
            TTNoriginalFunctions[TTNCount] = TTNinputs[i].onclick;
            TTNinputs[i].onclick = new Function(" return function () { if ('function'==typeof(PreCancelAction)) {if (!PreCancelAction()) {return false} }; TTNoriginalFunctions[" + TTNCount + "]();}")();
            TTNCount++;
          } 
        }
      }
    }
}

TTNPreCancelAction()  //intercept the Cancel buttons
 

 

 

Have a better solution? Post a comment below! 

.

SharePoint Online… Where's My Tree View?

 

As you are probably aware by now… SharePoint Online is a member of the "change of the day" club. My Tree View is missing in my online sites!

In my on-premises SharePoint 2013 I can go to Settings, Site Settings and there turn on/off the Tree View navigation control.

 

On-line? No Tree View for me…   Oh, there it is…. Navigation Elements.

 

What's a "Navigation Element" anyway? If I had to rename it, I would have called it "Quick View or Tree View".

 

This is great fun for people like me who write courseware for SharePoint.  

 

Another day… another change…………………………………

 

 

 

.

SharePoint: 64 bit IE and Office is not generally a good idea!

 

64 bit programs have to be better than 32 bit programs, right? This is a question that keeps popping up in my classes and the various support forums, usually in the form of "xxxxx does not work in SharePoint". In general all client applications used with SharePoint should be 32 bit. This includes Word, Excel, IE, etc.  The Office ActiveX controls used with SharePoint are all 32 bit.

2010

Here's the SharePoint 2010 Browser Support article from TechNet:

https://technet.microsoft.com/en-us/library/cc288142(v=office.14).aspx

For specific things not supported in 64 bit see:

https://technet.microsoft.com/en-us/library/cc288142(v=office.14).aspx#activex

 

2013

Things have gotten better with SharePoint2013 as there's less dependence on ActiveX controls. The Office 64 bit article (https://technet.microsoft.com/en-us/library/ee681792.aspx) says "We recommend the 32-bit version of Office for most users, because it's more compatible with most other applications, especially third-party add-ins."

For SharePoint 2013 the support browsers article says it a little differently, but has the same message: "Some functionality in SharePoint 2013 requires ActiveX controls. This produces limitations on browsers which do not support ActiveX. Currently only 32-bit versions of Internet Explorer support this functionality."

https://technet.microsoft.com/en-us/library/cc263526.aspx#activex

 

 

.

SharePoint Saturday Cincinnati - SharePoint 2013 Search Results Customization

 

SharePoint Saturday Cincinnati is almost here! In fact… it's tomorrow. I thought I'd do something a little different this time and put out a little teaser on my topic. To be fair though… there's 19 other cool topics planned for Saturday, just click here to see the schedule.  Register NOW!   http://bit.ly/1DAprtv

 

So a bit about my presentation…

SharePoint 2013 Search Results Customization

Let's start with a problem… Our users can't find a certain kind of document; for example, PowerPoint presentations about our R&D projects. And when they find them, they don't know which ones are highly confidential, and more than once they have shared them with the wrong people.

And then a solution…

• Create Site Columns for consistent metadata and to support Managed Property searches such as"ResearchGroup=aviation".
• Configure the search schema to support friendly property names and the ability to search using "<" and ">" on dates and numbers.
• Create a Content Type to ensure document classification and to collect important metadata.
• Create a Display Template to add useful information to the search results, and display our Governance policies for R&D documents. ("Warning… confidential content. Do not share!")
• Create a Result Type to map the Content Type to the Display Template.
• Create a user friendly and governance friendly search results page.

All in an hour! 

 

See you there!

 

.

SharePoint Column Validation Examples

Update 11/2/2015… added "Date must be the first day of the month" and "Date must be the last day of the month".
The following applies to SharePoint 2007, 2010 and 2013.

Column Validation

SharePoint does not include column types for phone numbers or part numbers, nor does it include support for Regular Expressions to test for character patterns. It does support Excel style functions that we can use to create useful column validation formulas.
Below you will find column validation examples for:

• OR
• AND
• Length (LEN)
• Pattern matching using SEARCH and FIND
• Date testing

General Validation Formula Rules:

• Formula must return True or False.
• Column validations can only be added to Single Line of Text, Number, Choice (Drop-Down menu or Radio buttons, but not Checkboxes), Currency and Date and Time columns.
• Expressions are generally Excel compatible, but not all Excel functions can be used.
• Field names without special symbols can be entered as is or in square brackets
        = Price * [Qty]  > 100
• Field namess with spaces or symbols must be enclosed in square brackets
        =OR( [Sales Region] = 1, [Sales Region] = 1)
• The text comparisons are not case sensitive.
        =OR( status = "a", status="c")     is true for either "A" or "a" or "C" or "c".
• In a column validation the formula cannot refer to another column.
• In a list / library validation the formula can refer to other columns in the same item.

Examples using "OR":

The OR function accepts two or more Boolean tests that each return True or False. OR returns True if any one of the tests is True.
=OR(YourFieldName="A",YourFieldName="C",YourFieldName="E")
=OR(State="OH", State="IN", State="KY", State="MI")
=OR(Qty=5, Qty=10, Qty=20)

Examples using "AND":

The AND function accepts two or more Boolean tests that each return True or False. AND returns True if all of the tests are True.
=AND(YourFieldName>"A", YourFieldName<"M")     YourFieldName value must be between A and M.
=AND(Qty>5, Qty<100, Qty<>47)      Qty must be between 5 and 100, but not 47.

Examples using "LEN":

As an example, if your part numbers are always 9 characters long:
    =LEN(YourFieldName) = 9
If the part numbers can be 9 or 12 characters long:
    =OR( LEN(YourFieldName) = 9, LEN(YourFieldName) = 12 )

Examples for Pattern Matching

The SEARCH function:  (online help)

• Matches a pattern using "*" and "?". "*" equals zero more characters and "?" equals exactly one character.
• To match an asterisks or question mark character prefix the symbols with "~". 
  Example: "a~?b?c" matches "a?bxc" but not "axbxc". 
• An "*" is assumed to be appended to the end of the match pattern. To limit the length use the AND and LEN functions.
• The comparison is not case sensitive.
• If there is a match, the function returns the position of the match. If the every character is to be matched you would typically test for "=1" or maybe ">0". 
• If there is no match, the function returns ERROR, therefore it must be wrapped inside of an ISERROR function. As we will have a match if there is no error, the ISERROR must be wrapped inside of a NOT function. (online help for ISERROR)

Examples:
Must start with an "a" or "A" and the third character must be a "c" or "C":
   =NOT(ISERROR( SEARCH("A?C",YourFieldName)=1 ))
   Matches: abc   AbC  aXc  a6c aBcDEF
   Does not match:   bbb   abb  ac  a
Match a phone number pattern of xxx-xxx-xxxx: (note: user could type letters or digits or type extra characters.)
   =NOT(ISERROR( SEARCH("???-???-????",YourFieldName)=1 ))
   Matches: 123-123-1234    aaa-aaa-aaaa   123-123-12344444
Match a phone number pattern of xxx-xxx-xxxx and limit the length:
   =AND( NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))), LEN(YourFieldName)=12 )
   Matches: 123-123-1234
   Does not match: 123-123-12345

Match a phone number and make sure only digits have been used:
The first example here is not a true pattern match. It just extracts the characters we think should be digits and tries to multiply them by any number. If that fails, then one or more of the characters is not a number. (online help for CONCATENATE and MID)
=NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4))))
   Matches: 123-123-1234    123x123x1234   123-123-1234xxxxx
   Does not match: abc-123-1234
The second example combines the earlier pattern match with a numeric test:
   =AND(NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))),LEN(YourFieldName)=12, NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4)))))

The FIND Function:  (online help)
The FIND function is similar to the SEARCH function with two differences;

• FIND is case sensitive.
• FIND does not support wild cards.

Examples Using Dates

You can create rules to limit date ranges by using the TODAY() function or the DATEVALUE() function.
Date must be in the future:
    =YourFieldName>TODAY()
Date must be in the future by "x" days:
    =YourFieldName>TODAY() + 3I.e. If today is the 7th, then valid dates start on the 11th.
Test against a particular date:  (online help for DATEVALUE)
    =YourFieldName>datevalue("1/1/2015")
Date must be between now and the end of the current year:  (online help for YEAR)
    =YourFieldName < DATEVALUE( "12/31/" & YEAR(TODAY()) )This example calculates a DATEVALUE by building a string to represent a future date.
Date must be within the next 30 days:
    =AND(YourFieldName >= TODAY(),YourFieldName <= TODAY()+30)
Date must be a Monday:   (1 = Sunday, 2 = Monday, 3 = Tuesday, …)   (online help for WEEKDAY)
    =WEEKDAY(YourFieldName)=2
Date must be the last day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn)+1,0)
Date must be the first day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),1)
Note: Some of the more "fun" Excel date functions like WEEKNUM, NETWORKDAYS and EOMONTH are not supported in SharePoint.

Not so useful tests!  

Value must be greater than PI.  (3.14159265358979 more or less…)
    =YourFieldName > PI()
And some square roots:
    =YourFieldName > SQRT(2)
And of course you need a little trig:
    =TAN(RADIANS(YourFieldName)) > 1

.

SharePoint: Who can't you hide things from?

 

You just created a new subsite. You broke inheritance. You removed all of the inherited permissions. You gave only three people access.

Can anyone else see this site?

Yup…

• Your Site Collection Administrator.
• People granted Web Application level "super user" permission policies by the server administrators. These roles are often called "Auditors" and "Super Administrators".
• Server administrators who have granted themselves "super user" permissions.
• Any administrator using the farm service account. (Never a best practice.)
• Any one who your team members have "Shared" with!  (see below)
• The SQL Database Administrator. (we never directly query the tables… right?)

 

Site Collection Administrators

When a new Site Collection is created the server administrator can assign people to two roles named Primary Owner and Secondary Owner. These two users can see and change everything in site collection, unless some permission has been denied by Web Application level user policies.

These two Site Collection Administrators can add as many other people to the list of Site Collection Administrators as they like. Only the Primary and Secondary will receive site alert emails, all of these admins have Full Control over everything in the Site Collection. For more interesting things about these extra admins, see: http://techtrainingnotes.blogspot.com/2012/12/fun-and-games-with-site-collection.html.

 

Web Application Level "Super User" Permission Policies

Server administrators can define Web Application level policies and broadly give or remove permissions. These policies overrule anything done at the Site Collection or subsite levels. Here's a few examples:

• Remove the "Create Subsites" permission from all users.
• Remove the "Manage Lists" permission from everyone in the Active Directory Sales Managers group.
• Make a user an "Auditor" with rights to see everything in the entire Web Application. Yes, everything, including permissions and everything in the Site Settings page.
• Make a user a "Super Administrator" with the ability to change anything in the Site Collection, and even run in "stealth mode" with all changes listed as "by System Account".

 

Team Member Sharing – Members are security admins???

In SharePoint 2013 Online, users given the "Edit" permission level can share the site or anything in any list or library in the site where they have that permission level. All they have to do is click one of the many "Share" buttons or links. This one should really scare you! All they have to do is click the Share at the top of the page, and they have shared the entire site without site owner approval. If they click Share on a document or list item, then they have broken inheritance on that item, and then shared it!  The same user in SharePoint on-premises is only creating an "access request". See how to hide the share buttons here: http://techtrainingnotes.blogspot.com/2015/08/hiding-evil-sharepoint-2013-share.html

A bit odd, while the user with the "Edit" permission can "share" a full site or a single list item, then cannot share a list or library. If they guess the URL to "Permissions for this document library" they get "access denied".

SharePoint Online/Office 365 vs. On Premises:

	On Premises	Online
Member clicks the site level Share button	Creates an "access request" – site owner needs to approve	Adds new user to the Members group with usually has the Edit permission level
Member clicks the list or library item Share button	Creates an "access request" – site owner needs to approve. If approved, breaks inheritance and adds permissions for the new user.	Breaks inheritance and adds permissions for the new user.
Member guesses the URL to the People and Groups page…	Can only see the list of users in the groups.	Can remove users from groups!

So… Consider editing the "Edit" permission level and removing the "Manage Lists" permission!

 

 

 

.