4

I have a multi values lookup column. I'm trying to update another column on another list from a lookup column. So let say I have values in a lookup column like so:

1;#Open 2;#Close 3;#Closed

I want to use this values to update another column that uses a lookup column. I can update if its one value e.g. 1;#Open

But when I have multiple values (e.g. the 3 of them) I get error :

Cannot convert value "Event Type" to type "System.Int32". Error: "Input string was not in a correct format."

$CMRSItems = $list.Items | where {$_['ID'] -le 5}

$CMRSItems | ForEach-Object {
$val = GetFormattedValue -val $_['Event TypeOld']
Write-Host "RefID: " $_['RefID']

$t = $val -replace ";"," "



$array = @($t)
foreach ($i in $array) {


    $c = $array.Count
    write-host $c

      $r = GetLookUpValues -val $i
      #Write-Host $r


      $documents = New-Object Microsoft.SharePoint.SPFieldLookupValueCollection($r);

      for ($i = 0; $i -lt $array.Count; $i++)
        {


           $CMRSItems["Event Type"] = $documents[$i];
           $CMRSItems.Update()

        }



 }


}



function GetFormattedValue([string]$val)
{
$tvalue = $val.Split("#") -match "\d{1,3}"
return $tvalue 
#-replace ";","`t"
}

function GetLookUpValues([string] $val)
{
foreach ($item in $lookupitem.GetItemById($val))

{
$lookupvalue = New-Object Microsoft.SharePoint.SPFieldLookupValue($item.ID,$item.Name.ToString());  

#Write-Host "lookupcol value" $lookupvalue

}
return $lookupvalue
}
  • Can you please add some extra explanation of what your code is doing, because it doesn't make much sense just reading it. Why the replacing of ";" with tabs ? Can you reduce it to just the relevant parts ? And what exactly are you trying to update with the multi value lookup column's values ? – Cameron Verhelst Mar 11 '14 at 7:06
  • Sorry didn't elaborate enough. As you mentioned just look at the last piece of the code.All i want to do is set/update a lookup column.I can update the column if its just one value but i can't seem to update if i need to set/update multi values. Hope this helps – naijacoder Mar 11 '14 at 12:44
3

There are various ways to get and set a lookup field

How to get a lookup field

Single value

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

Multi value

SPFieldLookupValueCollection itemValues = item["FieldName"] as SPFieldLookupValueCollection;
foreach (SPFieldLookupValue itemValue in itemValues)
{
  int id = itemValue.LookupId;
  string value = itemValue.LookupValue;
}

How to set van value

Single value

$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();

Multi value

SPFieldLookupValueCollection itemValues = SPFieldLookupValueCollection();
itemValues.Add(new SPFieldLookupValue(1, "Title"));
item["FieldName"] = itemValues;
item.Update();

See this document for more examples on how to set and get values with PowerShell

  • Thanks Remko.Im looking for a solution for powershell for setting a column with a lookup field(multi value) as you suggested – naijacoder Mar 14 '14 at 5:34
  • Remko i tried this solution but doesn't work when i need to update multiple values.it only updates the last value.So basically it updates on a single value – naijacoder Mar 17 '14 at 11:27
  • Are you sure you are using the SPFieldLookupValue collection option? Also add the existing values before updating, because its an override of the value not an add. – Remko Mar 17 '14 at 12:50
0

This is how it will work. Pay attention to the # sign: not in front of the first choice but all the others.

$listItem["ColumnLookupMulti"] = "1;#Open;#2;#Close;#3;#Closed"
$listItem.Update()

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.