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.

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.

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

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

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()
      }
)

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.

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']
       }
)

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
      }
)

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

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

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'="[email protected]")
      }
)