[Automation Kit - Feature]: Scheduler Proof of concept

[Grant-Archibald-MS]

Is your feature request related to a problem? Please describe.

The current process to schedule and manage Power Automate Desktop (PAD) execution is limited, which makes it difficult to automate certain tasks. For example, it's not easy to schedule PAD executions based on specific criteria or to manage multiple schedules in a centralized way.

Propose to develop a Scheduler Proof of Concept (PoC) that would allow users to schedule PAD executions in a more flexible and powerful way. The Scheduler PoC would leverage the existing PAD APIs for starting executions and the Power Automate platform for running flows on a schedule.

Describe the solution you'd like

• A user-friendly interface for creating and managing schedules, including support for recurring schedules, time zones, and other advanced options.
• Integration with the PAD APIs for starting executions, including the ability to pass parameters and to monitor the status of executions.
• Integration with the Power Automate platform for running flows on a schedule, including the ability to trigger other actions based on the results of PAD executions.

Describe alternatives you've considered

No response

Additional context?

Consider the following options:

1. Queue items as Work Queue items
2. On expiry of work queue item trigger the PAD via API
3. Methods to add new items to the work queue based on defined schedule
4. Providing the ability to change the queue time for a specific execution of a schedule
5. The ability to define dependencies between queue items

AB#1466

[Grant-Archibald-MS]

Consider the following approaches to trigger the Power Automate Desktop:

Triggering Power Automate Desktop

1. Manual testing - Get the access token
2. Call the dataverse unbound action from a Datavesrse unbound actions
   • https://learn.microsoft.com/en-us/power-automate/developer/desktop-flow-public-apis to call

Power Automate Cloud Flow Trigger Patterns to evaluate

Power Automate Cloud Flow Delay Until

1. Create Dataverse custom schedule table with Next Run Date Time
2. Dataverse Trigger on Update of custom schedule table
3. Use the Delay Until the date https://learn.microsoft.com/en-us/power-automate/dataverse/wait-conditions
4. After the Delay Until trigger desktop flow
5. Update CalculateNext to True
6. Add Dataverse Plugin to recalculate next Run when CalculateNext = True using https://www.nuget.org/packages/ncrontab/ to calculate the next run time

[!NOTE] Will need to consider approach when the Delay until needs to stopped or rescheduled to a new time

Power Automate Cloud flow

1. Use a timer trigger that runs every 5 mins
2. Evaluate records in Work Queue Item use delayuntil or expirydate
3. For each record ready to run execute the unbound action to trigger flow
4. Add new item to work queue to represent the next run time

[Grant-Archibald-MS]

Approach	Pros	Cons
Power Automate Cloud Flow Delay Until	Could have multiple cloud flows that listen to Dataverse table	If need to reschedule then need to cancel the scheduled flow and start a new one
	Do not need to poll the table for changes	How schedule multiple next runs?
	Use Chat GPT to use natural language to generate the cron expression
Power Automate Cloud flow Timer	Can schedule multiple items as each item is a work queue item row	Only run on time window of recurring time schedule
	Can update the delay until or expiry date with simple Dataverse PATCH	Single point of failure

For the delay until will need:

• Custom table Desktop Flow Schedule with Desktop Flow cron expressions which could have an effective start and end

  Columns	Type	Description
  Desktop Flow Id	GUID	The ID of the desktop flow
  Start Date Time	Date Time	Date when the cron expression is valid from
  End Date Time	Date Time	Optional nullable value of end time
  Cron Expression	Text	Valid Cron expression
  Cron Description	Text	Human readable version of the cron expression
  Prompt	Text	Chat CPT / Open AI prompt used to generate the cron expression
  Cloud Flow Id	Text	Identifier of the current cloud flow that is delay awaiting time. Could be used to cancel a waiting flow
  Recalculate	Y/N	If Y then the dataverse plugin should fire
  Next Run	Date Time	When should the scheduled flow next run

• Dateverse Plugin
  Bound to the Desktop Flow Schedule for Update and when Recalculate = "Y"
  Uses the cron expression to PATCH the NextRun field and Recalculate = "N"
• Power Automate Cloud Flow
  Bound to Desktop Flow Schedule
  Lookup Next Run field changed
  Get the current workflow using workflow()
  Call the Desktop Flow
  Set Recalculate = "Y" and Cloud Flow Id

[Grant-Archibald-MS]

User Experience. End state:

1. Select a Month, Week, Day

2. Simple case See a list of times that records in Desktop Flow Schedule that will run based on the crontab values. For example

   Desktop Flow	Schedule	Next Run	Cron Expression
   Test Flow	0 10 * * 1-5	2023-12-11 10:00AM	Run at 10:00 AM every weekday

   Display

   Select week of 11th Dec 2023

   Mon 11 10:00AM
   Tue 12 10:00AM
   Wed 13 10:00AM
   Thu 14 10:00AM
   Friday 15 10:00AM

3. Extended case

   Desktop Flow	Schedule	Next Run	Cron Expression	Priority	Start Date	End Date
   Test Flow	0 10 * * 1-5	2023-12-11 10:00AM	Run at 10:00 AM every weekday	1
   Test Flow (Wed)	0 11 * * 3		Run at 10:00 AM every weekday	2	12-13-2023	12-13-2023

   Display
   

   Select week of 11th Dec 2023

   Mon 11 10:00AM (Test Flow 1)
   Tue 12 10:00AM (Test Flow 1)
   Wed 13 11:00AM (Test Flow 2)
   Thu 14 10:00AM (Test Flow 1)
   Friday 15 10:00AM (Test Flow 1)

[Grant-Archibald-MS]

@praveen-pulagam-gl consider the following enhancements

User Experience

• Leverage a react scheduler generator as a PCF control https://www.npmjs.com/package/react-cron-generator

Dataverse Changes

• Rename Automate Cloud Flow table to Automation Schedule
• Consider a Automation Schedule Item to allow a 1:M relationship of multiple schedules related to an Automation Schedule

Column	Type	Description
Name	Text	Name of the schedule
Automation Schedule	GUID	Identifier of schedule
Type	??	Desktop Flow or Dataverse
Next Run	Date Time
Run Now	Boolean

• Add addition items to Automation Schedule Item

Column	Type	Description
Automation Schedule	GUID	Identifier of parent
Automation Type	Enumerated Type	Desktop Flow or Dataverse
Cron Expression	Text
Cron Expression Description	Text
Start Date	Date Time
End Date	Date Time
Priority	Int

Cloud Flow Changes

• Update the cloud flow. When Desktop Flow call Unbound Dataverse action to trigger desktop flow
• When Type Dataverse Set Run Now = True