Tuesday, April 24, 2012

SharePoint 2010 Search Wrong Sort Order by Title

We newly realized that the SharePoint Search has a strage effect when you sort the results by title. We have a custom java application, which uses the SharePoint search web service to query  and display search result from a non SharePoint Site.

When we order the search by title with the expression "Order by" either in keyword or sql syntax  we have results like that:

  • a
  • aa
  • aaa
  • b
  • ba
  • bc
  • bd
  • tt
  • z
  • ac  ' Search starts new order
  • ad
  • cd
  • fg

The first 50 results are ordered, than the next 50 and so on. After digging a while and contacting the MS support we found out that this is a SharePoint bug with multiple indexes.

If you have multiple indexes in your search application you'll get this result.


The MS Support said that the bugfix we'll be delivered with the CU April 2012. Another result is to create a second search service application with one index. Than you can assign this service to a custom webapplication which you can create specially for search and use the webservice with the new webapplication.

Get Username in Nintex Workflow 2010

If you select a user list field to be displayed in Nintex E-Mails you usually get the user login, like "domain/cmasters". If you want to display the full name of a user, you can use the "Search User Profile" Action in the "Integration" tab.
Search User Profile

When you configure the action, you can select the user list field from the item to be saved in a text variable. You can also select which profile property to read and to be saved in the variable.

Configure Action

Now you can use the variable in all other actions.

Monday, April 23, 2012

Display Name in SharePoint is out of synch

In some SharePoint installations there is often a problem with display names of users. As an example: The display name of a user in an organization consists of his firstname surname and orginizational unit like:
  • Betty Looser (RD/F)
If Betty gets married and changes her unit, SharePoint should show her new name and unit
  • Betty Winner (FR/I)
but it displays still the old one. More worse in some sitecollections the name is displayed correctly whereas in others not.

Why is this happening ?

This happens because SharePoint stores all user, which were ever logged in a site collection in a special database table "UserInfo Table". This information is stored for every site collection.
The contents of this table are displayed by a hidden SharePoint list called "User Information List". If you are a SharePoint admin, you can display the list information by typing the following url:
  • http://{sitecollection url}/_catalogs/users/simple.apx
When you first visit a SharePoint site collection, your user info is stored in the database table.

But there is a property in the user info table named "tp_IsActive". This property is set when a user in a site collection gets active. "Active" means (from msdn)

"A user is considered "active" if he or she created or modified any data in the site collection."

This property is used by the SharePoint timer jobs, to synch the user information with the profile store. A user information with tp_IsActive = 0 is therefore never updated within a site collection.

That means all user, that have only read rights or have never changed content in a site collection will have the problem with not updated user display names. This problem is also often caused when you grant "All autheticated users" read rights to a site.


The solution for this problem is to create a PowerShell script and a Windows Task Schedule.

The PowerShell Script

Create a file and name it "UpdateDisplayName.ps1". The script loops throught all user within all sitecollections and checks if their user display name is different that in profile store. If so the user display name is updated. You can change this script easily to change also other properties.

# Author: I.B.Bikmaz

# Loading Microsoft.SharePoint.PowerShell
$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) {  
  Add-PSSnapin "Microsoft.SharePoint.Powershell"

# Loading Needed Assemblies
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | out-null
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server") | out-null
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles") | out-null

<# --------------------------------
  These values can be changed
-------------------------------- #>
# -- Format of the date for the log file
# --
$date = Get-Date -uformat "%Y_%m_%d_%H_%M"

# -- Name of the log file
# -- Create Folder if not exists
$log = "E:\Appl\SP2010\Scripts\UserDisplayNameUpdate_$date.log" 

# -- Url of the web application
# --
$webapp = Get-SPWebApplication "http://sharepoint/"

# -----------END CHANGE-----------
# Logging!! 
Start-Transcript -path $log 

# Write Starting Date
$processDate = Get-Date
Write-Host "Starting Profile Info Update:" $processDate

# Create the stopwatch
[System.Diagnostics.Stopwatch] $sw;
$sw = New-Object System.Diagnostics.StopWatch

<# --------------------------------
  GET ProfilService
-------------------------------- #>
$upm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager( [Microsoft.Office.Server.ServerContext]::Default )
if ($upm -eq $null){
  Write-Host "Could not find User Profile Manager!"

<# --------------------------------
  Looping through all sites 
  to check if stored user info 
  has changed.
-------------------------------- #>
try {  
  foreach($site in $webapp.Sites) {
    $web = $site.RootWeb
    $siteCollUsers = $web.SiteUsers
    Write-Host "> SiteCollection: " $site.Url
    foreach( $user in $siteCollUsers ) {  
      $login = $user.LoginName                    
      $dispname = $user.Name
      if ($upm.UserExists($login)){
        $profile = $upm.GetUserProfile($login);
        $profilename = $profile["PreferredName"].ToString();
        if ($dispname -ne $profilename){
          Write-Host "  >> Changing '" $dispname "' >> '" $profilename "'"
          $user.Name = $profilename
catch [System.Exception] {
  Write-Host "Error while updating user info tables."
Write-Host "Time Elapsed: " $sw.Elapsed.ToString()
Write-Host "User Display Names successfully updated !"

The Windows Task Scheduler

Check the script manually before creating a task schedule. Run the script as SharePoint Farm Account or the account which has admin rights on the user profile service. If you use another user the script will fail.

Open the Task Scheduler in Windows 2008 and create a new task. I think that it will be enough to trigger the task once a week (depens on how many user your company has). Run the task as SharePoint Farm Admin or the account which has rights to use the user profile service.

In the Actions tab choose "Start a program" as Action. Program/script is "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" 
and argument is:
-command "C:\Scripts\UpdateDisplayName.ps1"

As the script writes a log file you can check if the script runned successfully. The place for the log file can be changed in the script.

Tuesday, April 3, 2012

SharePoint List Synchronization with Excel

A question which is often asked is how you can synchronize a SharePoint list with Excel. The answer differs on what version of Excel you use.

Excel 2003 with SharePoint 2007/2010

With Excel 2003 the synchronization works in both directions. This means that you can create a SharePoint list connect to Excel, make changes in Excel and write back to Excel. The single restriction is that you can't create new items in Excel but modify existing ones.

Excel 2003 has a bug in which an alert pops up and says that the field is read-only, when you try to modify a field. There is a patch for this bug found here: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=952608

To synch the lists go to the list you want to synch and click on "Export to Excel" button in the ribbon.

The list will be opened in Excel and automatically connected to the SharePoint list. Now you can make changes within the Excel sheet. To synch back click on "Data" >> "Lists" >> "Synchronize Lists". Ensure yourself that a cell within the table is clicked. Otherwise the "Synch Lists" menu item is disabled.

Excel 2007 with SharePoint 2007/2010

With Office 2007 Microsoft has choosen Access to be the tool for synchronzation with SharePoint. You can still create a connection from Excel to a SharePoint list, but the synchronization is one-way from SharePoint to Excel.

If you still want a two-way synchronization you can download a free Add-In from Microsoft. With this tool you have to create a list in Excel, publish it to a SharePoint list and synchronize with it. But it not addresses the issue to synchronize with an existing SharePoint list

There is also a Add-In from third party organization: http://www.softfluent.com/products/sharepoint-list-synchronizer. With this tool you can also synchronize with existing SharePoint lists.

Excel 2010 with SharePoint 2007/2010

Excel 2010 offers like Excel 2007 a one-way synchronization. You have the use Add-Ins to synchronize two-way with SharePoint. The better way is to use Access 2010 which can be synchronized with a SharePoint-List and then exported to a Excel list or the way back.

It seems that the MS Add-In works also fine with SharePoint 2010.  (See:  http://sp.meetdux.com/archive/2010/03/30/Sync-Excel-SharePoint-2010.aspx )