Tag Archives: Dataverse Web API

Dataverse Web API Tip #4: Alternate Identities (Alternate Keys)

• , ,

Out of the box with Microsoft Dataverse, a single table row may be referenced using pluralized-table-name(primary-key) syntax, like:

GET {{webApiUrl}}stores(78c91f30-0e86-4716-8718-176c14174cab)
(include the "always include" headers)

However, there are times where you know that rows in a table may also be uniquely identified using another column’s value (or maybe even a unique set of values across several columns). For example, imagine a table describing your company’s stores. In the technical domain, Dataverse identifies each row using an auto-assigned GUID primary key—but to the business, each store is identified by a numerical store number stored in a StoreId column, like StoreId = 1 or StoreId = 2.

You can formally recognize the presence of an alternate unique identifier by declaring the relevant column (or columns) as an alternate key.

Defining an alternate key brings with it several advantages, including:

  • It’s uniqueness will be enforced. The system will not allow multiple rows to have the same key value (so, in our example, if StoreId is declared as an alternate key, two records with StoreId = 1 won’t be allowed).
  • The ability to reference rows using the alternate key (more on this below).
  • Optimized lookup performance.
Continue reading →

Dataverse Web API Tip #3: Upsert

• ,

In database nomenclature, an upsert (update-or-insert) is an operation that results in the specified record being updated if it exists; and, if not, a new record being inserted.

Microsoft Dataverse’s Web API PATCH operation preforms upserts by default. It’s important to be aware of what this means and, when desired, how to change it, so you’re not inadvertently upset by an unexpected upsert.

What does the below request do?

PATCH {{webApiUrl}}contacts(00000000-0000-0000-0000-000000000001)
(include the "always include" headers)

{
  "firstname": "Bob",
  "lastname": "Brown"
}

If you said that it updates the contact with the primary key of 00000000-0000-0000-0000-000000000001, setting its first and last names, you are correct if a contact with that ID exists. However, if it doesn’t—thanks to Dataverse’s identity insert behavior—the request will result in a new record being created with the given primary key, first and last name values. What you expected to be an update turned into an insert.

Is upserting a good behavior? It depends.

Continue reading →

Dataverse Web API Tip #2: Identity Insert

• ,

By default, Microsoft Dataverse auto-generates primary key values when data is inserted. Often, this is fine: these GUID key values are not necessarily of interest to users—in fact, users may not even be aware they exist!

However, when external integrations are involved, there are times where it may be advantageous to explicitly set the primary key. That is, instead of Dataverse generating the key’s value for a particular record, you specify what that value should be when you insert the record. In the database world, bypassing an identity column’s auto-generation behavior like this is sometimes known as “identity insert,” as you are inserting the record’s identity value along with the other data that defines it.

For example, suppose you are inserting a record into Dataverse that corresponds with an entity in another system. To link between the two, you could add an “external ID” column to the table in Dataverse and populate it with the corresponding ID from the external system—or if that external ID is a GUID (or can be transformed into a GUID), you could skip creating an extra column and instead simply set the Dataverse record’s primary key value to match the external system’s GUID key!

Continue reading →

Dataverse Web API Tip #1: The “Always Include” Headers

• ,

All HTTP requests to Microsoft’s Dataverse Web API should include, at minimum, the following four headers:

Accept: application/json 
OData-MaxVersion: 4.0 
OData-Version: 4.0
If-None-Match: null

The last one’s value can be confusing. By default, it should be set to a literal string with the text value of “null”, not a null value (e.g. headers.Add("If-None-Match", "null"), not headers.Add("If-None-Match", null)). (There are some special cases where you’ll deviate from this default value, but in all cases the header itself should always be sent.)

Continue reading →