Tag Archives: Dataverse Web API

Dataverse Web API Tip #12: Lookup Property Annotations

,

In Dataverse Web API responses, you’ll sometimes see properties named along the lines of _{navigation property name}_value (like _owerid_value). These are lookup properties: system-computed, read-only values that appear on the many side of a many-to-one relationship and hold the primary key of the related record.

Often, you won’t need to touch these lookups, thanks to navigation properties—which are the preferred way to work with relationships. However, the annotations on lookup properties can come in handy even in cases where the lookup values themselves aren’t interesting—particularly when a relationship can point to more than one table (i.e. can point to more than one type of entity, like column types customer and owner).

Suppose your orders table has a SoldTo column of type customer. This column type can hold a reference to either an account entity or a contact entity, allowing an order to be sold to either a company (account) or an individual (contact). Logically, SoldTo is one relationship; but technically, it has two navigation properties, one for the connection to accounts and the other to contacts. For a given order, how do you know what type of entity this relationship points to and so which of the two navigation properties to use?

Continue reading

Dataverse Web API Tip #11: Extra Error Details

,

By default, a Dataverse Web API error message contains two fields, code and message. However, some errors have extra details available just for the asking.

Below, an OData annotations preference request is used to ask for additional error details. The request’s syntax is simple: include odata.include-annotations="{comma-separated list of requested annotations}" in the Prefer header.

GET {{webApiUrl}}SomethingThatCausesAnError
Prefer: odata.include-annotations="*" 
(include the "always include" headers)

(Above, a wildcard is used to request all annotations. It may be best to only use a wildcard like this only temporarily, during development. Once the specific annotations of interest have been identified, change the request to be more specific. This avoids the cost of unnecessary extra data being assembled and sent across the wire.)

Response:

{
  "error": {
    "code": "0x80048d08",
    "message": "Example Error Message.",
    "@Microsoft.PowerApps.CDS.ErrorDetails.OperationStatus": "0", 
    "@Microsoft.PowerApps.CDS.ErrorDetails.SubErrorCode": "10001",
    "@Microsoft.PowerApps.CDS.HelpLink": "http://go.microsoft.com/fwlink/?LinkID=398563&error=Microsoft.Crm.CrmException%3a80048d08&client=platform",
    "@Microsoft.PowerApps.CDS.TraceText": "\r\n[Plugin: Plugin.Plugin]\r\n[03ded7cb-60a7-eb11-c2ac-010d4d3c26cd: My Plugin Step Name]\r\n\r\n\t\r\n\tGetting ready to do something.\r\n\t\r\n",,
    "@Microsoft.PowerApps.CDS.InnerError.Message": "Example Error Message."
  }
}

Be aware that error annotations are not necessarily intended for end-user or public consumption. The component raising the error may expect that these values will only be used for development and technical troubleshooting purposes and so may include content that is not end-user friendly or even that contains sensitive data. It is a good practice not to expose these extra error details publicly except when you are sure that the component raising the error will only return “safe to display” values.

Bonus Tip: If you’re writing a custom plugin, InvalidPluginExecutionException‘s first three arguments correspond to the OperationStatus, SubErrorCode and InnerError.Message annotations. Additionally, annotation TraceText will include any trace text written by the plugin, a fact that can be leveraged to include a full stack trace, if desired. Unfortunately, HelpLink is auto-set by Microsoft; I’m not aware of a way to set or override it.

Note

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

Reference/Further Reading

Parse errors from the response (in Compose HTTP requests and handle errors)

Dataverse Web API Tip #10: Formatted Values

,

Ever notice that the values a Dataverse Web API call returns aren’t always identical to what a Microsoft Power Apps/Dynamics user would see when viewing the same data in the UI?

For example:

  • In the UI, a Boolean is displayed using configurable “friendly” text like “Allow” or “Disallow”, “Yes” or “No”, “OK” or “Cancel”, etc.—but Web API responses always return Bool values as true or false.
  • Similarly, choices are returned as numeric codes by the Web API while the UI shows their corresponding textual value.
  • For lookup columns, the Web API returns the GUID of the related record while the UI shows its display name.
  • Dates and numbers are displayed nicely formatted in the UI but returned by the API in raw technical form.

Did you know that, thanks to an OData concept known as annotations, you can request that these display values (a.k.a. formatted values) accompany Web API results?

Continue reading

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