@WonderLaura

This solution was done using Microsoft Flow.  The idea is that change requests are being created in a SharePoint site.  All of the people involved in getting assigned tasks to review the change request are EXTERNAL to the organization.  They are on the go, and I wanted to create a quick and easy way for them to respond to an email in order to complete their task, instead of having to go to SharePoint.

In trying to keep this blog post fairly simple, I’m not going to skip to the part where an email gets to someone when a task gets assigned.  The configuration of that email is where the fun starts

I’m using Flow, because I can control which email the flow is showing as FROM, and I can monitor the responses to that email.  ALSO, with Flow I can take the attachment from the change request (SharePoint item) and send it in the email, so yet again, those external users don’t have to go to SharePoint, because we have a LOT of them in this case, and you know how much fun it is trying to get non-technical people logged in as external users, right?  Also, this attachment isn’t a large document or anything, it’s mostly going to be just maybe a supporting screenshot, photo or something, explaining a change that needs to be made on a building site.

1. The flow trigger is when an item is created in the change request list, which is a custom list in SharePoint.
   
2. The first action is a SharePoint action called Get Attachments.  Just pick your site, and the same list you picked in the first action.  For the ID, pick the ID from the dynamic content, of the item that was just created.
   
3. Next, add the action called Get attachment content.  The ID is again, from the item that was created, and the File Identifier is from the ID of the file attachment.  Notice that as soon as you pick this second ID, an Apply to each loop will automatically be created around your new action.  This is because the workflow knows that there could be more than one attachment.  So, for each attachment, it’s going to get the content.
   
4. Next, add the action called Compose.  In the Inputs box, type the following, and insert the following two fields, the Attachment Content, and the DisplayName.
   {
   “ContentBytes”: ” “,
   “Name”: ” “
   }
   

   See, this is what it looks like inside of the loop that it automatically created:
   

5. The next action is to send an email to someone.  First, create a shared mailbox in Exchange, then use the action Send an email from a shared mailbox.
6. In the body of the email, I put the word ID and then inserted the ID of the item that got created (in the flow’s trigger at the very top) and typed ~.  THIS IS IMPORTANT.  Once they respond to the email, this is how I’ll know which change request it is for.   My task list (which is actually a custom list that I’m just *calling* a task list) has a lookup column to the change request list, called Related CR.
   You’re probably wondering why I didn’t create the tasks from within Flow, and just use the ID of the task that gets created.  Well, these are external users, and Flow won’t currently let me put an external user in the Assigned To (person) field in the task list.  So the task is getting created from a SPD workflow
7. Expand the advanced options.  Next to the Attachments box, click the little “T” icon, which collapses it down into one field instead of two.  In that single Attachments box, insert that Compose variable that was created at step 4.
   For Importance, choose Normal, for Is HTML, choose Yes.
   
8. That’s it for the first workflow.  Name it and save it.  Now, when a new change request gets created, the person gets emailed the list item attachment!
9. The next step is to create another Flow, to watch for incoming emails to that shared mailbox.  Create a new flow, and the trigger is When a new email arrives in a shared mailbox.
   
10. Then, since I don’t want to mess with a big blob of HTML in their email response, I use the action called HTML to Text.  Insert the email body in there.
    
11. Here’s the really ‘fun’ part.  I want to extract the ID from the body of their response email.  You could use this for other things, other specific text you may need from the email.  Add the action called Compose.  In the little Dynamic content flyout on the right, click Expression. Paste this in and click Update.
    Basically, it’s finding that ID: that I put in the email to the person, and it’s adding 3 to it, to pinpoint the exact spot where the ID number is.  It’s also finding the last place that there is a ~ character.  Then it’s subtracting the ID’s location from the ~ location.  it’s spitting out a substring of just the ID itself.

    substring(body(‘Html_to_text’),add(indexOf(body(‘Html_to_text’),’ID:‘),3),sub(lastIndexOf(body(‘Html_to_text’),’~‘),add(indexOf(body(‘Html_to_text’),’ID:‘),3)))
    
    

12. Then the next goal is to go find the specific task that needs to be marked as complete, and mark it as complete via workflow.  These people are just basically saying that they looked at something and that’s it.  Just reviewing, not approve versus reject.  Add an action called Get items from a SharePoint list.  You can rename actions, too, if you’d like.  You have to do it as you create them, though.  I renamed mine to call it Get Tasks.
13. Next, I’ve got a couple of fields that I need to filter by, but they’re complex fields.  A person field and a lookup field.  These types of columns are more complicated to deal with in Flow and PowerApps.  I’d like to narrow down  my list of tasks, but the ODATA query to a person or lookup field is, too complicated / I have spent hours on this flow and wasn’t able to figure that out.  Anyway, I’m at least going to narrow it down to only return tasks that aren’t completed, instead of the whole list.  Then, I have another way that I’m going to narrow it down to what I need. Put your site in there, and pick your task list (mine is not actually a task list, it’s a custom list and I named it ‘CR Tasks’).
    Filter Query: TaskStatus ne ‘Completed’
    Order By: Modified asc
    
14. Next, the Flow is going to iterate through the tasks that it finds, and for each task, I’ll have it check for a couple of conditions.  This isn’t as efficient as putting all of the filters in the filter query above, but it’s another way of going about it.  Add a condition.
15. In the first box, choose the AssignedTo email field from the task list.  IS EQUAL TO.  Then, choose the email address of the incoming email from this flow’s trigger.  Once you do that, an “Apply to each” loop will automatically wrap around your condition.  We’ll get back to this in a minute.
16. Under If YES, add an action called Update an item in a SharePoint list.
17. Note that this action has some weirdness to it, because even though I might not be changing the values of my required fields, like Title, it shows it as required and makes me put a value in it anyway.  So, I just put the value of itself.  For Input from Reviewer, this is just a text field in my list, and I want to put the verbiage from their email reply in there. So I do another expression.

    substring(body(‘Html_to_text’),0,indexOf(body(‘Html_to_text’),’From:’))
    

18. This substring looks at the person’s email response, and gets everything just in their response, leaving out the initial email to them.  Notice after you run a flow (I did many, many testing runs of this one), you can see what the output was when the workflow ran, when you look at the logs.  This is an example of the ‘Html_to_text’ thing.   See, the substring in step 17 above, is just looking at this and grabbing everything above the “From”, and putting it in the Input from Reviewer box in the task.    Also, note that I set the task status value to Completed.
    
19. One more thing.  We don’t just want to get the not completed tasks by this specific person, we want to get the task specific to this particular change request also.  Go back to your condition from step 15.  Click the button to edit it in advanced mode.  In order to do a condition that has an AND statement in it, we have to use this special syntax.  Basically, the condition is that the Related CR is equal to that Compose variable from step 11, and the Assigned To Email is equal to the FROM email address (from that email in this workflow’s trigger).

    @and(equals(items(‘Apply_to_each’)?[‘Related_x0020_CR_x003a_ID’]?[‘Value’], outputs(‘Compose’)),equals(items(‘Apply_to_each’)?[‘AssignedTo’]?[‘Email’], triggerBody()?[‘From’]))

    

How in the hell did I figure *that* out?  Well, I created each condition separately, and peeked at the advanced mode of each one, and copied that text.

It gave me this:  @equals(items(‘Apply_to_each’)?[‘Related_x0020_CR_x003a_ID’]?[‘Value’], outputs(‘Compose’))
and this: @equals(items(‘Apply_to_each’)?[‘AssignedTo’]?[‘Email’], triggerBody()?[‘From’])

then I put the AND on it, it goes in the front.

Done.  Save the flow.

Now you can probably see why I left the part off, about just creating the tasks.  The rest of this was quite enough for one post.  Again, the tasks are just getting created using a SharePoint Designer workflow, which runs when a new change request gets created, and I’m *not* using any task actions, just the Create List Item action.  Refer to step 6 for the explanation as to why it was done this way.  Again, this project definitely has some challenging requirements, but I feel like that whole thing with the external users is becoming more and more prevalent.  I mean, if these were all internal users, this whole project would have definitely been a good candidate for PowerApps.  (PowerApps can’t be used with external users)

You may also be wondering why bother with assigning tasks, if you’re never sending the users to go to them in SharePoint.  It’s for accountability.  The project manager needs to be able to quickly look at the list of tasks to see who has not completed theirs, so he can follow up or whatever he needs to do.

Reference: check out the session: Deep dive: Advanced workflow automation with Microsoft Flow by Stephen Siciliano