Part 3: Tasks
The objective of this part is to make the list of work stored in the Excel file available in the open task list in Dime.Scheduler.
This is the third 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 job and action in the Power Platform connector
- Create and manage tasks in Dime.Scheduler
- Schedule tasks
Step 1: What constitutes a task
Now that we have our resources available for planning, let's give them some work. Manual appointments can be used, but are usually used for ad-hoc and unstructured interventions, such as tentative appointments awaiting confirmation from the customer. However, Dime.Scheduler comes into its own when used in conjunction with other systems. As mentioned in earlier parts of this series, a simple Excel sheet provides a list of the work that needs to be planned:
Much like a resource, a task can be anything. It is simply a piece of work that needs to be executed. In this tutorial, we are going to plan a number of events for the employees of the company Dunder Mifflin Inc. The plannable work in the Excel sheet as shown above couldn't be any simpler: it contains a list of events that need to be planned. The 'Finished' column indicates if the event has been planned yet. This is just a simple showcase though; it is ultimately up to you to define what a task is and you can make it as complex as you want.
Any system that can provide a list of work is eligible to connect to Dime.Scheduler. Moreover, any system that can make its data flow through the Dataverse can leverage our connector and add work to the list that needs to be scheduled.
The data model of a task in Dime.Scheduler is slightly different as it consists of two levels: jobs and tasks. A job is the high-level entity that can be best compared to a project: it is a collection of activities (or tasks) that need to be completed to reach a certain outcome. They bundle common information that apply to all underlying tasks, such as customer info, billing details, address, etc. For a complete overview of a job's properties, check out the API reference.
A task, on the other hand, is the actual unit of work that needs to be carried out. A task always belongs to a job while it uniquely identifies an item of work that will ultimately appear in the open task list, and subsequently, in the planning board when scheduled. For a complete overview of a task's properties, check out the API reference.
With this data model, there are many ways to organize your work. For simplicity's sake, we will adopt a 1:1 relationship between jobs and tasks. In other words, for each task we will create a new job. However, you may want to consider grouping tasks if you want to know how many hours your consultants have worked on a given project, or get a detailed report how much budget there is left on a fixed-budget project.
| Event field (Excel) | Field (Dime.Scheduler) | Type | Comment |
|---|---|---|---|
| Event + Number | JobNo | Job | The JobNo field serves as an external identifier that can be traced back to the job |
| Event + Number | TaskNo | Task | The TaskNo field serves as an external identifier that can be traced back to the task |
| Description | Description | Job/Task | |
| Region | Region | Job | |
| Responsible | Region | Job |
In this setup, concatenating Event + Number creates a unique identifier for this work item. For more complex projects with multiple tasks, the JobNo and TaskNo will almost always be different.
The next two steps are just about the same as the previous part. We will create a flow that is triggered manually, and invoke the right actions with the correct data mapping so we send it to Dime.Scheduler and start planning.
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.
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 'Events' sheet. With a few simple commands, 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 task in Dime.Scheduler. Inside the 'Apply to each' section, add the following actions (in that order):
- Add, update or remove a job
- Add, update or remove a task
In the output field of the loop action, select value.
Step 5: Inserting tasks
When you expand either action, you'll find an extensive list of fields that we can use to alter the behavior of Dime.Scheduler. To dig deeper, make sure to check out the Job API reference and Task 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 record with the external identifier (JobNo or TaskNo) that you provide in the action. 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 in step 1, 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 and source type fields, we've set static values that apply to all data that pass through this flow. For each job in the Excel sheet, we will know that its original data source is a SharePoint library and comes from the 'Event' sheet.
When creating a task, not only do you need to provide a unique external identifier in the TaskNo field, you also need to add a reference to a job using the job's unique external identifier in the JobNo field. Hence, the value of the Job No field in both actions need to be the same (and so do the Source App and Source Type fields, for that matter).
Not visible in the screenshot below are two additional fields:
- Duration in seconds is set to 14400. This value (4 hours) will be used to set the initial value of the appointment when it is dropped on the planning board.
- Subject is set to 'Event
Number:Description'. When dropped on to the planning board, this format will be applied to set the appointment's subject.
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 you navigate to the open task list, you will see the list of tasks:
If nothing appears, you may want to check the status of the step in the Power Platform, or the logs in Dime.Scheduler.
Congratulations, you've just added tasks to Dime.Scheduler and are ready to plan them! Take any task, look for an available resource, drag and drop it on the right date and time and you're done!
