Part 2: Resources
The objective of this part is to use a list of resources stored in an Excel file and make them available in Dime.Scheduler.
This is the second 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:
- Use the resources action in the Power Platform connector
- Create and manage resources in Dime.Scheduler
- Schedule resources in the planning board
Step 1: A first look at the data
This tutorial shows you how a simple Excel file can be transformed into a powerful data source for scheduling. The Excel file is stored in a SharePoint library, which makes it a doddle for the Power Platform to connect to. The Excel file contains a list of employees that we'd like to schedule in the 'Resources' sheet:
The work that needs to be done is listed in the 'Events' sheet:
Both sheets set the tone and demonstrate the two dimensions of Dime.Scheduler's data model. Everything revolves around the actor that is to carry out the work (i.e. the Resource) and the work itself that needs to be carried out (i.e. the Task). In this example, the employees are the resources and office events are the tasks.
This is an oversimplified example for good reason: so long as the Power Platform can tap into your data source of choice, whether that be a SharePoint list or service cases in Microsoft Dynamics CRM, it can be used to feed that information in Dime.Scheduler. Therefore, the Power Platform can be seen as a toolbox to connect the dots and link your application suite with Dime.Scheduler.
In this part, we will focus on the resources and how we can make them available in Dime.Scheduler. In the next part, we'll do the same but focus on the work that needs to be planned.
Step 2: What constitutes a resource
A resource is someone or something that can be scheduled, that's as detailed as we can define it. It is therefore an extremely open-ended and consequently flexible concept. You may want to plan consultants, meeting rooms, vehicles, all at the same time in the same planning tool. It is all up to you to define what constitutes a resource.
In this tutorial, our resources are employees of the company Dunder Mifflin Inc. There are many dimensions we could take into consideration when planning employees, like their seniority, expertise, languages, region, etc. However, the goal of this tutorial is to showcase the connectivity between the Power Platform and Dime.Scheduler, so we'll keep it simple and only keep track of their name, department, and job title, as shown in the Excel sheet above.
Dime.Scheduler's Resource entity contains many fields and references to other entities, as can clearly be seen in the API reference. Since we only have a limited number of fields in our Excel sheet, we will only need a small subset of the available fields in Dime.Scheduler:
| Employee field (Excel) | Resource field (Dime.Scheduler) | Comment |
|---|---|---|
| Number | ResourceNo | The ResourceNo field serves as an external text identifier that can be traced back to a single entity. |
| Name | DisplayName | |
| Type | Department | |
| Title | FreeText1 | There is no field for a job title, but we can use one of the FreeText fields. |
| N/A | SourceApp | We will use a fixed value to indicate that the original data source is the Excel file in the SharePoint library. This is particularly useful in situations where you manage resources from multiple back-office systems. |
| N/A | SourceType | We will use a fixed value to indicate that the original data source is the 'Resources' sheet in said Excel file. |
| N/A | ResourceType | Since we only will plan employees, we can use a static value. |
In many entities in Dime.Scheduler, you will find a field that sports the name of the entity, followed by 'No'. For example, ResourceNo, TaskNo and JobNo are all used to create a link between the records stored inside the database of the original system and that of Dime.Scheduler. This info is crucial when we want to write back information of the schedule after we've planned a resource or task.
Furthermore, if you intend to schedule resources and work items that originate from various data sources, the SourceApp and SourceType fields also have an important role to play. The SourceApp identifies the underlying back-office system (in this case the Excel file), while the SourceType represents the underlying data set (in this case the 'Resources' Excel sheet).
Step 3: Create a new flow
Let's create a new flow in Power Automate. For the sake of simplicity, we are going to create a flow that we'll trigger manually:
In the modal window, give the flow a name and select 'Manually trigger a flow':
This will show the Flow builder with the trigger already added to the canvas. Now the fun can really begin.
Step 4: Looping through each record in the Excel sheet
One of the many advantages of storing the Excel file on Office 365 is the ability of the Power Platform to not only access the file, but also to read and update rows and columns. In our example, we want to send over the entire list in the 'Resources' sheet. With one simple command, we can run an action against every item that can be found.
Add a step to the flow and look for the action 'List rows present in a table' in the Excel Online connector. This action is pretty self-explanatory: you need to give instructions where to find the Excel file and which table to look for:
Now that we've got the list, we need to iterate through each record that can be found in the table. The Power Platform has got us covered yet again with the 'Apply to each' action. For each record in the table, we want to add a resource in Dime.Scheduler. Inside the 'Apply to each' section, add the action 'Add, update or remove a resource' of the Dime.Scheduler connector:
In the output field of the loop action, select value.
Step 5: Inserting resources
When you expand the resource action, you'll find an extensive list of fields that can be used to alter the behavior of Dime.Scheduler. To dig deeper, make sure to check out the API reference.
For most actions in the Dime.Scheduler connector for the Power Platform, the first field is usually a Yes/No field that asks you whether to append the record or remove it. To append is to create or update a record; and is idempotent: if a record already exists with that external identifier, it will update that record. When set to false, Dime.Scheduler will attempt to remove the resource with the resource number that you provide in the ResourceNo field. For this tutorial, set append to true.
What makes the Power Platform so fantastic is that you can simply click your workflows together. Inside an action, Power Automate will tell you what fields are in scope. Using the mapping table above, we simply need to look for the right field in Dime.Scheduler and populate it with the columns of the Excel sheet:
For the source app, source type, and resource type fields, we added a static value that applies to all data that pass through this flow. For each resource in the Excel sheet, we'll know that its original data source is a SharePoint library and comes from the 'Resource' sheet. The resource type field can be used to distinguish between the different types of resources. The only thing you need to do is specify a name and Dime.Scheduler will create those while appending the resource. We've kept it simple and have used the default value that is available when you deploy Dime.Scheduler.
Save the flow, hit the Test button at the top-right corner of the screen, select 'Manually' and click the Test button at the bottom of the fly-in window.
To follow up on the status of this flow, proceed to the 'Run history' section of the flow and click on the last run:
When all went well, you will see the list of resources in the planning board:
If nothing appears, you may want to check the status of the step in the Power Platform, or the logs in Dime.Scheduler.
To modify the caption of the 'Free Text 1' field, go to Settings -> Localization, look for the FreeText1 field on the 'Resource' source table, select it, and add a caption in your language so the application knows to display something more meaningful such as 'Job title' instead of the field name 'FreeText1'.
Congratulations, you've just added resources to Dime.Scheduler and are ready to schedule them! You can already go ahead and create manual appointments on the planning board. It takes two to tango though, so in the next part we are going to focus on the list of work that needs to be planned.