I am wondering if anyone has seen an issue when updating an Office 365 list that contains a lookup column? I have 2 Lists in my SharePoint Online site, the first List is my lookup list that maps locations to inventory numbers, the second list uses that lookup List as a column, so I have a powershell script that downloads the lookup List and creates a hashtable with the item ID and one of the columns for the value, then it takes a csv file and loops through it and adds/updates my second List.
I use the hashtable find a match and then to dynamically create the lookup value of "ID;#Value", this works great for all items in the list, except for the last one, for some reason if the item has a value that equals the last item in my lookup list I get an error: office 365 The list item could not be inserted or updated because invalid lookup values were found for the following field(s) in the list
I deleted the last item in the lookup List and recreated it, I still get the same error, to be sure I am passing the proper values my script shows the value from the hashtable and the item in the cvs that matched, the ID of the item from the hashtable, this is created from the lookup List, so there is no chance that one was not entered correctly, I am not sure what to make of this issue. Next step is to add a dummy item at the end of the lookup List and see if that resolves the issue, but even if it does it would only be temporary as they still need to add items to the lookup List.
#cls
# This script creates a hashtable from the COIDs & Facilities List which gives us the COID and the COID's ID value
# which we need to update the Employee Removal List's COID column, which is a lookup column, dynamically. We need
# this value to use to update or add a new item to the List.
# This script also updates the SharePoint Online List Employee Access Removal with values from the HR csv file running as a
# scheduled task each night.
# Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
# Variables for Processing
$SiteUrl = "sharepoint.com"
$ListName="Employee Rewards"
$hashTable = @{}
$ListLookup = "COIDs & Facs"
# Location on the local computer to where the file is stored
$ImportFile ="c:\temp\HR.csv"
# Username and password of an account that can access the Office 365 site
$UserName = "[email protected]"
$Password = "password"
# Define the array to hold all items that are flagged for change
# this will be used to create a log file when we are done
$curItems = @()
# Get the current month number and the 4 digit year so we can filter the csv file for items in the current month and year
$curMonth = Get-Date -UFormat "%m"
$curYear = Get-Date -UFormat "%Y"
# These variables are used to dynamically get the current month and year so we only process the data for each month
$curDate = "$curMonth/*/"+$curYear
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
function GetLookupValues
{
#Set up the context
$lookupCTX = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$lookupCTX.Credentials = $Credentials
$lookupList = $lookupCTX.Web.Lists.GetByTitle($ListLookup)
$lookupItems = $lookupList.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$lookupCTX.Load($lookupItems)
$lookupCTX.ExecuteQuery()
#$items.Count
foreach($item in $lookupItems)
{
$ID = $item["ID"].ToString()
$Facility = $item["Facility"]
$COID = $item["C0ID"].ToString()
$hashTable.Add($ID,$COID)
#Write-Host "ID: $ID | Facility Name: $Facility | COID: $COID"
}
#$hashTable
AddData($hashTable)
}
function AddData($hashTable)
{
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials
# Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
# Get the Data from the CSV file and add a header to it
$data = Import-Csv $ImportFile -Header coid,uid,nameLast,nameFirst,departmentID,departmentDesc,jobTitleID,jobTitle,labels,blank2,email,itemIsARecord,startDate,reasonCode,classificationIDDate,status,nameFull,address1,address2,city,state,zip,description
# Loop through each row in the csv file
foreach($row in $data) {
# Create a variable for the start date
$startDate = $row.startDate
# TODO: Add a try/catch block to handle exceptions
# Filter for the current month and year
if($startDate -like $curDate)
{
# Take care of formatting issues of the names look proper
$fullName = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower() + " " + $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()
# Add items to the List
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$Item = $List.AddItem($ListItemInfo)
$Item["COID"] = $row.COID
# In order to set the value of the COID we have to use the ID we have in our
# $hashTable variable, this maps to the actual COID we want to update
# this is bc we are using a lookup column in our list
$match = $hashTable.GetEnumerator()| Where-Object {$_.value -eq $row.coid}
if($match)
{
# Output the employees name, the current COID, the current value in the hashtable, (these two should match)
# the hashtable Key has the SharePoint List ID of the current COID, which we will use to create a string
# dynamicaly and to make sure the values are what we are expecting.
$fullName
$row.coid
$match.Value
$mKey = $match.Key
$mKey
# Create the value to update the COID column in the format "ID;#Value"
$coidValue = $mKey.ToString() + ";#" + $row.coid.ToString()
#$coidValue
$Item["C0ID"] = $row.coid
$Item["Facility"] = $coidValue.ToString()
# If the column name in the list has a space then this special char set is added to the column name: _x0020_
$Item["Full_x0020_Name"] = $fullName
$Item["Department"] = $row.departmentID
$Item["Department_x0020_Description"] = $row.departmentDesc
$Item["Body"] = $row.description
$Item["Email"] = $row.email
$Item["Job_x0020_Title"] = $row.jobTitle
$Item["First_x0020_Name"] = $row.nameFirst.substring(0,1).toUpper()+$row.nameFirst.substring(1).toLower()
$Item["Last_x0020_Name"] = $row.nameLast.substring(0,1).toUpper()+$row.nameLast.substring(1).toLower()
$Item["Reason_x0020_Code"] = $row.reasonCode
$Item["StartDate"] = $row.startDate
$Item["Status"] = $row.status
$Item["UID"] = $row.uid
$Item.Update()
$Context.ExecuteQuery()
# Add items to the array of what is going to be updated
$curItems += @($row)
}
else
{
# Log the error
}
}
}
# Find out how many records were updated
$importedItems = $curItems.Count
Write-host "$importedItems items to be imported into the Employee Removal SharePoint List. Log file is in the C:\Temp directory"
# Create a log file with the users identified to be updated
$today = Get-Date -UFormat "%Y.%m.%d"
$logfilePath = "C:\temp\"
$logFile = $logfilePath+$today + "-HR-Updates.csv"
$curItems | Export-Csv -Path $logFile -NoTypeInformation -Force
}
# Call the function
GetLookupValues
