Part 4: Processing appointments

The objective of this part is to reach out to the underlying application (the Excel file) and update the tasks when they are (re)scheduled. We'll show you how easy this is using the webhook connector and a HTTP-triggered Power Automate flow.

info

This is the fourth post in the guide on how to integrate Dime.Scheduler with Microsoft Excel through the Power Platform:

• Part 1: Setting up the connector
• Part 2: Resources
• Part 3: Tasks
• Part 4: Processing appointments

In this post, you will learn:

• How to post back to a Power Automate Flow
• Update the Excel file with appointment info

Step 1: What happens when you schedule an appointment

In contrast to the standard connectors that Dime Software provides, by default nothing will happen when you modify an appointment on the planning board in Dime.Scheduler. With your Power Platform solution, you'll have to build it yourself if you want to do something with the appointment information.

This is when the webhook connector comes into play. As soon as you do something to the planning, the webhook connector will make an HTTP POST request to your endpoint of choice with the entire appointment data graph. Inside the body of the request, you can navigate the graph and retrieve the info you're looking for, such as who has been assigned, when and where it is taking place, and more. For a complete overview of what's in the graph, check out the API reference.

Once more, the Power Platform is shining bright as it is possible to invoke a Power Automate flow by posting an HTTP request, which is what we'll do in this post. In this example, we are going to go back to the Excel file on SharePoint and update the 'Finished' status to true when a task has been scheduled:

Step 2: Create a flow

To keep this flow as simple as possible, we've made one small addition to the flow responsible for creating tasks. We've added the Number column to one of the free text fields:

Run this flow again to update your tasks. Add the Task FreeText 1 column to the open task column list to validate whether the values of the Number column have been added correctly to the free text property.

In the postback, we'll use this information so we can quickly update the right row in Excel. Let's do just that and create yet another Power Automate flow. This time, we will use a different trigger. Select "When a HTTP request is received" and paste the following JSON in the text area:

{
    "type": "object",
    "properties": {
        "Task": {
            "type": "object",
            "properties": {
                "FreeText1": {
                    "type": "string"
                }
            }
        }
    }
}

The Power Platform will parse this JSON and generate variables for you to use in subsequent steps.

info

The JSON that is actually posted is much, much larger. We snipped the JSON content to make our point. To see the entire data graph, use a network monitor or using testing tools such as webhook.site.

To go back to the original Excel file and update the row of the scheduled appointment, select the 'Update a row' action and provide the instructions to get back to the original Excel sheet.

The Key Column and Key Value are there to tell the Power Platform which column to use and which identifier to look for. In our case, the Number field in the Excel sheet may serve as a unique field. When an appointment is scheduled, it sends Dime.Scheduler's entire appointment data graph, which forces you to map the data once again, this time in the other direction (from Dime.Scheduler to the Power Platform). To make our own lives easier, we added the Number field as a separate value in the task's FreeText1 field earlier in this step. To update this record, we simply set the Finished column value to 'Yes'.

Save the flow and copy the value in the HTTP POST URL field of the HTTP trigger at the top of this flow.

Step 3: Set up the connector

When you schedule an appointment, Dime.Scheduler will try to figure out which back-office system needs to be notified. You may remember that we've specified a SourceApp value of 'SharePoint' in all of our flows. When such a task is scheduled, Dime.Scheduler will look for a connector that contains the same value.

In the connector setup page, ensure that there is a record that has the same value and contains a valid URI that points to the back-office system. In our scenario, we want to invoke the Power Automate flow we just created. Create a new record and specify the same source application as the jobs and tasks you just created, tick the checkbox, select 'Webhook' as the back-office system, and paste the URL of the Power Automate flow trigger:

tip

To test this connector, you can also consider using testing sites such as webhook.site which also shows you all the fields and values that Dime.Scheduler publishes.

Step 4: Test

Now that the Power Automate flow and Dime.Scheduler connector are set up, it's time to test! Pick any of the tasks, drop it on the planning board and have a look at the Power Automate flow history.

And sure enough, the flow has updated the correct row:

Congratulations, you have now set up a two-way message flow between Dime.Scheduler and the Power Platform. There are no limits as to what you can do. The appointment graph contains all the information of the scheduled work, as well as references to the original items, allowing you to create incredibly complex and powerful solutions.