SharePoint: A Complete Guide to Getting and Setting Fields 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. This article demonstrates how to set and get the various SPField types for a SharePoint list.
The examples demonstrated 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:
http://4.bp.blogspot.com/-gU4qOefmCHo/UnuXXWjgjGI/AAAAAAAAAR4/UMs__9Xw_r8/s1600/gestsetfields04.png
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
http://3.bp.blogspot.com/-Lc0X3YeEDE0/UnuYzgx_J9I/AAAAAAAAASY/dcmYzOpScPA/s1600/gestsetfields05A-allfields.png
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"];
http://3.bp.blogspot.com/-kajupQTUp4M/UnuYQuQxWGI/AAAAAAAAASA/bEe1rqWcJ7o/s1600/gestsetfields08-number.png
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
http://3.bp.blogspot.com/-ipLBFpdfBw8/UnuYeu00QII/AAAAAAAAASI/8L5N2JEaZIk/s1600/gestsetfields05-currency.png
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"];
http://1.bp.blogspot.com/-MjCVAAtzfyY/UnuYoqFI0NI/AAAAAAAAASQ/2hc4QY9WKAk/s1600/gestsetfields07-percent.png
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)
http://2.bp.blogspot.com/-g65QS9O_H2U/UnuZLl6u2LI/AAAAAAAAASo/1ywEPpvvw6k/s1600/gestsetfields06-date.png
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]
};
http://3.bp.blogspot.com/-aL44Iw6P3UU/UnuaS6pZuKI/AAAAAAAAAS4/haabRcvFfhI/s1600/gestsetfields09-mc.png
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}
http://3.bp.blogspot.com/-Iv8G8k22WBY/UnubIg0lzDI/AAAAAAAAATE/oxObrq4Fmj0/s1600/gestsetfields10-mu.png
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 Multiple Valued Managed Metadata Field
Set a Multiple 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 Multiple 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"]);
http://4.bp.blogspot.com/-2xTDv4M-CE8/UpbW5HDfjhI/AAAAAAAAATc/AewsUcEXqOk/s1600/psfr5.png
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.
- Formula: The formula used to calculate the value.
- OutputType: The type of the value that results from the calculation. Supported types are, Text, Number, Integer, Currency, Boolean and DateTime.
- DisplayFormat: Used with number, integer and currency to specify the number of decimal places
- DateFormat: Used with DateTime to specify Date, or Date and Time.
In the following example, we perform the following tasks:
- View the current formula
- View the current display format
- Change the display format from two decimal places to four decimal places
- Change the output type from currency to integer
- 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"]);
http://1.bp.blogspot.com/-KujNbE2HZhE/UpbWkGoIRPI/AAAAAAAAATU/_Em-v8IkWNg/s1600/psfr6.png
See Also
- SharePoint: A Complete Guide to Getting and Setting Fields using C#
- SPField
- SPFieldText
- SPFieldMultiLineText (Note)
- SPFieldBoolean
- SPFieldCalculated
- SPFieldChoice
- SPFieldCurrency
- SPFieldDateTime
- SPFieldLookup
- SPFieldMultiChoice
- SPFieldMultiChoiceValue
- SPFieldNumber
- SPFieldUrl
- SPFieldUrlValue
- SPFieldUserValue
- SPFieldUserValueCollection
- TaxonomyField
- TaxonomyFieldValue
- TaxonomyFieldValueCollection
- SPNumberFormatTypes
- SPFieldType
- SPDateTimeFieldFormatType