Dataverse Web API Tip #9: Deltas (Tracking Changes)

,

Let’s say you want to find out about changes made to a table in Dataverse (Microsoft Dynamics, Microsoft Power Apps, etc.), perhaps because you’d like to synchronize those changes to an external system.

A primitive way of pulling this off is to query the table of interest, selecting only those records whose modifiedon value is greater than or equal to the last time you checked for changes:

GET {{webApiUrl}}contacts?$filter=modifiedon ge 2021-04-28T15:19:30Z
(include the "always include" headers)

This approach has several potential downsides, including:

  • Deletes aren’t included—If a record was removed from the specified table, the results to a “GET filtered on modifiedon” query won’t return anything indicating the deletion.
  • Missed modifications—Normally, modifiedon is automatically set to the moment the record was last changed in Dataverse; however, this behavior can be overridden. Since modifiedon isn’t an absolute indicator of whether or when changes occurred, filtering by it isn’t guaranteed to find all changes actually made in Dataverse after a point in time. (Think of a data import or an inbound data sync scenario: inserts/updates might be backdated so modifiedon matches when the record was last changed in the external system, which could be hours or days prior to when the data is brought into Dataverse.)

Dataverse’s Web API provides a better option for finding changes—an option built specifically for this purpose. When fetching entities from a table, if the table has change tracking enabled, you can ask Dataverse to provide you with a delta link. Later, you can use that link to fetch the delta of changes that occurred since your initial GET.

Continue reading

Dataverse Web API Tip #8: Those Mysterious ETags (Conditional Operations)

, ,

Ever notice the mysterious @odata.etag values that seems to be returned for every entity you GET? What is an ETag, and what can you do with it?

GET {{webApiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)?$select=firstname
(include the "always include" headers)

Response:
{
  "@odata.context": "{{webApiUrl}}$metadata#contacts(firstname)/$entity",
  "@odata.etag": "W/\"842270\"",
  "firstname": "Yvonne",
  "contactid": "b8d3f910-1896-eb11-b1ac-000d3a3ac80d"
}

Think of an ETag (short for “entity tag”) as a record’s version identifier. In the Dataverse world, if you’re fetching the same resource and get back the same ETag, you know that the resource is semantically equivalent to what you previously fetched.

So, yesterday if you did a GET {{webApiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d) and the data you received was accompanied with an ETag of W/"1377902", then today you fetch the same resource and get back the same ETag, you know that the resource hasn’t changed.

While there may be some usefulness in performing full entity fetches and then comparing ETags client-side, it’s the If-Match and If-None-Match headers that make ETags shine.

Conditional GET

Let’s go back to our initial example. Instead of doing a straight second fetch today then comparing ETags client-side to determine whether the entity changed, you could add an If-None-Match header to today’s request, setting it to the ETag received yesterday:

GET {{webapiurl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)
If-None-Match: W/"1377902"
(include the other "always include" headers)

On the server, if the entity’s ETag is different from the specified value, the entity will be returned. However, if the ETag you provided matches the entity’s current ETag, a 304 Not Modified response is returned. This signals you that you already have the latest on this entity. At the same time, it saves the cost of having the server send you another copy of that data.

By setting the If-None-Match header, you pushed ETag comparison to the server. This is an improvement over handling ETag comparison client-side—but things keep getting even better….

Conditional Update

Imagine you’re building an API client which fetches an entity’s data then displays it in a UI where it can be edited. What should happen if the entity is updated in Dataverse between when your client retrieves the entity’s details and when it saves changes back to Dataverse? Let’s say a user of your client pulls up an entity Friday before leaving for the weekend. On Monday, they come back in and edit the entity’s details without refreshing (so they’re working with the entity as it looked Friday). Over the weekend, someone else modified the same entity in Dataverse. What should happen when your client’s user clicks “save”: Should their change override the other edit? Should the save error because of the other edit?

In the data world, the second choice (erroring if someone else changed the entity) is known as optimistic concurrency control. In the Dataverse world, optimistic concurrency for an update is easy to implement thanks to the If-Match header.

To perform an update with optimistic concurrency, send the PATCH request with header If-Match set to the ETag the edit is based on (i.e. the ETag received when your API client performed its fetch). Dataverse will allow the update to succeed if the entity’s current ETag matches the specified value. Otherwise, it will error with a 412 Precondition Failed response.

PATCH {{webApiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)
If-Match: W/"1377902"
(include the other "always include" headers)
	
{ … JSON with column edits goes here … }

This ensures you that the edit will only be accepted if the entity wasn’t changed on the server during the intervening time. Nice!

Conditional Deletes

If-Match can be very important with DELETEs. How often do you want a deletion to succeed if someone edited the entity in the meantime?

DETELE {{webApiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)
If-Match: W/"1377902"
(include the other "always include" headers)

Just like update, the above succeeds if the entity’s current ETag matches the given value and errors with a 412 Precondition Failed otherwise. In other words, if a user of your API client views an entity’s details then clicks delete for the entity, that operation will only succeed if the details they viewed match the entity’s current details. If the entity has changed in the intervening time, the delete will be rejected.

Upsert Control

If-Match and If-None-Match also can be used to change PATCH’s behavior. By default, PATH upserts—it updates the specified entity if it exits and inserts a new one if it does not.

What does a request like the below do? It depends on whether the specified entity exists. If it does, it will be updated; if not, it will be created.

PATCH {{webapiurl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)
(include the "always include" headers)

Sometimes, this is not the desired behavior. Maybe you’re trying to update a couple of fields on an existing entity. If another user deleted the record in the intervening time, you don’t want to (re-)create a new one with just the two or three fields populated. Maybe you’d rather the PATCH fail so you can resend it setting all the fields. Maybe you’d rather it fail and not be retried out of respect for that fact that someone had a reason to delete the entity. In either case, you don’t want upsert behavior.

Using If-Match and If-None-Match, you can force PATCH to only update or only insert, instead of upserting. For more details, see tip #3 (Upsert).

The “Fine Print”

A few ETag-related details to be aware of:

  • Checking for equality is the only safe comparison to perform between ETags. Do not try greater than or less then comparisons. There’s no guarantee that an ETag from a more recent revision of a resource will be greater than the ETag for an older version—just that it will be different.
  • ETag inequality does not always indicate that a change occurred. The algorithm used to generate ETags can change with Dataverse upgrades. You may run into cases where a resource’s ETag changes even when the resource itself has not. So, while a different ETag for the same resource likely means the resource has been modified, this isn’t guaranteed.
  • ETags can affect caching (such as by a web browser). To prevent unexpected caching, every request sent to the Dataverse Web API should include an If-None-Match header, which should be set to the literal string “null” if no other value is more appropriate (see tip #1 (The “Always Include” Headers).
  • An ETag applies to a single entity. Query option $expand and header Prefer: odata.include-annotations can both result in a GET request returning data from multiple entities. When either of these is present, Dataverse won’t check the If-Match or If-None-Matched header against the entity’s ETag; instead, it will always return data (never a 304 Not Modified response).
  • ETags only work for entities where optimistic concurrency is enabled. Thankfully, by default, this option is enabled for all custom entities and some built-in ones, as well. However, this may impact you if you were hoping to use ETags with system-provided entities (like those that store internal configuration).
  • In the Dataverse world, it appears ETags are resource specific, not URI specific. I believe Dataverse will return the same ETag for an unmodified resource even if it is fetched using different URLs (e.g. if you fetch the same account using URLs accounts, accounts(guid-key) and contacts(guid-key)/my_account, the same ETag will be returned each time) even though some of the relevant specification text suggests that ETags are URI specific.

Note

Don’t forget to include the “always include” headers with every request sent to the Web API.

Reference

Perform conditional operations using the Web API

Dataverse Web API Tip #7: Creating Contained Entities

If you’ve used Microsoft’s Dataverse Web API for any length of time, you’re probably familiar with accessing related entities using navigation properties.

Imagine that in our hypothetical world an account can be related to one or more contacts. To fetch the contacts associated with a particular account, you might make a Web API call something like:

GET accounts(00000000-0000-0000-0000-000000000003)/my_account_contacts
(include the "always include" headers)

Nothing surprising so far—but did you know that you can also use navigation properties to create associated entities?

Say you wanted to create a contact that is associated with the above account. One option is to POST (or PATCH) directly to the contacts entity set (i.e. directly to the contact table’s URL). In the request’s JSON body, you’d define the contact to be created, including setting the appropriate relationship field (in this example, my_account) to tie it to the appropriate account:

Request: POST contacts
(include the "always include" headers)

Body: 
{
  "firstname": "Bob", 
  "lastname": "Smith", 
  "my_account@odata.bind": "accounts(00000000-0000-0000-0000-000000000003)"
}

Alternately, you could POST to the account’s navigation property—that is, POST to the exact same URL that was used to fetch the account’s contacts in the first example. In OData terminology, this is known as creating a contained entity.

Request: POST accounts(00000000-0000-0000-0000-000000000003)/my_account_contacts
(include the "always include" headers)

Body: 
{
  "firstname": "Bob", 
  "lastname": "Smith"
}

With this approach, there’s no need for the request’s JSON body to set the relationship from contact to account—no need to specify a my_parent value, as was done in the previous example—for the system will automatically infer and set this relationship.

Both of the above contact creation methods are equivalent in their effect: both create a new contact that is related to the same account.

Keep in mind that POSTing to a navigation property always creates a new entity. For collection-valued properties, the new entity will be added to the collection (like the last example, which resulted in the new contact being added to the collection of contacts associated with the account). However, in the case of a single-valued navigation property, there’s no collection to add the new entity to, as the property can point to only one entity. When a new entity is created because a single-valued property is POSTed to, what happens to the entity that the relationship previously pointed to? It’s disassociated but not deleted—it stays around, just with the previous relationship removed.

There you have it: an alternate way to create related entities. Which approach to use is a matter of preference. For collection-valued properties, I find POSTing to the navigation property (a.k.a. creating contained entities) attractive for its symmetry: the same URL is used to both fetch and create related entities.

Note

Don’t forget to include the “always include” headers with every request sent to the Web API.

Dataverse Web API Tip #6: Views (Separation of Concerns & DRYness)

,

Did you know that the views you and your users define in Microsoft Power Apps (including Microsoft Dynamics) can also be used from the Dataverse Web API?

Drop-down menu from Power Apps showing user and system views available for the current entity grid

The syntax is slightly different depending on whether the view is a system view (a.k.a. a public view; typically defined via a development tool such as make.powerapps.com or provided in a solution) or a user view (created by an end user—for example, in a model-drive app, by using the “Create view” menu option when viewing a grid page).

Continue reading

Dataverse Web API Tip #5: Direct Single-Property Access

If all you’re interested in is working with a single column value from a single table row, you can reference the property of interest directly by name (no need to append a query string with a $select in it, like ?$select=firstname):

GET {{webapiurl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname
(include the "always include" headers)

If the property of interest is not null, you’ll receive a JSON object in response along the lines of the following, with the requested value in value:

{
  "@odata.context": "{{webApiUrl}}$metadata#contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname",
  "value": "Yvonne"
}

This response can be streamlined. If all you want is a rendering of the specified property’s value instead of a formal JSON object, you can instruct the API to return just that appending /$value to the URL:

GET {{webapiUrl}}contacts(b8d3f910-1896-eb11-b1ac-000d3a3ac80d)/firstname/$value
(include the "always include" headers)

Yvonne
Continue reading

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