Get and Set SharePoint Yes No Field Using Flow

Simple question: Using a Flow condition, how do you check the value of a SharePoint Yes No field?

My first attempt was to set a variable equal to the SharePoint list value, then check the condition like this:



And, for good reason, this did not work.
My next attempt was to try replacing the condition value with true or false.



And, again, this did not work!
Sooo, what if I convert the true or false to a string?


It worked!



How do you set or update a SharePoint Yes No field using a Flow variable?


Create a string variable, then set the value to true or false.
SharePoint display values:
Yes = true
No = false


Update Password in Remote Desktop Manager RDCMAN

I can never manage to remember how to do this. Simple enough, how do you update your login credentials in Remote Desktop Manager ( RDCMAN )?

Right click on the root node and select Properties.

Select the Security Settings tab, then click on Source: default settings group.
A new window will open, in it, select the Profile Management tab.
Select the account you want to update, click the Edit button on right, then click Ok on the bottom.

All done.

Classic ASP Update Records in Access Error [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

I was trying to update an Access database from a classic ASP page and encountered this error:

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

The fix is outlined here:

http://support.yessoftware.com/kb_article.asp?article_id=72

In a modern OS, you need add the local IUSR account to the folder that holds your database.  The account will need read and write access.

Who knew that classic ASP was so much fun to work with?

Document ID Service and Migrating Documents

You are using the Document ID Service in SharePoint and you want to move your documents to another location.  For whatever reason, you notice when you move the documents that your document id’s are lost or reset.  This can be real bad if other systems outside of SharePoint leverage the document id to locate documents in SharePoint.

Here are the steps I used to get around this.
Move the documents from location A to B, keeping the original documents in A.
Run a crawl.
Execute the script below (input your own values for the site and libraries).
Remove / delete the documents from location A. For safe measures, also empty the recycle bin, both at the site and site collection level.
Run another crawl.
Trigger the Document Id Settings to update.

What the script is doing:
Get all the documents in the Shared Documents library of site A.
Loop through the documents.
Locate the document by name in the Shared Documents library of site B.
Update the Document ID of the item.

Things you could improve on:
Get all the documents in site A and output the Name and Document ID to a csv file.
– Then process the csv file with the script.
Add logging.
– Output your pass / fail items to csv file.
Expand the search to query a site.
– Then update the returned item.

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

$sSite = Get-SPWeb "http://sharepoint.net/sites/A"
$sList = $sSite.lists["Shared Documents"]

$dSite = Get-SPWeb "http://sharepoint.net/sites/B"
$dList = $dSite.lists["Shared Documents"]

$sQuery = New-Object Microsoft.SharePoint.SPQuery
$sQuery.ViewAttributes = "Scope='Recursive'"
$sQuery.RowLimit = 2000
$sQuery.Query = '<Where><Gt><FieldRef Name="ID" /><Value Type="Counter">0</Value></Gt></Where>'

do
{
	$sItems = $sList.GetItems($sQuery)
	$sQuery.ListItemCollectionPosition = $sItems.ListItemCollectionPosition
	foreach($sI in $sItems)
	{
		$docName = $sI["Name"].ToString()
		Write-Host $docName

		$dQuery = New-Object Microsoft.SharePoint.SPQuery
		$dQuery.ViewAttributes = "Scope='Recursive'"
		$dQuery.RowLimit = 2000
		$dQuery.Query = '<Where><Eq><FieldRef Name="FileLeafRef"/><Value Type="File">' + $docName + '</Value></Eq></Where>'

		do
		{
			$dItems = $dList.GetItems($dQuery)
			$dQuery.ListItemCollectionPosition = $dItems.ListItemCollectionPosition
			foreach($dI in $dItems)
			{
				try
				{
					$dI["Document ID Value"] = $sI["Document ID Value"]
					$dI.Update()
					Write-Host $dI["Name"] " has been updated"
				}
				catch
				{
					Write-Host $dI["Name"] " ---- " $_.Exception.Message
				}
			}
		}
		while ($dQuery.ListItemCollectionPosition -ne $null)
	}
}
while ($sQuery.ListItemCollectionPosition -ne $null)

This item cannot be updated because it is locked as read-only

Had to call in Microsoft Support to help unlock files that were marked as read-only. Some files appeared to be locked, but using the UI, they couldn’t be unlocked, declared as a record, or edited.

Error: This item cannot be updated because it is locked as read-only
Error: The file “your file string” is checked out for editing by SHAREPOINT\system

This script was setup to run on a SharePoint 2010 instance. If you need to run it on another version, try updating the $sharePointAssembly line.
To unlock a file, input the URL of the file in the line that starts with $fileUrl.


$recordFields =
  "_vti_ItemHoldRecordStatus",
  "_vti_ItemDeclaredRecord"

$recordProperties =
  "ecm_RecordRestrictions",
  "ecm_ItemLockHolders",
  "ecm_ItemDeleteBlockHolders"

$fileUrl = "http://siteURL/siteCollection/libraryName/folder/subFolder/fileName.pdf"

$sharePointAssembly = [System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")

Add-Type -TypeDefinition @"
using Microsoft.SharePoint;

public class EventsDisabler : SPEventReceiverBase
{
  public EventsDisabler() {}
  
  public bool EventsDisabled
  {
    get { return !EventFiringEnabled; }
    set { EventFiringEnabled = !value; }
  }
}
"@ -ReferencedAssemblies $sharePointAssembly

Write-Host "Getting site collection at $fileUrl..."
[Microsoft.SharePoint.SPSite]$site = New-Object Microsoft.SharePoint.SPSite($fileUrl)
if ($site -eq $null) { exit }
$siteUrl = $site.Url
Write-Host "Found site collection $siteUrl"
Write-Host ""

Write-Host "Getting web at $fileUrl..."
[Microsoft.SharePoint.SPWeb]$web = $site.OpenWeb()
if ($web -eq $null) { exit }
$webTitle = $web.Title
Write-Host "Found web $webTitle"
Write-Host ""

Write-Host "Verifying current user is System Account"
$web.CurrentUser.ID
$site.SystemAccount.ID
if ($web.CurrentUser.ID -ne $site.SystemAccount.ID)
{
  Write-Error "Please run this script as System Account" -Category PermissionDenied
  #exit
}
Write-Host ""

Write-Host "Getting list at $fileUrl..."
Write-Host $fileUrl
[Microsoft.SharePoint.SPList]$list = $web.GetList($fileUrl)
if ($list -eq $null) { exit }
$listTitle = $list.Title
Write-Host "Found list $listTitle"
Write-Host ""

Write-Host "Getting list item at $fileUrl..."
[Microsoft.SharePoint.SPListItem]$listItem = $web.GetListItem($fileUrl)
if ($listItem -eq $null) { exit }
$listItemName = $listItem.Name
Write-Host "Found list item $listItemName"
Write-Host ""

$eventsDisabler = New-Object EventsDisabler
$eventsOriginallyDisabled = $eventsDisabler.EventsDisabled
if ($eventsOriginallyDisabled -eq $false)
{
  Write-Host "Disabling events"
  $eventsDisabler.EventsDisabled = $true
  Write-Host ""
}

$didWork = $false
$itemNeedsUpdate = $false

#Discard any check-out
if ($listItem.File -ne $null -and $listItem.File.CheckOutType -ne [Microsoft.SharePoint.SPFile+SPCheckOutType]::None)
{
  Write-Host "Undoing check-out"
  $listItem.File.UndoCheckOut()
  $didWork = $true
}
else
{
  Write-Host "No file or file is not checked out"
  Write-Host ""
}

#Iterate the Record fields and set all values to null
foreach($recordField in $recordFields)
{
  if ($listItem.Fields.ContainsField($recordField) -eq $true -and $listItem[$recordField] -ne $null)
  {
    $recordFieldValue = $listItem[$recordField]
    Write-Host "$recordField = $recordFieldValue"
    Write-Host "Setting $recordField to null"
    $listItem[$recordField] = $null
    $didWork = $true
    $itemNeedsUpdate = $true
  }
}

#Iterate the Record properties and remove any that exist
foreach($recordProperty in $recordProperties)
{
  if ($listItem.Properties.ContainsKey($recordProperty) -eq $true)
  {
    $recordPropertyValue = $listItem.Properties[$recordProperty]
    Write-Host "$recordProperty = $recordPropertyValue"
    Write-Host "Removing property $recordProperty"
    $listItem.Properties.Remove($recordProperty)
    $didWork = $true
    $itemNeedsUpdate = $true
  }
}

#Remove the icon Record lock overlay
if ($listItem.IconOverlay -eq "lockoverlay.png")
{
  Write-Host "Removing the icon Record lock overlay"
  $listItem.IconOverlay = $null
  $didWork = $true
  $itemNeedsUpdate = $true
}

if ($didWork -ne $true)
{
  Write-Host "No changes were made"
}
Write-Host ""

#Update the item
if ($itemNeedsUpdate -eq $true)
{  
  Write-Host "Updating item"
  $listItem.SystemUpdate()
  
  Write-Host ""
}

if ($eventsOriginallyDisabled -ne $true)
{
  Write-Host "Enabling events"
  $eventsDisabler.EventsDisabled = $false
  Write-Host ""
}

$web.Dispose()
$site.Dispose()

Error When Updating Item Using Web Service MoveNext

Using the SharePoint REST service to update items, ran into this error:

Unable to update the sharepoint document – An error occurred while processing this request.System.Data.Services.Client.DataServiceClientException: <?

xml version=”1.0″ …

…..

at System.Data.Services.Client.DataServiceContext.SaveResult.<HandleBatchResponse>d_1e.MoveNext()

The target list was set to require checkout before items could be updated.  Once I set require checkout to No, the REST service was able to update items.

 

Person or Group Field Append or Remove a Group

Using PowerShell I needed to append a group to a Person or Group field in a list.  The same logic should apply for adding or removing a user.

Append Group:



$web = Get-SPweb "http://sharepointed.com/sites/taco/"
$list = $web.lists["Good Tacos"]

$groupName = "Taco Eaters"
$group = $web.SiteGroups[$groupName]
$GroupValue = new-Object Microsoft.SharePoint.SPFieldUserValue($web,$group.id, $group.Name)

foreach ($item in $list.items)
 {
 $groups = $item["GroupsField"]

$groups.Add($GroupValue)

$item["GroupsField"] = $groups
 $item.Update()
 }

Remove Group:



$web = Get-SPweb "http://sharepointed.com/sites/taco/"
$list = $web.lists["Good Tacos"]

$groupName = "Taco Eaters"
$group = $web.SiteGroups[$groupName]
$GroupValue = new-Object Microsoft.SharePoint.SPFieldUserValue($web,$group.id, $group.Name)

foreach ($item in $list.items)
{
$groups = $item["GroupsField"]

<span style="line-height: 1.5em;">$groupRemove = $groups | ? { $_.LookupId -eq $GroupValue.LookupId }</span>

$groups.Remove($groupRemove)

$item["GroupsField"] = $groups
$item.Update()
}

Change List or Library URL and Name

Recently had a scenario where I needed to move a bunch of libraries from different sites, to another farm. Every library was titled the same, so when I went to import the libraries, I ran into a small issue. Using a combo of Export-SPweb and Import-SPweb, I moved a library over, then updated the library url and name to the source site name.

if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null)
{
Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}

$webURL = “http://sharepointed.com/sites/taco”

$before = “LibBefore”
$after = “LibAfter”

#Change the URL of the library
$web = Get-SPWeb $webURL
$web.lists[$before].RootFolder.MoveTo($after)
$Web.Dispose()

#Update the library name
$web = Get-SPWeb $webURL
$list = $web.Lists[$after]
$list.Title = $after
$list.Update()
$web.Dispose()

Couple notes.
You might be able to shorten the script to rename and update in a couple lines of code.
If you use the script to change the URL of a list, the list will moved to the root of the site. it will no longer live at site/lists.

*I have not tested this with libraries or list that workflows associated with them. It’s also safe to assume any links to documents would be broken.*

Use PowerShell to Update Item Permssions in SharePoint

What if you want to update the item permissions on every item in a list or library, BUT you don’t want to trigger an associated workflow?

Why?

Request came in to add a new SharePoint group to all the items in a library.  Options were to update EVERY item in the library and let the workflow update the permissions, update the items ONE AT A TIME…

OR

Use PowerShell to update item permissions and not stress the server(s).


# add powershell snapin
$web = Get-SPWeb -Identity "http://sharepointed.com"
$list = $web.Lists.TryGetList("Taco Time")
$group = "Taco Makers"
$PermissionLevel = "Read"

#get site group and setup permission/role
$group = $web.Groups[$group]
$roleAssignment = new-object Microsoft.SharePoint.SPRoleAssignment($group)
$roleDefinition = $web.RoleDefinitions[$PermissionLevel];
$roleAssignment.RoleDefinitionBindings.Add($roleDefinition);

if ($list -ne $null)
{
	foreach ($item in $list.Items)
	{
		if ($item.HasUniqueRoleAssignments -eq $False)
		{
			$item.BreakRoleInheritance($True)
		}
	       
                if ($web.SiteGroups[$group] -ne $null)
		{
			$item.RoleAssignments.Add($roleAssignment)
		}
		else
		{
			Write-Host "Group is not valid."
		}		
	}
}
$web.Dispose()

InfoPath Dynamic Hyperlink Field C# and Code Behind

In InfoPath, dynamically update a hyperlink field using code.
Sounded simple, but it took me a few minutes to get it straightened out.

I’ve attached the InfoPath form and the code behind files here: http://www.sharepointed.com/wp-content/uploads/2012/12/Dynamic_URL1.zip

Create a new Blank InfoPath form.

Add a Hyperlink control to the form.

In this step, I’ve set the field to Read-Only. Why? I didn’t want the user to be able to modify the field.

Change the field names. The first field (field1) is the field that will hold the URL path. The second field (field2) will hold the text that you want to display in place of the URL.

I’ve updated my fields as follows:
field1 changed to URLpath
field2 changed to URLdisplay

To quickly test this post, click on the Developer tab, then click Loading Event.

        public void FormEvents_Loading(object sender, LoadingEventArgs e)
        {
            XPathNavigator ipFormNav = MainDataSource.CreateNavigator();
            XPathNavigator assnAttachURL = ipFormNav.SelectSingleNode("/my:myFields/my:URLpath", NamespaceManager);
            XPathNavigator assnAttachDisplay = ipFormNav.SelectSingleNode("/my:myFields/my:URLpath/@my:URLdisplay", NamespaceManager);

            //Update the URL path to a document on your site.
            assnAttachURL.SetValue("http://sharepointed.com/library/taco.xlsx");
            assnAttachDisplay.SetValue("taco");
        }

To get the correct XPath values, see the screenshot below.

To see this work, in the code editor, click on Build, then Start Debugging.