Two-way sync of bookings between Dynamics 365 and Outlook, Part III – Sync from Outlook to D365

In part II we looked at syncing bookings from Dynamics 365 to Outlook. In this part we’ll look at syncing existing bookings from Outlook to Dynamics 365. Events originating from Outlook as new events are not synced so this blog post only covers syncing existing events. I might write a separate blog post about syncing events that Originate from Outlook, after relevance search in Power Automate is GA in June 2021. The sync between Outlook and D365 looks for Outlook events with open extensions and a bookingSyncGuid value. Both concepts were covered in part II.

Flow for syncing bookings from Outlook to D365

The flow that updates bookings from Outlook to D365 is the same flow, that validates new subscriptions to change notifications. The validation part was covered in part I. When a change notification is received, the Content-Type of the trigger will contain application/json; charset=utf-8. The flow then runs in the ProcessChangeNotifications path of the switch action, seen in images 1 and 2. The other path in the switch is for validating subscriptions. That process was covered in part I. The Default path in the switch doesn’t contain anything. Kudos to MVP Jan Vidar Elven for writing an excellent post that explains how to processing change notifications works. His content got me started with my change notification flows.

Processing change notifications

When a new change notification is received, it should be processed as early as possible with a 202 - Accepted response. The value for the clientState property should also be validated. In part I, we defined a value for clientState, when creating a subscription to change notifications. The trigger’s output will contain the value for clientState and a condition will validate whether or not the value matches what has been previously defined. For more information on processing change notifications, see this docs article. The JSON schema I’ve used in the parson JSON action in image 3 is:

{
    "type": "object",
    "properties": {
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "subscriptionId": {
                        "type": "string"
                    },
                    "clientState": {
                        "type": "string"
                    },
                    "changeType": {
                        "type": "string"
                    },
                    "resource": {
                        "type": "string"
                    },
                    "subscriptionExpirationDateTime": {
                        "type": "string"
                    },
                    "resourceData": {
                        "type": "object",
                        "properties": {
                            "@@odata.type": {
                                "type": "string"
                            },
                            "@@odata.id": {
                                "type": "string"
                            },
                            "id": {},
                            "activity": {
                                "type": "string"
                            },
                            "availability": {
                                "type": "string"
                            }
                        }
                    },
                    "tenantId": {
                        "type": "string"
                    }
                },
                "required": [
                    "subscriptionId",
                    "clientState",
                    "changeType",
                    "resource",
                    "subscriptionExpirationDateTime",
                    "resourceData",
                    "tenantId"
                ]
            }
        }
    }
}

Initial compose actions and changeType

When processing change notifications, we need to extract the following information from the trigger’s output:

The expressions used in the compose actions are as follows:
first(body('Parse_JSON_from_trigger')?['value'])?['changeType']
first(body('Parse_JSON_from_trigger')?['value'])?['resource']
first(split(last(split(outputs('Compose_resource'),'Users/')),'/Events'))
first(split(last(split(outputs('Compose_resource'),'Events/')),'"'))

The changeType property is something I ended up spending a bit of time figuring out. My thought was that I could direct the flow in different paths, depending on the value of changeType (created, updated or deleted). It turned out that when an event resource is created, multiple change notifications are received (up to four in total). An initial create leads to an update due to some internal updates that happen during a create.

I realized that I can’t properly use the different changeTypes, and I’d have to look at the ones that have a value of updated. While this means there’s no need to build the flow to branch in three different switch paths, it also means that a lot of additional validation needs to be built due to the flow processing every single update. As the event resource sends up to four change notifications, which the flow processes, it means that a single new appointment in Outlook will cause the flow to fire off several times. In larger organizations, this is a guaranteed API call burner and will also cause the flow to throttle quite fast.

Processing deleted events

The first step that we need to look at is whether or not a change notification is sent based on an event resource having been deleted in Outlook. If an event is deleted, then the corresponding booking in D365 should either be canceled or deleted. In this example I’ve preferred canceling bookings over deleting them – more on that in the section where we look at the child flow that cancels bookings in D365.

To get the details of the event that caused a change notification to be sent, we’ll use an HTTP GET action. We also need to get the bookingSyncGuid property’s value from the open extension that was created in part II. As open extensions don’t support OData filtering, we’ll use the approach of getting a known resource instance expanded with a matching extension. You can read more about that approach on docs. In this example, a GET request uses an open extension’s name (in this example Com.Anttipajunen.BookingSyncOpenExtension) but it’s also possible to use its fully qualified name (in this example microsoft.graph.openTypeExtension.Com.Anttipajunen.BookingSyncOpenExtension). The GET query used in this example is:
https://graph.microsoft.com/v1.0/users/idPropertyFromUserResourceGoesHere/events/outputOfComposeEventGoesHere?$expand=extensions($filter=id eq 'openExtensionNameGoesHere')

The condition that follows looks at the HTTP GET action’s output’s statusCode. If it’s 404, then the event has been deleted and the corresponding booking should be canceled in D365. If the statusCode code is something else, then the flow moves on. Let’s look at the child flow for canceling bookings in D365 next.

Child flow for canceling bookings in Dynamics 365

Before we dissect the child flow, I want to first highlight the response that we get from it. The response is seen in image 5 above. Depending on the child flow’s outcome, we’ll return a simple comment to the parent flow whether or not an event was canceled or deleted in D365. The parent flow is then terminated as succeeded.

The reason I’m using a child flow is to have an easier means of diagnosing issues. The parent flow in its entirety is fairly complex so adding all logic to a single flow would make it hared to diagnose failures and bugs. It’s also easier to build the additional logic in a child flow, especially as we’re jumping from building logic against the Graph API to building logic against Dynamics 365.

The child flow that cancels bookings in D365 takes an eventId and UPN as their input from the parent flow. While using UPN works, using Azure AD Object ID is a better idea as its value is constant. A user’s UPN may change. The initial actions get us the User (systemuser) row in D365, and the user’s Bookable Resource row.

In part II we covered that the id of the event resource needs to be present on the booking row, so that it’s possible to match bookings with events. We’re going to look at how that row gets its value later in this blog post, but before we do that, we need to understand its meaning. As the child flow covered in this chapter is about canceling bookings based on a deleted event, the custom column for event id on the Bookable Resource Booking table will have a value. The last list records action seen in image 6 below illustrates how we’re able to list the exact booking row that’s related to the deleted event. While the event doesn’t exist anymore, its event id is on the Bookable Resource Booking row.

After the initial actions, a condition is used to check whether or not a BRB row was found in D365. If a row isn’t found, the event in question has been created in Outlook. This means it’s out of this concept’s scope (events created in Outlook are not synced to D365) and the flow responds back to the parent flow that the event has originated from Outlook. The expression used to check whether or not the list records action returns values is:
empty(outputs('List_records_-_Bookable_Resource_Bookings')?['body/value'])

If a row was returned, the Yes side of the condition gets us the BRB row in question. There can only be a single returned row so the expression used to get the first row from the list records action is:
first(body('List_records_-_Bookable_Resource_Bookings')?['value'])?['bookableresourcebookingid']

An expand query is used to get the value of the BRB’s Booking Status (msdyn_fieldservicestatus). The following actions then get us the Bookable Resource and User rows for the resource in question. Next up is the flow’s first nested condition.

The child flow’s first nested condition checks that the User row found through Bookable Resource matches the User row related to the deleted event. If the values don’t match then the booking has been reassigned to another resource and it can’t be canceled or deleted in D365. If the User rows match, a second nested condition checks whether or not the booking has already been canceled. Let’s look at that condition in more detail.

If the booking is canceled, the second nested condition runs in the No path and clears the event id from the booking row. If the booking isn’t canceled, the flow will clear the event id from the booking row in the condition’s Yes path. It will then unrelated the booking status from the booking row (i.e clear the booking status lookup), look for the booking status row in D365 that is for the value on Canceled, and then update the booking row with a booking status on canceled. The final action responds back to the parent flow with comments.

As we can see from this child flow, the logic required to accurately cancel bookings in D365 based on deleted events in Outlook requires a fair number of conditions. There are several variables to take into consideration when syncing bookings between Outlook and D365 and so far we’ve only covered deleted Outlook events in this blog post. Next, we’ll get back to the parent flow and look at what happens if the received change notification is not about a deleted event but about an update to an event’s start/end dates.

Processing updated events

In this chapter, we’re back in the parent flow. If the received change notification is about an update to an Outlook event, that update needs to reflected in the related booking in D365. In image 5 we got our hands on the event that the received change notification is about. In the compose action in image 10 below, we’re getting the bookingSyncGuid from the open extension. This way we can now get our hands on the correct booking row in D365, as we progress in the flow.

A condition after the compose looks whether or not the event resource has an extensions property. If it doesn’t, the event has been created in Outlook and the flow terminates as canceled. If an extension property exists, the event has an open extension and it has originated from D365. In this case, the event in question is something we want to update and process further. The expression used in the condition is:
empty(body('Get_event_from_changeType_updated')?['extensions'])

The parse JSON action that follows the condition is redundant. I have originally added it in case additional logic would be built into the flow, but that has been a path I’ve not yet explored in more detail. The compose actions for event start and event end are relevant, though. The expressions used to extract the dates are:
body('Get_event_from_changeType_updated')?['start']?['dateTime']
body('Get_event_from_changeType_updated')?['end']?['dateTime']

Before we dissect the parent flow’s second child flow, let’s look at the last two actions in the parent flow. The second child flow responds to the parent with the GUID of the booking row that it has processed. The action is just informative so that it’s easy to see that the second child flow has run. The final action in the parent flow is used to terminate the flow as succeeded. Now let’s dissect the second child flow.

Child flow for updating bookings in Dynamics 365

As seen in image 10 above and in image 11 below, the inputs of the second child flow consist of resource id, eventId, bookingSyncGuid, start, and end. With this information, the child flow can process bookings in D365. Based on the bookingSyncGuid passed from the parent flow to the child flow, we can get our hands on the booking row that needs to be updated. The two compose actions are used to manipulate the format of the start and end dates to a format that D365 can accept. The expressions used are:
formatDateTime(triggerBody()?['text_6'],'yyyy-MM-ddTHH:mm:ssZ')
formatDateTime(triggerBody()?['text_7'],'yyyy-MM-ddTHH:mm:ssZ')

The condition that follows the compose actions is critical. It checks whether or not the start time and end time columns on the booking row match the Outlook event resource’s start and end dates. If the values don’t match then the start and/or end date of the Outlook event have been changed. The start time and end time values of the booking in D365 will be updated with values from the Outlook event. This is how changes in Outlook can be reflected in D365. The event’s id will also be updated to the Event id for booking sync column on the booking row. The child flow then responds to the parent flow with the GUID of the updated booking.

If the values evaluated in the condition match, the event’s start and end dates in Outlook haven’t been updated. The booking still needs to be updated with the event’s id so that the booking has the event’s id referenced on it in the Event id for booking sync column. This specific child flow and action in the Yes path of the condition updates a net new booking created in D365 with an event’s id. I.e. when a booking is created in D365, an event is created in Outlook. That event has an open extension that holds the booking’s GUID. This was covered in part II. The parent flow covered in this blog post then processes the change notification that results from the created event. The parent flow then passes the event’s id to the child flow covered in this chapter, and the child flow updated the booking in D365 with the event’s id.

In the final part IV we’ll look at how events are deleted in Outlook based on a deleted booking in D365. Canceled bookings were covered in part II, but deleted bookings require extra work, as this sync concept is based on Power Automate, and Power Automate has a hard time getting pre-images.

Disclaimer:
All my blog posts reflect my personal opinions and findings unless otherwise stated.