cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AdamBradbury198
Level: Powered On

Trying to use Flow to create a reminder of open actions for an approved change management plan.

I have built a management of change process using SharePoint and Microsoft Flow.  The process seems to be working fine.

At the last fortnight Management of Change team meeting, it was agreed that we needed to set up a reminder process to send an email to the assigned person that there is an action for them to complete.

However, we only want the actions that have been approved to be sent out, not the entire Task list (which will contain draft actions).

 

The way we have set up the Management of Change Process SharePoint site is that there is a Change Management Plan list - that contains the details of the change request, and records whether it is approved or not.

We also have a Change Management Tasks Task List, which contains the individual actions raised from change management plans.

The Task List actions are linked to the Change Management Plan by two common fields - the 'MOC Request #' and the 'Title'.

 

I have tried to build a Flow - but am having issues.

Firstly, what I tried was:

  • Trigger - Recurrence.
  • Get Items - CMPs that are approved  {with the Query Filter to return only Change Management Plans - Approval equals 'Yes'}.
  • Condition CMP is equal to Yes.
  • Get items - to pull the actions from the Task List {with the Query Filter to return only the Actions with the same title as the Change Management Plans from the first Get Items}.
  • Then the different conditions for whether there has been a due date set, if it is overdue, etc.

1.png2.png

When I test the flow, it Fails.  I get a Bad Request Error (400), with the message saying that the expression is not valid.

3.png4.png5.png

 

I have tried changing so it sorts by the 'MOC Request #', not the 'Title', but get a similar Bad Request Error (400), the expression is not valid.

6.png

7.png

I have then tried to stay away from using the Query, and just tried to limit the returns by the conditions, but somehow ended up with receiving each action may times, rather than just for the one change management plan.

8.png9.png

 

Instead of getting 16 actions for the 1 approved CMP, I received 800 actions (the entire 64 items on the action list sent over and over again for every CMP (not just the approved one).

 

I don't know how to get this to work.

 

How do I set this flow up so that it will check to see which items in the CMP custom list have the column 'Change Management Plan-approved' value as 'Yes', and then will go to the Task List and look at only the tasks which have either the corresponding 'MOC REquest #' or 'Title', to then send a reminder email to the 'Assigned To' for those specific tasks.

We don't want the other actions on the task list (for CMPs that haven't yet been approved) to have reminders sent out. 

 

I would appreciate any assistance.

Many thanks,

best regards,

Adam

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AdamBradbury198
Level: Powered On

Re: Trying to use Flow to create a reminder of open actions for an approved change management plan.

I have found a way to get this working.

 

I had to rearrange things a bit, and use conditions to narrow down the volume of actions being sent out.

It is still a 'Recurrence' to start the flow.

Then it is a 'Get Items' for the Task List {without any queries to limit the number of returned items}.

The another 'Get Items' for the custom List 'Change Management Plans', with a query to limit the returned items to only those that have been Approved}.

 I have then used a Condition to tie the two data sources together, that the 'MOC Request #' of the actions in the Task List is equal to the 'MOC Request #' of the approved Change Management Plan.

If Yes, then the next condition is checking that the Task Status Value is equal to Completed.

If No, the the next condition is to check if a Due Date has been set for the action

@empty(items('Apply_to_each_6')?['DueDate'])

 

If yes (no due date set), it sends a reminder email (which mentions in the Body of the email that the due date of the action has not yet been set).

 

If no, the next conditions are looking at whether the Due date has been passed, with a different email for when the action is overdue, versus when it the due date is still in the future, using:

formatDateTime('items('Apply_to_each_6')?['DueDate'], 'MM-dd') {is greater than or equal to} formatDateTime(utcNow()), 'MM-dd')

or

formatDateTime('items('Apply_to_each_6')?['DueDate'], 'MM-dd') {is less than} formatDateTime(utcNow()), 'MM-dd').

 

In testing the flow, it seems to be working fine.

It has narrowed down the 64 action items on the list, down to the 16 actions for which the Change Management Plan is approved, and is sending out reminder emails of the correct 8 open actions, three for items with no due date set, and 5 for overdue items. 

It isn't sending reminder emails for the 8 completed actions.

 

Thanks,

 

Adam

 

1.png2.png3.png4.png

View solution in original post

1 REPLY 1
AdamBradbury198
Level: Powered On

Re: Trying to use Flow to create a reminder of open actions for an approved change management plan.

I have found a way to get this working.

 

I had to rearrange things a bit, and use conditions to narrow down the volume of actions being sent out.

It is still a 'Recurrence' to start the flow.

Then it is a 'Get Items' for the Task List {without any queries to limit the number of returned items}.

The another 'Get Items' for the custom List 'Change Management Plans', with a query to limit the returned items to only those that have been Approved}.

 I have then used a Condition to tie the two data sources together, that the 'MOC Request #' of the actions in the Task List is equal to the 'MOC Request #' of the approved Change Management Plan.

If Yes, then the next condition is checking that the Task Status Value is equal to Completed.

If No, the the next condition is to check if a Due Date has been set for the action

@empty(items('Apply_to_each_6')?['DueDate'])

 

If yes (no due date set), it sends a reminder email (which mentions in the Body of the email that the due date of the action has not yet been set).

 

If no, the next conditions are looking at whether the Due date has been passed, with a different email for when the action is overdue, versus when it the due date is still in the future, using:

formatDateTime('items('Apply_to_each_6')?['DueDate'], 'MM-dd') {is greater than or equal to} formatDateTime(utcNow()), 'MM-dd')

or

formatDateTime('items('Apply_to_each_6')?['DueDate'], 'MM-dd') {is less than} formatDateTime(utcNow()), 'MM-dd').

 

In testing the flow, it seems to be working fine.

It has narrowed down the 64 action items on the list, down to the 16 actions for which the Change Management Plan is approved, and is sending out reminder emails of the correct 8 open actions, three for items with no due date set, and 5 for overdue items. 

It isn't sending reminder emails for the 8 completed actions.

 

Thanks,

 

Adam

 

1.png2.png3.png4.png

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,078)