Write to Dataverse Notes Table using Web API

I had a challenge recently where I needed to migrate hundreds of notes from an old database to the Dataverse. Dataverse has an Out of the Box Notes table (logical name “annotations”) that allows for recording notes and attachments against a related table. This post will showcase the preparation steps and PowerShell script used to do just that.

Table of Contents hide

1. Preparation Steps

1.1. Enable Notes and Attachments

1.2. Add Timeline Control to Form

1.3. Construct the Payload

2. Putting it all Together

Preparation Steps

Enable Notes and Attachments

For this example, let’s assume we are trying to write rows from a csv into the notes table regarding Purchase Orders. First, ensure the table has the option “Enable Attachments (including notes and files)” enabled. This is key!

Enable this setting. Note that once enabled, it cannot be undone.

Once you complete that step, Dataverse automatically creates a relationship between the two tables behind the scenes. It adds an additional “Regarding” column, which essentially acts as the lookup field.

The relationship created automatically on the Notes Table

Add Timeline Control to Form

Next, To display the notes in a Model Driven App, I will add a timeline control to my Purchase Order Main form. Then, I added a new section called “Notes” and added the timeline control. Don’t forget to save!

Construct the Payload

The tricky part is getting the regarding lookup column populated correctly. We need the following values from our Purchase Order Table:

Set Name: the plural name of the table (bb89_purchaseorders)
Logical Name: The logical name of the Table (bb89_purchaseorder)
The primary identifier (guid) of the record we want to update.

You can copy the set and logical name settings from the Table Tools > Advanced as shown below. While the guid you get from your table records under the primary identifier column

In PowerShell, the payload regarding the associated table starts with “objectid_” as shown below. Additionality, I am updating the subject and notetext logical name columns in the Notes Table.

{
  "objectid_bb89_purchaseorder@odata.bind": "/bb89_purchaseorders(a970c354-e458-f011-bec1-00224802cb4d)",
  "notetext": "This is a comment about subject C",
  "subject": "Test Subject C"
}

Putting it all Together

Here is the completed PowerShell script used. Note that I am using the techniques I described in this prior blog post to obtain my access token and define the environment being used.

<#
.SYNOPSIS
Use script to create and associate a note to a table

.NOTES

Your target Dataverse Table must have "Enable Notes and Attachments enabled first!
Once done, this script will loop through a csv file of notes and associate with the dataverse record defined in the csv file

Csv File should have the following columns:

Subject,Comment,Record

#>

$Notes = Import-Csv .\inputs\NotesToImport.csv

# Update the $Table Variables below with your target table's values

$TableName =  "Purchase Order"
$TableSetName = "bb89_purchaseorders"
$TableLogicalName = "bb89_purchaseorder"

# Dot Source Dataverse Environment and Access Token Scripts

. .\GetDataverseAccessToken.ps1
. .\DataverseParams.ps1

$Params =@{
    ClientId = $DataverseParameters.ClientId
    TenantId =  $DataverseParameters.TenantID
    ClientSecret = $DataverseParameters.ClientSecret
    EnvironmentURL =  $DataverseParameters.EnvironmentURL
    Scope =  "$($DataverseParameters.EnvironmentURL)/.default"
}

$DataverseParameters = Get-DataverseParams
$authResponseObject = Get-DataverseAccessToken @Params

$NoteURI = $Params.EnvironmentURL + "/api/data/v9.2/annotations"

$HeaderInfo = @{
    "Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
    "Accept" = "application/json"
    "OData-MaxVersion" = "4.0"
    "OData-Version" = "4.0"
    "Content-Type" = "application/json"
    "Prefer" = "return=representation"
}

Foreach ($note in $Notes){

    $NoteBody = @{
    subject = $note.Subject
    notetext = $note.Comment
    "objectid_$($TableLogicalName)@odata.bind" = "/$($TableSetName)($($note.Record))" 
    } | ConvertTo-Json

    Write-Host "Adding Note $($note.Subject) to $($TableName) record ID $($note.Record)" -foregroundcolor Cyan

    try {
        Invoke-RestMethod -Uri $NoteURI -Body $NoteBody -Headers $HeaderInfo -Method Post
    }
    catch {
        Write-Host "Error adding note: $($_.Exception.Message)" -ForegroundColor Red
    }

}