Wednesday, 6 November 2013

A Quick Guide to Getting and Setting the various SPField types using PowerShell

Introduction

PowerShell is a really powerful tool to know how to use. It's invaluable as a SharePoint Administrator, or as a SharePoint Developer. In this post, I want to demonstrate how to set and get the various SPField types for a SharePoint list.

The examples demostrated below set and get fields from an item that belongs to a custom list. The custom list contains a number of fields, and all the fields are named based on the type of field they are. For example, there is a Text field, which has been named, textfield. This is depicted in the following picture:


Applies To

The examples demonstrated below are tested with and apply to the following versions of SharePoint:
  • SharePoint 2010
  • SharePoint 2013

Displaying all the fields

The example below gets the list and displays all the user created fields.

$w = Get-SPWeb "http://devmy131"            
$l = $w.GetList("http://devmy131/lists/fieldslist");            
$l.Fields | sort StaticName,Type | ?{$_.CanBeDeleted -eq $true -and $_.Hidden -eq $false} | FT Title,StaticName,Type



Add an Item

This is the basic PowerShell for creating a new list item.

$w = Get-SPWeb "http://devmy131"            
$l = $w.GetList("http://devmy131/lists/fieldslist");            
$i = $l.Items.Add();

Get an Item

This is the basic PowerShell for getting the first item in the list.

$w = Get-SPWeb "http://devmy131"            
$l = $w.GetList("http://devmy131/lists/fieldslist");        
$i = $l.Items[0];

Set and Get the Title

Set the title

$i["Title"] = "Using PowerShell to set SPFields";            
$i.Update();

Get the title

$i["Title"].ToString();

Set and Get a Text Field

Set a text field

$i["textfield"] = "Dogs cannot write poems";            
$i.Update();

Get a text field

$i["textfield"].ToString();            
$text = $i["textfield"];

Set and Get a Note (or RichText) Field

Set a note field

$i["notefield"] = "Dogs cannot write poems.`r`nBut then again, either can I!";            
$i.Update();

Get a note field

$i["notefield"].ToString();            
$text = $i["notefield"];

Set and Get a Yes/No Field (Boolean)

Set a yes/no field

$i["yesnofield"] = $false;            
$i.Update();

Get a yes/no field

$i["yesnofield"].ToString();            
$yesorno = $i["yesnofield"];

Set and Get a Number Field

The type of a Number field is a Double. When you get the number from the field, you can use standard numeric format specifies to format the number for display. See Double.ToString for more information.

Set a number field

$i["numberfield"] = 35;             
$i.Update();            
#-or-            
$i["numberfield"] = [Double]::Parse("45.67");            
$i.Update();

Get a number field

$i["numberfield"].ToString();            
$i["numberfield"].ToString("F");            
$i["numberfield"].ToString("F0");            
[Double]$number = $i["numberfield"];



Set and Get a Currency (Number) Field

A currency field uses the same SharePoint field type as Number (SPFieldNumber). The type of a Number field is a Double. When you get the number from the field, you can use standard numeric format specifies to format the number for display, specifically, formatting it as a currency. See Double.ToString for more information.

Set a currency field

$i["currencyfield"] = [Double]::Parse("123.48");            
$i.Update();

Get a currency field

$i["currencyfield"].ToString();            
$i["currencyfield"].ToString("C2");            
[Double]$double = $i["currencyfield"];            
$double + 432



Set and Get a Percent (Number) Field

A percentage field uses the same SharePoint field type as Number (SPFieldNumber). The type of a Number field is a Double. When you get the number from the field, you can use standard numeric format specifies to format the number for display, specifically, formatting it as a percentage. See Double.ToString for more information.

Set a percent field

$i["percentfield"] = [Double]::Parse("0.8"); #0.2 for 20%, 1 for 100% etc            
$i.Update();

Get a percent field

$i["percentfield"].ToString();            
$i["percentfield"].ToString("P");            
$i["percentfield"].ToString("P0");            
[Double]$percent = $i["percentfield"];



Set and Get a Date Field

To set a date field, use a System.DateTime object to create a date, then assign the DateTime object to the list item's field. When you retrieve the value of a DateTime field, you can use standard date format specifiers to format the output of the value. See DateTime.ToString for more information.

Set a date field

$i["datefield"] = [System.DateTime]::Now;             
$i.Update();            
#-or-            
$i["datefield"] = [System.DateTime]::Now.AddDays(-2);            
$i.Update();

Get a date field

$i["datefield"].ToString();            
$i["datefield"].ToString("d");            
$i["datefield"].ToString("D");            
$i["datefield"].ToString("R");            
[DateTime]$date = $i["datefield"];            
$date.AddDays(13)



Set and Get a Choice Field

Set a choice field

$i["choicefield"] = $l.Fields["choicefield"].GetFieldValue("Green");            
$i.Update();

Get a choice field

$i["choicefield"].ToString();            
$choicefield = $i["choicefield"];

Set and Get a Multi-Choice Field

Set a multi-choice field

$choicevalues = New-Object Microsoft.SharePoint.SPFieldMultiChoiceValue;
$choicevalues.Add("Green");            
$choicevalues.Add("Blue");            
$i["multiplechoicefield"] = $l.Fields["multiplechoicefield"].ParseAndSetValue($i,$choicevalues);            
$i["multiplechoicefield"].ToString();            
$i["multiplechoicefield"] = $choicevalues;            
$i.Update();            
#-or-            
$l.Fields["multiplechoicefield"].ParseAndSetValue($i,$choicevalues);            
$i.Update();

Get a multi-choice field

$multichoicevalues = New-Object Microsoft.SharePoint.SPFieldMultiChoiceValue($i["multiplechoicefield"].ToString());            
$multichoicevalues.ToString();            
for($c=0;$c-lt$multichoicevalues.Count;$c++)            
{            
 $multichoicevalues[$c]            
};



Set and Get a Person Field

Set a person field

$i["personfield"] = $w.EnsureUser("domain\user");             
$i.Update();            
#-or-            
$i["personfield"] = $w.EnsureUser("matthew.yarlett@company.com");            
$i.Update();

Get a person field

$i["personfield"].ToString();            
$userfield = New-Object Microsoft.SharePoint.SPFieldUserValue($w,$i["personfield"].ToString());            
$userfield.User.DisplayName;            
$userfield.User.Email;

Set and Get a Multi-Person Field

Set a multi-person field

[Microsoft.SharePoint.SPFieldUserValueCollection]$lotsofpeople = New-Object Microsoft.SharePoint.SPFieldUserValueCollection            
$user1 = $w.EnsureUser("domain\user1");            
$user1Value = New-Object Microsoft.SharePoint.SPFieldUserValue($w, $user1.Id, $user1.LoginName)            
$user2 = $w.EnsureUser("domain\user2");            
$user2Value = New-Object Microsoft.SharePoint.SPFieldUserValue($w, $user2.Id, $user2.LoginName);            
$lotsofpeople.Add($user1Value);            
$lotsofpeople.Add($user2Value);            
$i["lotsofpeoplefield"] = $lotsofpeople;            
$i.Update();            
#-or-            
$l.Fields["lotsofpeoplefield"].ParseAndSetValue($i,$lotsofpeople);            
$i.Update();

Get a multi-person field

[Microsoft.SharePoint.SPFieldUserValueCollection]$lotsofpeople = $i["lotsofpeoplefield"]     
$lotsofpeople.ToString();
foreach($uv in $lotsofpeople.ToArray()){$uv.User}            
foreach($uv in $lotsofpeople.ToArray()){$uv.User.DisplayName}



Set and Get a Lookup Field

Set a lookup field

$lookupfield = $l.Fields["lookupfield"] -as [Microsoft.SharePoint.SPFieldLookup];            
$lookuplist = $w.Lists[[Guid]$lookupfield.LookupList];            
$sourcefield =  $lookupfield.LookupField;            
$lookupitem = $lookuplist.Items[0];             
#-or-            
$lookupitem = $lookuplist.GetItemByUniqueId([Guid]"{fc71b84c-74d4-4f7c-9eed-fb7a5fbe24a6}")  
#-or-            
$lookupitem = $lookuplist.GetItemById(1)            
$lookupvalue = New-Object Microsoft.SharePoint.SPFieldLookupValue($lookupitem.ID,$lookupitem.ID.ToString());            
$i["lookupfield"] = $lookupvalue;            
$i.Update();

Get a lookup field

$lookupfieldvalue = $i["lookupfield"] -as [Microsoft.SharePoint.SPFieldLookupValue]          
$lookupfieldvalue.LookupValue;

Setting and Getting a HyperLink Field

Set a hyperlink field

$hyperlinkfield = $l.Fields["hyperlinkfield"] -as [Microsoft.SharePoint.SPFieldUrl];         
$urlFieldValue = New-Object Microsoft.SharePoint.SPFieldUrlValue;            
$urlFieldValue.Description = "Microsoft";            
$urlFieldValue.Url = "http://www.microsoft.com";            
$hyperlinkfield.ValidateParseAndSetValue($i,$urlFieldValue.ToString()); #SharePoint 2013 only
$i.Update();            
$hyperlinkfield.ParseAndSetValue($i,$urlFieldValue.ToString()); #SharePoint 2010 and 2013    
$i.Update();

Get a hyperlink field

$hyperlink = $i["hyperlinkfield"] -as [Microsoft.SharePoint.SPFieldUrlValue];
$hyperlink.Url;            
$hyperlink.Description;

Setting and Getting a Managed Metadata Field

Set a Managed Metadata field

$managedmetadataField = $l.Fields["managedmetadatafield"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];            
$tsId = $managedmetadataField.TermSetId;            
$termStoreId = $managedmetadataField.SspId;            
$tsession = Get-SPTaxonomySession -Site $l.ParentWeb.Site;            
$tstore =  $tsession.TermStores[$termStoreId];              
$tset = $tstore.GetTermSet($tsId);                  
$termName = "Frog Catchers";            
$terms = $tset.GetTerms($termName,$false);            
$term = $null;            
if($terms.Count -eq 0)            
{            
 Write-Host ([String]::Format("Creating Term, {0}",$termName)) -ForegroundColor DarkYellow;            
 $term = $tset.CreateTerm($termName, $tstore.Languages[0]);            
 $tstore.CommitAll();            
}             
else            
{            
 $term = $terms[0];            
}            
$managedmetadataField.SetFieldValue($i,$term);            
$i.Update();

Get a Managed Metadata field

$taxFieldValue = $i["managedmetadatafield"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue];            
$taxFieldValue.Label;

Setting and Getting a Multi-Valued Managed Metadata Field

Set a Multi-Valued Managed Metadata field

$multiplemanagedmetadatafield = $l.Fields["multiplemanagedmetadatafield"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyField];                        
$tsId = $multiplemanagedmetadatafield.TermSetId;                        
$termStoreId = $multiplemanagedmetadatafield.SspId;                        
$tsession = Get-SPTaxonomySession -Site $l.ParentWeb.Site;                        
$tstore =  $tsession.TermStores[$termStoreId];                          
$tset = $tstore.GetTermSet($tsId);                              
$termCollection = new-object Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection($multiplemanagedmetadatafield);            
$taxonomyTerms = @("Frog Catcher","Giraffe Stealer","Lion Rider");            
foreach($t in $taxonomyTerms)            
{            
 $terms = $tset.GetTerms($t,$false);                        
 $term = $null;                        
 if($terms.Count -eq 0)                        
 {                        
  Write-Host ([String]::Format("Creating Term, {0}",$t)) -ForegroundColor DarkYellow;                        
  $term = $tset.CreateTerm($t, $tstore.Languages[0]);                        
  $tstore.CommitAll();                        
 }                         
 else                        
 {                        
  $term = $terms[0];                        
 }              
 $termValue = new-object Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue($multiplemanagedmetadatafield);            
 $termValue.TermGuid = $term.Id.ToString();            
 $termValue.Label = $term.Name;            
 $termCollection.Add($termValue);            
}            
$multiplemanagedmetadatafield.SetFieldValue($i,$termCollection);            
$i.Update();

Get a Multi-Valued Managed Metadata field

$taxFieldValueCollection = $i["multiplemanagedmetadatafield"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValueCollection];                        
foreach($taxFieldValue in $taxFieldValueCollection)            
{            
 Write-Host "Term from a TaxonomyFieldValue object:"$taxFieldValue.Label;             
 #Get the term            
 $term = $tstore.GetTerm((new-object Guid($taxFieldValue.TermGuid)));             
 Write-Host "Term from a TermSetItem object:"$term.Name;            
}

Setting and Getting a Calculated Field

Calculated fields work in a different manner to that of normal fields. A formula is set on the list field, and when a list item is added or updated, the value of the column for that list item is calculated based on the formula.

Get a Calculated Field value

Get a reference to the calculated field. Then get a reference to the list item. Finally, call the GetFieldValueAsText method, passing in the value of the item objects calculated field. 

$i = $l.Items[0];            
$calculatedfield = $i.Fields["calculatedfield"] -as [Microsoft.SharePoint.SPFieldCalculated];            
$calculatedfield.GetFieldValueAsText($i["calculatedfield"]);


Set the Properties of a Calculated Field

The value of a calculated field is calculated at the time a list item is created or updated. It is not possible to directly set this value. You can use the following methods to set the formula though, which is used to calculate the fields value. Before looking at these methods, there are four main properties that can be set on a calculated field; Formula, OutputType, DisplayFormat and DateFormat. Which properties you need to set, depends on the value of the calculation.
  1. Formula: The formula used to calculate the value. 
  2. OutputType: The type of the value that results from the calculation. Supported types are, Text, Number, Integer, Currency, Boolean and DateTime. 
  3. DisplayFormat: Used with number, integer and currency to specify the number of decimal places
  4. DateFormat: Used with DateTime to specify Date, or Date and Time.
In the following example, we perform the following tasks:
  1. View the current formula
  2. View the current display format
  3. Change the display format from two decimal places to four decimal places
  4. Change the output type from currency to integer
  5. Change the formula, output type and set the date format

$w = Get-SPWeb "http://devmy131"                        
$l = $w.GetList("http://devmy131/lists/fieldslist");            
$i = $l.Items[0];            
$calculatedfield = $i.Fields["calculatedfield"] -as [Microsoft.SharePoint.SPFieldCalculated]            
$calculatedfield.Formula;            
$calculatedfield.DisplayFormat;            
            
$calculatedfield.DisplayFormat = [Microsoft.SharePoint.SPNumberFormatTypes]::FourDecimals;            
$calculatedfield.Update();            
$calculatedfield.GetFieldValueAsText($i["calculatedfield"]);            
            
$calculatedfield.OutputType = [Microsoft.SharePoint.SPFieldType]::Integer            
$calculatedfield.Update();            
$calculatedfield.GetFieldValueAsText($i["calculatedfield"]);            
            
$calculatedfield.Formula = "=[datefield]+90";            
$calculatedfield.DateFormat = [Microsoft.SharePoint.SPDateTimeFieldFormatType]::DateOnly;            
$calculatedfield.OutputType = [Microsoft.SharePoint.SPFieldType]::DateTime;            
$calculatedfield.Update();            
$i.SystemUpdate();            
$calculatedfield.GetFieldValueAsText($i["calculatedfield"]);


See Also


Posted by at
Labels: , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)