5

I am working on sharepoint 2016 on-premises farm. and i wrote the following power-shell script to update the items of a list:-

>>  $web = get-spweb "http://servername/offices/Departments/IT/"
>>  $list2 = $web.lists["list123"]
>> foreach ($i in $list2.items)
>> {
>> $a = $i["ID"];
>>
>> $sourceItem = $list2.items.GetItemById($a);
>>
>> $sourceItem["NormalNumericColumn"] = $sourceItem["CalcualtedNumericColumn"] ;
;
>>
>> $sourceItem.Systemupdate();
>>
>>
>> }
>>

where what i am trying to achieve to to assign a normal numeric field, the value of a calculated column (which is also of type number). but the above script will raise the following exception:-

Exception calling "SystemUpdate" with "0" argument(s): "Invalid number value.
A number field contains invalid data. Please check the value and try again."
At line:15 char:1
+ $sourceItem.Systemupdate();
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SPException

now if i try to get the value for a non-calculated column, the script will work fine.. so can anyone adivce if there is a way to the value for a calculated column inside my power-shell script ??

6

Your calculated field, even if defined as a number, isn't a full number - it's structured almost like a lookup - there a float;# in front of it.

You can do a .split(';#')[1] to get the value. It will have decimal values, 2 will show up as 2.0000000000000, so you might have to deal with that..

  • @Robim seems your answer was correct we will get the value as follow "float;#123.00000" so we need to use split – john Gu May 17 '17 at 21:09
4

Try this:

if($sourceItem){
    $cf = [Microsoft.SharePoint.SPFieldCalculated]$list2.Fields["CalcualtedNumericColumn"];
    $cf.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"]) //should output value
    }
  • 1
    try my edit, should run only if $sourceItem is found. – Mike May 17 '17 at 20:27
  • 1
    try Write-host $sourceItem["CalcualtedNumericColumn"] – Mike May 17 '17 at 20:32
  • 1
    then try $value = [double] $sourceItem["CalcualtedNumericColumn"].toString().Split('#')[1] – Mike May 17 '17 at 20:37
  • 1
    not sure, but glad it works, sometimes you have to retype in powershell because the character mapping is different – Mike May 17 '17 at 20:59
  • 1
    If you have the value, you won't need that anymore, just assign that value to the other column as you were doing before. $sourceItem["NormalNumericColumn"] = [double] $sourceItem["CalcualtedNumericColumn"].toString().Split('#')‌​[1]; – Mike May 17 '17 at 21:02
2

Try the below code, it is basically get the value of Calculated filed as text then convert it to integer value. Once it is Int then you can set it to your Number column.

 $web = get-spweb "http://servername/offices/Departments/IT/"
$list2 = $web.lists["list123"]
  foreach ($i in $list2.items)
 {
$a = $i["ID"]

 $sourceItem = $list2.items.GetItemById($a)

$calculatedfield = $sourceItem.Fields["CalcualtedNumericColumn"] -as [Microsoft.SharePoint.SPFieldCalculated];            
$c = [int]$calculatedfield.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"])
$sourceItem["NormalNumericColumn"] = $c 
$sourceItem.update();
}
  • thanks for the reply..now in my case the column i want to assign the value to and the calculated column are of type number with automatic number of decimals... so i need to use [double] instead of [int] ?is this correct? – john Gu May 17 '17 at 19:44
  • now this $calculatedfield.GetFieldValueAsText($sourceItem["CalcualtedNumericColumn"]) will raise the following error "you not call the method on a null-valued expression.." – john Gu May 17 '17 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.