(PnP Powershell) Note type fields appear to be empty when versioning is enabled

Question

I'm trying to build a script that will copy the contents of a list from a site to another list in a subsite or a different site.

Unfortunately many multiline text fields (FieldType = Note) appear to be empty even though they're not, when versioning is enabled. Versioning is enabled on the source list and all BUT ONE of these Note Type fields have versioning enabled. They all store simple text (not RTF or HTML).

Oddly enough, some rows are readable and some are not, seemingly at random.

$source = "https://tenant.sharepoint.com/sites/source-site"
$sourceListName = "sourceList"

Connect-PnPOnline -Url $source -UseWebLogin
$SourceListItems = Get-PnPListItem -List $SourceListName

#first item: 
$SourceListItems[0]["Diagnosi"] # returns nothing
$SourceListItems[0]["Diagnosi"].ToString() # returns an error "impossible to use a method on an expression with null value" or something like that
$SourceListItems[0].FieldValues # returns what you can see in the attachment, among the other fields

How can I effectively collect the values stored in those fields where versioning is enabled? TA

Answer 1

I think you have an option on the note field enabled to append text to already existing content in that field. What happens is that when that is enabled SP saves the info in item version if the last version didn't append the text it will be empty. Out of the box UI traverses the version history to show the full appended version of the note field. You would have the recreate that behavior in your code.

Answer 2

First, if you are truly getting nothing when you examine $SourceListItems[0]["Diagnosi"], it is possible that you need to tell Get-PnPListItem to fetch that column for you. Depending on how many columns are in the list, Get-PnPListItem may not bring back all the fields you want, so it is safer to just specify which fields you need, for example: $SourceListItems = Get-PnPListItem -List $SourceListName -Fields "Diagnosi". Also see example #7 on the documentation for Get-PnPListItem.

Secondly, and likely the bigger problem, when you have "Append Changes to Existing Text" set on a multi-line text field, SharePoint isn't actually appending anything to the text field, it is still overwriting and saving the value like normal. The User Interface is just doing some trickery behind the scenes to loop through that item's version history and display the various iterations of that field on the page.

So, you'll need to essentially do the same in your code:

$appendedTextValue = ""
foreach($ver in $SourceListItems[0].Versions)
{
    #Print the user who modified the version.
    $author = $ver["Editor"]
    $authorObj = New-Object Microsoft.SharePoint.SPFieldUserValue($web, $author)
    $authorDisplayName = $authorObj.User.DisplayName
    if ($ver["Diagnosi"] -ne $null) {
        $appendedTextValue += $authorDisplayName +"(" + $ver["Created"] + ")" + "`n" + $fld.GetFieldValueAsText($ver[$col]) + "`n`n"
    }
}