0

I have written a powershell script which reads off of a CSV and creates records in a list. There are duplicate customer names within the CSV but they will have a different 'interested system' field.

On the list the interested system field is a choice with multiple choices allowed.

I have set the correct logic in the script to trigger if the record is a duplicate, If it is a duplicate only the 'Interested system' field will get updated.

The issue that i am facing is that when i use the code to update the Choice value of the field it is clearing the field and only adding the new choice. I am using a CAML query to do so.

Please see below script and please also ignore the commented parts. These were just my tests.

Function AddCustomerRecords($SiteURL,$ListName,$CSVPath,$IntentSignal)
{ 
#Remove-Variable PreviousValue -ErrorAction SilentlyContinue

#Remove-Variable PreviousValue1 -ErrorAction SilentlyContinue
#Read customer records from the CSV file
$CustomerData = Import-CSV $CSVPath 

 
$i = 0;
$TotalRecords = $CustomerData.Count

#Iterate through each row in the CSV and import it as a list item to the SPO List.
ForEach ($Record in $CustomerData)
{
$i++;
#$PreviousValue=$null;
#$PreviousValue1=$null;

#Remove-Item variable: PreviousValue
#Remove-Item variable: PreviousValue1
Write-host "Adding the record for company '$($Record.'Company Name')'" "$i out of $TotalRecords completed"

$CheckURL = $Record.'URL'
$HTTP = 'Http://'
$CheckDup = $Record.'Email'

if($CheckURL -notlike 'http*')
{

$CheckURL = "$HTTP$CheckURL"

Write-host 'Http has been appended onto the provided URL'

} 
else{
$CheckURL =$Record.'URL'

 }



#$ItemIDRef=  Get-PnPListItem -List $ListName -Fields "ID"


#$ItemIDRef1= $ItemIDRef.FieldValues.ID

$items2 = Get-PnPListItem -List $ListName -Query "<View><Query><Where><Eq><FieldRef Name='Email'/><Value Type='Text'>$CheckDup</Value></Eq></Where></Query></View>"

#$previousvalues = Get-PnPListItem -List $ListName -Fields "InterestedSystem" 

#$items2 = Get-PnPListItem -List $ListName -Query "<View><Query><Where><Eq><FieldRef Name='Email'/><Value Type='Text'>$CheckDup</Value></Eq></Where></Query></View>"


#$PREVid= Get-PnPListItem -List $ListName -Fields "ID"



#$PreviousSignal
if($items2)
{
ForEach ($PrevItem in $Items2)
{
#$PreviousValue1= $null

#$PREVid= Get-PnPListItem -List $ListName -Fields "ID"

#$PreviousValue= Get-PnPListItem -List $ListName -Fields "InterestedSystem" 

#$PreviousValue1= $PreviousValue.FieldValues.InterestedSystem


Write-host 'This Record already exists. Adding the Signal Intents'

Write-Host "$PreviousValue1"

Set-PnPListItem -List $ListName -Identity $items2 -Values @{"InterestedSystem" = "$IntentSignal"}

#Remove-Variable -Name PreviousValue
#Remove-Variable -Name PreviousValue1
}

}
else{


#Add List Item
Add-PnPListItem -List $ListName -Values @{
"Title" = $Record.'First Name';
"Last_x0020_Name" = $Record.'Last Name';
"CompanyName" = $Record.'Company Name';
"Email" = $Record.'Email'
"Role" = $Record.'Title'
"Phone" = $Record.'Phone'
"AddressLine1" = $Record.'Address Line 1'
"AddressLine2" = $Record.'Address Line 2'
"Address_x0020_Line_x0020_3" = $Record.'Address Line 3'
"PostalCode" = $Record.'PostalCode'
#"DirectPhone" = $Record.'Direct Phone'
"InterestedSystem" = $IntentSignal
"ContactMade" = "New"
"URL" = $CheckURL

};

}
 
}
}

Any help would be greatly appreciated. I have attempted to save the previous value as a variable and then shoot it back in along side the choice but this seems to make the choices grow rapidly and is not working properly.

Thanks

2
  • Is the multi choice field or single choice field? Aug 30, 2022 at 10:22
  • Hi Kalpesh it is a multi choice field
    – Adz127
    Aug 30, 2022 at 10:36

1 Answer 1

0

You need to get existing field value for choice and append new values to it.

Use below code inside for each loop where you are updating item. It should work

    if($items2)
    {
        ForEach ($PrevItem in $Items2)
        {
            #$PreviousValue1= $null

            #$PREVid= Get-PnPListItem -List $ListName -Fields "ID"

            #$PreviousValue= Get-PnPListItem -List $ListName -Fields "InterestedSystem" 

            #$PreviousValue1= $PreviousValue.FieldValues.InterestedSystem

            
            $PreviousValue1 = ""
            Write-host 'This Record already exists. Adding the Signal Intents'

            Write-Host "$PreviousValue1"

            $prevValueColl = $PrevItem["InterestedSystem"]
            $isExist = $false
            foreach($prevValueChoice in $prevValueColl)
            {
               if($prevValueChoice -eq $IntentSignal)
               {
                  $isExist = $true;
               }
               $PreviousValue1 = $PreviousValue1 + ";" + $PreviousValue1
            }

            if($isExist -eq $false)
            {
                $PreviousValue1 = $PreviousValue1 + ";" + $IntentSignal
            }


            Set-PnPListItem -List $ListName -Identity $items2 -Values @{"InterestedSystem" = $PreviousValue1}

            #Remove-Variable -Name PreviousValue
            #Remove-Variable -Name PreviousValue1
        }

    }
5
  • Updated my answer as there was one mistake there Aug 30, 2022 at 12:07
  • Hi Kalpesh, Thanks for your response. This script is still only adding the First IntentSignal and ignoring the second?
    – Adz127
    Aug 30, 2022 at 12:39
  • Can you add some write-host to show $PreviousValue1 and paste screen shot here? Aug 31, 2022 at 4:00
  • After the write host the previous value is returning ;
    – Adz127
    Aug 31, 2022 at 8:30
  • Hey Kalpesh are you able to help?
    – Adz127
    Sep 1, 2022 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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