Sunday 16 October 2011

Programmatically add lookup field values to sharepoint lists using powershell

This weekend, I have been scripting powershell to populate several SharePoint relational lists.  Data is imported from excel (csv) files in this case.  

The whole piece is for an application I am developing.  However, I had some difficulties with lookup lists.  I refined a function (highlighted in blue) which i am to share here.

So for example, calling code (highlighted in orange) can get the special format of [ID];#[NAME]ready for importing to the field.

$spweb = Get-SPWeb $weburl 
    try
    {

        # CSV path/File name
        $contents = Import-Csv $csvPath                    
        $lists = $spweb.Lists;
        $list = $lists[$listName];
        $managerList = $lists["Manager"];
       
        # Delete existing List Data, If not required remove this line
        try
        {
            $list.get_items() | % { $_.Delete() }
        }
        catch
        {
            #ignore the list could be empty
        }
        # Iterate for each list column
        foreach ($row in $contents)
        {           

              $item = $list.items.add()    
              # Check if cell value is not null
              if ($row.EmployeeLanId -ne $null)
                {$item["EmployeeLanId"] = $row.EmployeeLanId}    
              $item.update()                   
              if ($row.EmployeeEmail -ne $null)
                {$item["EmployeeEmail"] = $row.EmployeeEmail}   
              $item.update()
              if ($row.ManagerLanId -ne $null)             
                {$item["ManagerLanId"] = GetFormattedLookupValue $spWeb "Manager" $row.ManagerLanId "ManagerLanId" }                                           
              $item.update()
        }
    }
    catch
    {
        write-host “Execution stopped due to: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
    }
    finally
    {
        $spweb.Dispose();
    }




<#
GetFormattedLookupValue - return a string in the format id;#name
param [object]  $spWeb sharepoint web
param [string]    $lookupListName path of the csv file
param [string]    $lookupListName        the name of the list   
param [string]    $fieldName url of the site
return [string] id;#name
#>
function GetFormattedLookupValue([object]$spWeb, [string]$lookupListName, [string]$lookupListName, [string]$fieldName)
{
    $spweb = Get-SPWeb $weburl 
    try
    {
                   
        $lists = $spweb.Lists;
        $LookupList = $lists[$lookupListName];

        try
        {           
            $LookupItem = $LookupList.Items | Where-Object { $_.Item($fieldName) -eq $valueToLookFor }
            $Lookup = ($LookupItem.ID).ToString() + ";#" + ($LookupItem[$fieldName]).ToString()
        }
        catch [System.Exception]
        {
            write-host “Problem getting lookup: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
        }
    }
    catch [System.Exception]
    {
        write-host “Problem adding lookup: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
    }
    finally
    {
        $spweb.Dispose();
    }

    return $Lookup
}

Sunday 18 September 2011

New Blog

I have spent some time consolidating all my past efforts in the realm of sharepoint and related.  I am lucky enough to be based in London which offers me the opportunity to study in the evenings at Birkbeck.  Lots of content around the various courses has been generated, I decided to share this.

I am impressed how easy it is to generate content with google docs, sites and an android phone.  In no time at all a site can be setup.

Site, powered by Google, https://sites.google.com/site/tristian2portfolio.