Dataverse - How to Patch the 5 most complex data types

In a previous post, I covered the more complex use cases of the Patch function against a SharePoint data source.

As I mentioned, Patch is the core function to add or update individual record in a data source. Although it's simple to set the value of text and numeric fields, the syntax to set other data types can be complicated.

In this post, we'll cover the syntax to patch Lookup, Choice, Yes/No, and People columns.

Reminder - Dataverse Data Types

To begin, let's take a moment to refamiliarise ourselves with the Dataverse data types. These are shown in the illstration beneath.

The majority of these data types are simple to work with. Column types such as email URL and phone number behave exactly like strings. When we set one of these column values through a call to patch, we can simply pass a string value.

Likewise, all the data types that appear beneath the whole number and date time groups behave as numbers and dates respectively. For these column types, we can simply patch numer or date values.

The more complex data types that we cover in this post appear in the lower section. These include choice, choices, lookup, and customer.

Customer is a notable data type because it's a special type of lookup column, called a polymorphic lookup. The thing that makes this data type special is that we can assign values from either the Account or Contact tables.

Example Dataverse Table

To demonstrate the use of Patch, we'll use the Dataverse table shown here.

This table features the following columns:

Column nameData type   
Description
PropertyLookupA lookup to the property table
DocumentType
Choice
Single selection choice column with the following options -
Document, Image, PDF,Audio
SharePermissionsChoices  Multi-selection choice column with the following options -
Can Share On Website
Can Share With Customer
Can Share Internally
Can Share With Team

ContainsPersonalInfo 
Yes/NoA boolean yes/no field
PropertyAgentCustomer
A lookup to the account table
DocumentContributors  
Person or Group Allows multiple selections of persons or groups

Let's now look at the Patch synax to add new records to this table.

1 .  Lookup Column

To patch a lookup column, we assign a record from the assoicated table by calling the LookUp function.

Here's the syntax to create a new record and to set the property lookup value to the first property record with an Address1 value that matches “10 High Street”.

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
Property: LookUp(Properties, Address1="10 High Street")
}
)

If we were creating or updating a record and want to set a lookup value to null or blank, we can do this by setting the lookup value to Blank.
Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
Property: Blank()
}
)
However, there's a very important caveat when we want to patch null or blank values. We must enable the 'formula level error management' setting. If this setting is not enabled, the patch function will not correctly set the null value.


2 .  Choice column

Here's the syntax to create a new record and to set the document type choice field to “PDF”

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
DocumentType: DocumentType.PDF
}
)
With choice columns, Power Apps is helpful because it exposes all available choice values through an enumeration. In this example (shown in the screenshot beneath), we can access the available choices by typing the name of the column, followed by a period symbol. The IntelliSense in the editor then shows the available choices.

3 . Choices column

To assign multiple values to a Choices column, we assign an array of choice values. The syntax to create a new record and to set the SharePermissions value to "Can Share On Website" and "Can Share With Customer" looks like this.

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
SharePermissions: [SharePermissions.'Can Share On Website',
SharePermissions.'Can Share With Customer']
}
)
This example demonstrates how we enclose the choice items inside square brackets ( eg, []).

4 . Yes/No column

With Dataverse yes/no fields, we can simply pass a boolean true or false value. Here's the syntax to create a new record and to set the ContainsPersonalInfo  field to “yes”.

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
ContainsPersonalInfo: true
}
)

5 . Customer column

To patch a customer column, we assign a record from either the Account or Contact table by calling the LookUp function.

Here's the syntax to create a new record and to set the PropertyAgent value to the first record in the Account table with an account name that matches “ACME Corp”.

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
PropertyAgent: LookUp(Accounts, 'Account Name'="ACME Corp")
}
)

Alternatively, here's the syntax to create a new record and to set the PropertyAgent value to the first record in the Contact table with a Full Name value that matches “Tim Leung”.

Patch(PropertyDocuments,
Defaults(PropertyDocuments),
{Name:"Contract Doc 6 - Account Lookup",
PropertyAgent: LookUp(Contacts, 'Full Name'="Tim Leung")
}
)

6 . User columns

Finally, here's a quick note on user columns. To add a 'user' column to a Dataverse table, we add a look up column that references the user table in Dataverse. To set this value with Patch, we can lookup the user by email address like so.

Patch(PropertyDocuments, 
Defaults(PropertyDocuments),
{Name:"Contract Doc",
DocumentApprover: LookUp(Users, 'Email'="tim1@powerappsguide.com")
}
)

A mistake that some app builders make (particularly those from a SharePoint background) is to attempt patch a value that they retrieve from the 'Office 365 Users' connector. This will fail with an error and therefore, the correct approach is to look up the user record from the users table.

Conclusion

The syntax to patch complex data types to Dataverse can be complicated. This post provides a reference summary to help carry out this task.