In part I we looked at change notifications in Graph API. In this part II we’ll cover how bookings (Bookable Resource Bookings) are synced from Dynamics 365 to Outlook when bookings are created, updated or deleted. To clarify terminology, bookings, Bookable Resource Bookings, and BRBs are used interchangeably in this blog post.
Before we move on, I want to emphasize that the entire concept (not just the cloud flow in this blog post) of syncing bookings between D365 and Outlook is a concept. It’s not a production ready solution. There are certain issues and technical limitations that need to be addressed before production use. Those limitations are listed at the end of this blog post.
Flow for syncing bookings from D365 to Outlook
The concept of syncing bookings from D365 to Outlook is based on a cloud flow. One of my personal goals was to see how far such a concept can be built using a no-code approach. Another reason for using cloud flows is the fact that I’m not a developer. The sync flow covered in this blog posts runs on both create and update of bookings in D365. In hindsight, having two different flows for create and update would have saved some time diagnosing issues in the overall concept, however a single flow is doable.
Let’s look at the trigger and initial actions of the cloud flow. For this example I’ve set the trigger to fire off when name, starttime, endtime, resource or bookingstatus columns change. This way bookings are updated if a BRB is assigned to another resource, its status changes or its dates/times change. The name column is more for the sake of making testing easier.
The row filter controls that the trigger fires off when a BRB is related to a Work Order. To save unnecessary runs in update scenarios, the trigger won’t fire off when a booking is modified by a service principal. Thanks to my colleague and fellow MVP Timo Pertilä for the idea! Updates to bookings from change notifications (Outlook to D365) are covered in part III. The overall logic won’t break even if updates made by a service principal are not filtered. The filter does help mitigate unnecessary flow runs, which means less API calls.
The environment variables scope contains environment variables that I wanted to test as I was building the flow. It also includes an app id and secret for the AAD app that is used for authentication in Graph API related actions. Best practice would be to use Azure Key Vault for sensitive information but for some reason I never got to that when building this flow. The amount of moving parts was already quite overwhelming in the overall concept.
The compose action for SdkMessage is important. As the cloud flow is used for both create and update scenarios, we need to be able to identify whether the trigger fires off from a create or an update. This information is stored in SdkMessage. The value isn’t available as dynamic content so an expression is needed to dig it out. It’s as simple as triggerOutputs()?['body']?['SdkMessage']. For more information, read the following post by MVP Roohi Shaikh.
A switch action is used to run the flow on a create or an update path, depending on the value in SdkMessage. The default path in the switch doesn’t contain any actions. Let’s cover the create path next for scenarios where bookings are created in D365.
Syncing new bookings
Before we dissect the flow’s create path, it’s important to understand why the BRB table needs to be extended with two custom columns. When a new booking is created in D365, a matching event resource is created in a user’s calendar. The id of that event needs to be stored so that it’s possible to match a Bookable Resource Booking in D365 to an event in Outlook, when updating or deleting bookings in D365 or events in Outlook. When a booking’s Bookable Resource in D365 changes, the event in the original resource’s Outlook needs to be deleted and a new event then created for the new Bookable Resource. As a pre-image is not available in cloud flows, the easiest solution is to store the original resource’s Azure AD Object ID in the BRB row.
Now it’s time to look at the create path in more detail. The first action gets us the Azure AD Object ID of the Bookable Resource in question. This is achieved with an expand query and it saves us from having to use an extra action in the flow. The query is UserId($select=systemuserid,azureactivedirectoryobjectid). The second action updates the Original Resource support column with the AAD Object ID. It’s specifically the AAD Object ID that’s needed so that we can identity the user in Azure AD. I have also fetched a user’s systemuserid primarily for D365 related test purposes.
The third action gets us the name of the Service Account related to the Work Order. This is later added to the subject of event that’s created in Outlook. The following expand query is used: msdyn_serviceaccount($select=name). The fourth action is used to get the Booking Status of the BRB if there is use for it when creating an event. The fifth action is used to get the user’s properties with the Azure AD Object ID, in case additional properties are needed.
The sixth action in the create path is a scope that includes actions for creating an event resource. The first action in the scope is used to compose a subject for an event and the second is a compose that holds the JSON used to create an event resource.
Let’s comb through the JSON as there are a few important “gotchas” in it. The first property in the JSON below is transasctionId. While this property is optional, it’s recommended that it’s used when using open extensions with event resources. There seems to be a known issue with a POST to event resource occasionally failing if open extensions are used. Using the transactionId property may mitigate the issue. Please see here for more information. As I’ve not found much information about the transactionId property, I’m setting it a random value using the following expression: rand(1000000,9999999).
Another important “gotcha” are open extensions. They provide a means of adding additional properties to the event resource. In this booking sync concept, the property that is added is the bookableresourcebookingid i.e. the GUID of the BRB record. This way it’s possible to match an event resource with a BRB row in D365. This will be covered in more detail in part III when we’ll look at the flow that processes change notifications. The JSON used in the flow can be found below image 4.
The action that creates a new event resource in Outlook is an HTTP POST against Graph API. Authentication is done with an Azure AD app. A user’s id comes from the Get user – create path action seen in image 3 above. By making a POST to /calendar/events, a new event resource is created in a user’s default calendar. It’s possible to use a different calendar than default but that will make identifying the correct calendars for different users more challenging.
The final action in the scope is only an example of getting the id of a created event resource from the HTTP POST’s output. The expression used is outputs('Create_event_-_update_path')?['body']?['id'].
JSON used in the cloud flow for HTTP POST
{
"transactionId": "@{rand(1000000,9999999)}",
"Subject": "@{outputs('Subject_for_event_-_create_path')}",
"Body": {
"ContentType": "HTML",
"Content": "This is an automatically created calendar event for Bookable Resource Bookings. Bookable Resource Booking's current status:."
},
"Start": {
"DateTime": "@{triggerOutputs()?['body/starttime']}",
"TimeZone": "FLE Standard Time"
},
"End": {
"DateTime": "@{triggerOutputs()?['body/endtime']}",
"TimeZone": "FLE Standard Time"
},
"Attendees": [
{
"EmailAddress": {
"Address": "",
"Name": ""
},
"Type": "Optional"
}
],
"extensions": [
{
"@odata.type": "microsoft.graph.openTypeExtension",
"extensionName": "Com.Anttipajunen.BookingSyncOpenExtension",
"bookingSyncGuid": "@{triggerOutputs()?['body/bookableresourcebookingid']}"
}
]
}
Syncing updated bookings
Updating existing booking is where the flow gets more complex. As a booking’s resource might change, the flow has to delete an event from an old resource’s Outlook and create a new event for a new resource. The initial actions for getting an Azure AD Object ID and the Booking Status are the same as in the create path. A condition validates that the custom column for an event id has a value. If it doesn’t the flow terminates as canceled.
The update path branches in two subpaths based on whether the BRB’s Bookable Resource has changed or not. Let’s look at the true path to see how the flow continues when a resource has not changed.
Update path when Bookable Resource hasn’t changed
If the flow runs in this path then its start time, end time or Booking Status have changed. If the Booking Status is Canceled, then the related event in Outlook is deleted and the flow terminates as succeeded. As the flow runs in the update path, the support column for event id has a value in D365. That’s how the event resource is found in the user’s Outlook.
If the booking’s Booking Status is something else than Canceled, start time and end time need to be compared with the related event’s start and end values in Outlook. The HTTP GET also includes an example of expanding the query to retrieve an open extension. An open extension’s specific properties can’t be directly queried so the option here is to query for the open extension as a whole. As we’re in the update path, the open extension includes the BRB’s GUID. Additional validation could be built in the flow based on it. Image 7 below shows the output of parse JSON with the open extension’s properties.
After the parse JSON, values for the event resource’s start and end properties are stored in compose actions. The values need to be formatted to match the format used in D365 with the following expression: formatDateTime(body('Parse_JSON_from_Get_event')?['start']?['dateTime'],'yyyy-MM-ddTHH:mm:ssZ'). A condition then evaluates whether or not the start and end values of the event in Outlook match Start Time and End Time values in D365. If the values match, no changes are required and the flow finishes successfully. If that is the case then the name of the booking has been changed for testing purposes. If the values don’t match, an HTTP PATCH action is used to update the event resource in Outlook with values from D365’s BRB row.
Update path when Bookable Resource has changed
If the Azure AD Object ID doesn’t match the value in the Original Resource support column, the BRB’s Bookable Resource has changed. In this case the original event needs to be deleted from the original resource’s Outlook and a new event created in the new resource’s Outlook. An HTTP DELETE is used to delete the event from the original resource’s Outlook. The Azure AD Object ID of the new Bookable Resource can be retrieved with a get records action against the Work Order in question – much like on the create side of the flow. The last part to cover is the scope action in the update side.
After the event from the old resource’s Outlook has been deleted, the flow creates a new event in the new resource’s Outlook. The JSON used is the same as in the create path with one exception: In this path I’ve not used a transasctionId property as that was one of the final additions I made to the create path for testing purposes. As I’m writing this blog post, I’m still in the process of identifying some issues so I’ve not added a transasctionId property to the flow’s update path. The Event id – update path compose is used to get the value of the event id property from the HTTP POST action. The final action in the update path updates the BRB row with the new event id and sets the new resource as an original resource, in case the booking’s resource is changed again.
In part III, we’ll look at how change notifications are processed and how bookings are synced from Outlook to D365. Before you implement a sync concept between D365 and Outlook, make sure you are aware of the know issues and limitations mentioned in the next chapter.
Known issues and limitations
The create and updated process has some known issues and limitations. Some of them are architectural while some are technical issues that need further diagnosis.
- An HTTP POST action creating a new event resource with an open extension occasionally fails, even though the event still gets created. As I’m writing this blog post, I haven’t heard of a permanent fix even when a transactionId property is used. As a workaround, consider making a POST without an open extension and then using a new POST to add an open extension to the created event resource. This will make error handling easier in a cloud flow.
- Power Automate will throttle in cases where there are high transaction volumes in created and updated bookings and service protection API limits are hit. As the Logic Apps engine attempts retries 10 times and then gives up, transactional consistency may be lost in high transaction volumes. High volumes also stress the Dateverse Async Service. As the Dataverse trigger in Power Automate doesn’t work in batch, consider implementing custom batching logic with pro-code tools such as plugins and Azure Functions to avoid hitting service protection limits.
- The event execution pipeline of the event framework can’t be used in Power Automate. Consider implementing a sync solution using pro-code tools like plugins and Azure Functions.
- Depending on how Bookable Resource Bookings are created, the result of a booking for a longer period of time may result in tens or even hundreds of BRB rows. The logic built in this concept isn’t suited for scenarios where high volumes of BRBs are created for a Bookable Resource. PSA and Project Operations are examples of 1st part applications where bookings are typically created for longer periods and the transaction scope for bookings is large.
- Bugs. I’m sure the concept has bugs I’ve not yet identified. Let me know if you find any!