Power Query M Primer (Part 21): Identifier Scope & Sections

• , , , ,

The same identifier name (think: variable name, field name, etc.) can be defined more than once in the same set of Power Query expressions. If you reference an identifier name that’s been defined in multiple places, which of those definitions will your reference point to?

In this post, let’s learn how M sorts this out. We’ll also explore sections—the usually hidden “frame” at the core of organizing the different expressions that make up a Power Query program.

Let’s get to it…and have fun while we’re at it!

Continue reading →

Migration Thoughts: Raw Data vs. Real Data

• , ,

A challenge when migrating data to a new system is that the legacy database may only contain a representation of the old system’s data. Arguably, the actual to-be-migrated data is what a user of the old system sees when using its user interface, reports or APIs. This data isn’t always exactly what is present in the database. Whether purposefully or inadvertently (think: bugs), the legacy system may apply transforms, filtering and such to the database’s raw data before displaying or outputting it. In order to create an accurate export for migration, these data manipulations need to be factored in.

From one migration, I remember an email address table along the lines of the following. This table’s design allows a customer to have multiple email addresses, including multiple primary email addresses (as the example below shows).

EmailId  CustomerId  EmailAddress       IsPrimary
-------  ----------  -----------------  ---------
1        15835       joe@example.com    1
2        15835       jsmith@work.com    1
3        15835       jsmith@home.com    0
4        321163      bob@elsewhere.com  1

If this system’s database were the exhaustive, authoritative source for its data, it would seem reasonable to conclude that all of the table’s email addresses should be included in the dataset to be migrated.

However, in the old system’s user interface, a customer could only have two email addresses: one primary and one secondary. Due to a bug, some email address edits resulted in new address rows being created instead of existing rows being updated.  The system, perhaps inadvertently, was smart enough to ignore these old rows, so their presence didn’t cause it problems. Even though these rows were physically present in the database, they weren’t a part of the system’s authoritative data because from the user’s perspective they did not exist; instead, they were data corruptions which needed to be ignored during the export extraction process.

Continue reading →

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 →