Workflow

Power Automate Sample – Custom Microsoft Teams Reminder for Meeting

Brian T. Jackett Microsoft Flow, Workflow

In this post I’ll walk through a sample workflow I created for sending out Microsoft Teams messages for a team sync meeting. This is part of my Spring 2022 Fix Hack Learn (FHL) project.

Requirements

  • Send individual reminder to teammates (35+) to capture “weekly accomplishments” for next sync meeting
  • Schedule to run weekly (Fridays)

Solution

The high-level architecture for the solution I prototyped is as follows:

  • Trigger
    • Recurrence – weekly on Fridays
  • Actions
    • Create variables for tracking date when flow is run and “next Tuesday”
    • Create copy of template file and rename to desired filename
    • Get list of team members
    • For each team member
      • Send Teams message with link to above file

Below is a sample of the Teams message that is sent.

Challenges

Dynamic links in Teams chat message

When using the HTML designer for the Teams “message”, you are only able to specify static links. In our scenario we need to include a dynamic link to the upcoming document for weekly accomplishments.

Through some investigation online (link) we found that this can be achieved using the Code View. Once in Code View, you can specify an HTML anchor element <a href=…> and include the “link to item” for the file in SharePoint.

Rename files in SharePoint Online document library

Power Automate has a SharePoint action for copying a file, but you have little control over naming the file (ex. replace, new copy with an incremented integer, etc.). Separately, there are currently no native actions for renaming a file.

Instead, I found a quick workaround (link) to copy the contents of an existing “template” file and then create a copy using the contents of the template file. In the process we can provide whichever name we desire.

Calculate date for “next Tuesday”

As part of the renaming of file, I wanted this flow to be able to dynamically calculate the date for the next Tuesday on the calendar. Aside from being an interesting calculation I was personally curious about, this would also allow sending out the reminder on any day of the week.

After much searching online, I found solutions for calculating this in Excel (example), but that made use of a different set of functions that are not available in Power Automate. In the end I decided not to spend too much time on this and instead hard coded the calculation to be Friday’s date + 4 days. See below for the formula.

formatDateTime(addDays(variables('currentDate'),4), 'yyyy-MM-dd')

Conclusion

Hopefully this example can give you inspiration if you need a similar solution for sending out customized reminders of a recurring event or meeting. I was surprised at how quickly I was able to prototype the first iteration of this (less than 1 hour) and then iterate on it with a few enhancements before the day was done. I’m a big fan of how easy Power Automate makes it to automate and simplify manual processes. If you have any suggested improvements, share them in the comments.

-Frog Out

Resources

Flow does not generate clickable links for dynamic paths
https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-does-not-generate-clickable-links-for-dynamic-paths/m-p/789336#M63152

Power Automate Rename File
https://www.enjoysharepoint.com/power-automate-rename-file/

How To Round Date To Previous Or Next Specific Weekday In Excel?
https://www.extendoffice.com/documents/excel/2663-excel-roud-date-to-nearest-sunday.html

Using Microsoft Flow to Start and Stop a Set of Azure VMs

Brian T. Jackett Azure, Microsoft Flow, Workflow

   In this blog post I’ll walk through creating a Microsoft Flow flow for starting (and another for stopping) a set of Azure Resource Manager (ARM) VMs.  Note that this is not my own original work.  I implemented this based on the work of someone else I found online but can no longer find the original owner’s reference.  If you do find this elsewhere please feel free to let me know in the comments.

Background

   While it is possible to start and stop Azure VMs from the newly released Azure mobile app, most time I need to start up a set (3-5) VMs at a time for a SharePoint farm / app dev environment / etc.  I was able to find a sample someone wrote in Microsoft Flow to trigger the start / stop from the Flow mobile app.  The flow calls Azure AD to get an access token using an Azure AD app that has permissions to start / stop VMs.  The access token is then passed into a series of REST calls to start up VMs in order (usually domain controller, database server, app server, web front end, etc.)  Finally the flow will send a mobile push notification letting me know that the VMs have started.

Word of caution

   This solution embeds the client ID and client secret (essentially user name and password) for the Azure AD app which has permissions to the Azure VM.   This could be a security risk and as such should be cautioned from doing this.  Treat this sample as a proof of capability for development purposes only.  I’m continuing to explore alternatives (ex. Managed Service Identity, Azure connector in Microsoft Flow) which would increase security for this solution.  If anyone has any suggestions please feel free to let me know in the comments.

Solution – Start Azure VMs

   I won’t go into detail on each and every step as some of these are self explanatory or a repeat of others (ex. 2nd and 3rd VM to be started.)  Before going into the flow to be created, ensure you have an Azure AD app registered with permissions on the desired VMs to be started / stopped.

Register Azure AD App

   Log into the “new” Azure portal (portal.azure.com) and go into the Azure AD screen.  First click on Properties to view the directory ID.  Make note of this for future use.

image

   Click App registrations and create a new app of type “Web app / API”.

image

   Make note of the application ID (also known as client ID).

image

   Go into the Required Permissions setting for the app.  Add a permission for the “Windows Azure Service Management API”.  Choose the permission “Access Azure Service Management as organization users” which is currently in preview.

   Create a key for the Azure AD app and write this down.  You will only get to see this key once and cannot retrieve it at a later time.  If you lose the key value you will need to create a new one.

Assign access control to resource group

   Now that the Azure AD App has been registered it will need access control to the resource group (or individual Azure VMs, more administration if this option) so that the app can start / stop the desired VMs.  I granted Virtual Machine Contributor role to the Azure AD App but more fine grained controls might be possible if security concerns are a factor.

image

Microsoft Flow sample

  1. Manually trigger a flow
  2. Get access token for Azure
  3. Parse JSON to extract access token
  4. Start VMs (in series)
  5. Push notification if successful

image

Manually trigger a flow

   This is self explanatory.  This will let you initiate the flow from Flow web portal or the Flow mobile app.

Get access token for Azure

   This step will use an HTTP POST action to the Azure AD directory where the Azure AD app is registered.  Ideally you should send a request to this URI using Postman or a similar REST endpoint testing tool to get a sample of the JSON response to be used in the following step.

image

  • Method: POST
  • Uri: https://login.microsoftonline.com/<directoryID from previous step>/oauth2/token
  • Headers
    • Content-Type: application/x-www-form-urlencoded
  • Body: resource=https://management.azure.com/&client_id=<client ID from previous step>&grant_type=client_credentials&client_secret=<client secret from previous step>

Example JSON response using Postman:

{
   “token_type”: “Bearer”,
   “expires_in”: “3599”,
   “ext_expires_in”: “0”,
   “expires_on”: “1508115492”,
   “not_before”: “1508111592”,
   “resource”: “https://management.azure.com/”,
   “access_token”: “<removed value>”
}

Parse JSON

   Either using the sample JSON response above or your own you can define the schema of the JSON to be parsed.  Specify the “Body” of the JSON response from the prior HTTP POST action.  The important element to parse out is “access_token”.

image

{

    “type”: “object”,

    “properties”: {

        …<other properties here>…,

        “access_token”: {

            “type”: “string”

        }

    }

}

Start VM REST call

   Add another HTTP POST action this time specifying the following configuration.

image

  • Method: POST
  • Uri: https://management.azure.com/subscriptions/<Azure subscription ID>/resourceGroups/<resource group name>/providers/Microsoft.Compute/virtualMachines/<Azure VM name>/start?api-version=2016-04-30-preview
  • Headers
    • Authorization: Bearer <insert the bearer token “input” from prior Parse JSON step>

   Note that I used an older version for the “api-version=” portion of query string (highlighted in green).  A newer version might also be available and compatible but I haven’t tested anything newer.

   Create as many additional HTTP POST actions that call off to additional VMs as needed.  I hand coded the Uri for each as Microsoft Flow didn’t yet support expressions and other dynamic variables when this solution was first created.  You may want to investigate those to reduce repeated syntax if possible.

Notify when VMs started

   Straight forward action with a simple notification to let me know when flow has completed.

image

Solution – Stop Azure VMs

   The steps for stopping a set of Azure VMs will be identical to the “start” flow except that stopping VMs can be done in parallel as the order is not as important.  In your own scenario the order may be important so consider that when creating your own solution.

  1. Manually trigger a flow
  2. Get Access Token for Azure
  3. Parse JSON to extract access token
  4. Stop VMs (in parallel)
  5. Push notification if successful

image

   The other important difference will be to call to “deallocate” (highlighted in red) the VM rather than “start” using the Azure Service Management API.  See example below for the HTTP POST to a VM.

Sample Execution

   As you can see from the below sample executions of both flows the start and stop of each VM can take some time (2-3 minutes) but is still an easier process of clicking one button rather than multiple clicks within the Azure Portal or mobile app.

image

image

Conclusion

   Hopefully this walkthrough will help others who are interested in automating Azure VMs to start and stop (or any other authenticated actions against Azure resources).  I’m hoping to try out additional options to remove the need to store client ID and secret within the flow.  For the time being try out this process and let me know if you have any issues.

      -Frog Out

SharePoint Designer 2010 Workflow Email Link To Item

Brian T. Jackett Workflow

<Update: 2015-06-15> My colleague Joe Rodgers pointed out that the below steps do not work in SharePoint 2013 style workflows.  As it turns out there was an issue with the link that was generated for the “Workflow Context: Current Item Url”.  This issue is addressed in the SharePoint  2013 June 2014 CU (requires Service Pack 1 or the March 2013 PU).  See below for more details.

</Update>

In this post I’ll walk you through the process of sending an email that contains a link to the current item from a SharePoint Designer 2010 workflow.  This is a process that has been published on many other forums and blogs, but many that I have seen are more complex than seems necessary.

 

Problem

A common request from SharePoint users is to get an email which contains a link to review/approve/edit the workflow item.  SharePoint list items contain an automatic property for Url Path, but unfortunately that Url is not properly formatted to retrieve the item if you include it directly on the message body.  I tried a few solutions suggested from other blogs or forums that took a substring of the Url Path property, concatenated the display form view Url, and mixed in some other strings.  While I was able to get this working in some scenarios I still had issues in general.

 

Solution

My solution involved adding a hyperlink to the message body.  This ended up being far easier than I had expected and fairly intuitive once I found the correct property to use.  Follow these steps to see what I did.

First add a “Send an Email” action to your workflow.  Edit the action to pull up the email configuration dialog.  Click the “Add hyperlink” button seen below.

EmailLink1

When prompted for the address of the link click the fx button to perform a lookup.  Choose Workflow Context from the “data source” dropdown.  Choose Current Item URL from the “field from source” dropdown.  Click OK.

EmailLink2

Your Edit Hyperlink dialog should now look something like this.

EmailLink3

The end result will be a hyperlink added to your email pointing to the current workflow item.  Note: this link points to the non-modal dialog display form (display form similar to what you had in 2007).

EmailLink4

 

SharePoint 2013 Considerations (update 2015-06-15)

If you attempt to use this process for SharePoint 2013 style workflows (based on Workflow Manager and not the out of the box SharePoint 2010 workflow engine) you will notice that the URL generated is incorrect (ex. “Lists/MyList/1_.00”).  It will look something like the following.

EmailLinkWFItem2013_3_thumb[1]

 

The solution to this is to install the SharePoint 2013 June 2014 CU (I tested with the Server package but it may be included in the Foundation package as well).  After you install this cumulative update you may notice that the URL generated only includes from the list level and down (ex. “LIsts/MyList/DispForm.aspx?ID=1).  As such you will need to combine this with the site URL.  See below for example of the new URL that is generated.

EmailLinkWFItem2013_4_thumb

 

Conclusion

In this post I walked you through the steps to create a SharePoint Designer 2010 workflow with an email that contains a link to the current item.  While there are many other options for accomplishing this out on the web I found this to be a more concise process and easy to understand.  Hopefully you found this helpful as well.  Feel free to leave any comments or feedback if you’ve found other ways that were helpful to you.

 

-Frog Out

Guest Post: Instantiate SharePoint Workflow On Item Deleted

Brian T. Jackett SharePoint, Visual Studio, Workflow

In this post, guest author Lucas Eduardo Silva will walk you through the steps of instantiating a workflow using an item event receiver from a custom list.  The ItemDeleting event will require approval via the workflow.

Foreword

As you may have read recently, I injured my right hand and have had it in a cast for the past 3 weeks.  Due to this I planned to reduce my blogging while my hand heals.  As luck would have it, I was actually approached by someone who asked if they could be a guest author on my blog.  I’ve never had a guest author, but considering my injury now seemed like as good a time as ever to try it out.

About the Guest Author

Lucas Eduardo Silva (email) works for CPM Braxis, a sibling company to my employer Sogeti in the CapGemini family.  Lucas and I exchanged emails a few times after one of my  recent posts and continued into various topics.  When I posted that I had injured my hand, Lucas mentioned that he had a post idea that he would like to publish and asked if it could be published on my blog.  The below content is the result of that collaboration.

The Problem

Lucas has a big problem.  He has a workflow that he wants to fire every time an item is deleted from a custom list. He has already created the association in the “item deleting event”, but needs to approve the deletion but the workflow is finishing first. Lucas put an onWorkflowItemChanged wait for the change of status approval, but it is not being hit.

The Solution

Note: This solution assumes you have the Visual Studio Extensions for Windows SharePoint Services (VSeWSS) installed to access the SharePoint project templates within VIsual Studio.

1 – Create a workflow that will be activated by ItemEventReceiver.

clip_image002

2 – Create the list by Visual Studio clicking in File -> New -> Project. Select SharePoint, then List Definition.

clip_image004

3 – Select the type of document to be created. List, Document Library, Wiki, Tasks, etc..

clip_image006

4 – Visual Studio creates the file ItemEventReceiver.cs with all possible events in a list.

clip_image008

5 – In the workflow project, open the workflow.xml and copy the ID.

6 – Uncomment the ItemDeleting and insert the following code by replacing the ID that you copied earlier.

//Cancel the Exclusion 

properties.Cancel = true;





//Activating Exclusion Workflow




SPWorkflowManager workflowManager = properties.ListItem.Web.Site.WorkflowManager;








SPWorkflowAssociation wfAssociation =




    properties.ListItem.ParentList.WorkflowAssociations.




    GetAssociationByBaseID(new Guid("37b5aea8-792a-4ded-be25-d283d9fe1f9d"));








workflowManager.StartWorkflow(properties.ListItem, wfAssociation, wfAssociation.AssociationData, true);








properties.Status = SPEventReceiverStatus.CancelNoError;








7 – properties.Cancel cancels the event being activated and executes the code that is inside the event. In the example, it cancels the deletion of the item to start the workflow that will be active as an association list with the workflow ID.


8 – Create and deploy the workflow and the list for SharePoint.

9 – Create a list through the model that was created.

10 – Enable the workflow in the list and Congratulations!


Every time you try to delete the item the workflow is activated.


TIP: If you really want to delete the item after the workflow is done you will have to delete the item by the workflow.








this.workflowProperties.Site.AllowUnsafeUpdates = true;




this.workflowProperties.Item.Delete();




this.workflowProperties.List.Update();








Conclusion


In this guest post Lucas took you through the steps of creating an item deletion approval workflow with an event receiver.  This was also the first time I’ve had a guest author on this blog.  Many thanks to Lucas for putting together this content and offering it.  I haven’t decided how I’d handle future guest authors, mostly because I don’t know if there are others who would want to submit content.  If you do have something that you would like to guest author on my blog feel free to drop me a line and we can discuss.  As a disclaimer, there are no guarantees that it will be published though.  For now enjoy Lucas’ post and look for my return to regular blogging soon.


-Frog Out


<Update 1> If you wish to contact Lucas you can reach him at luesilva1102@gmail.com </Update 1>

					

		
		
	




			
				


	

		

					
Adding Intellisense for SharePoint 2010 Custom Workflow .Actions File

								

								 Brian T. Jackett
															Visual Studio, Workflow					
												

					


				

			
    This post will show you how to add inteliisense support for custom workflow .actions files in SharePoint 2010.  During the course of some research for my writing I found out that the SharePoint 2010 SDK doesn’t include intellisense for the .actions files (at least as of the August 2010 release).  As the schema can be a bit difficult to remember off I searched the interwebs to see if anyone had implemented this already.  As luck would have it I was able to find this blog post that included an XSD file, but no instructions on how to install it.  That’s where I’ll help you fill in the gaps.


Steps


    

  1. Download schema file 
    2. 

  2. Rename schema file from .xml to .xsd 
    3. 

  3. Copy schema file to Visual Studio directory 
    4. 

  4. Update SharePoint catalog files 
    5. 



    First thing to do is download the wssactions.xml file from this location and rename it to wssactions.xsd.  Copy the file to your Visual Studio 2010 XML templates folder which is typically something like “C:Program Files (x86)Microsoft Visual Studio 10.0XMLSchemas”.  Next open the SharePoint catalog files (WssSchemaCatalog64.xml and WssSchemaCatalog.xml) and insert the below child element into the SchemaCatalog element for both.




<Association extension=”actions” schema=”%InstallRoot%/xml/schemas/wssactions.xsd” />




    Once you have completed this you’re all set to go.  No need to even re-launch Visual Studio 2010.  Create a new .xml file named <some_name>.actions.  You should be able to begin receiving intellisense for your workflow action definition file.


Conclusion


    In just a few short minutes you too can have intellisense for your SharePoint 2010 custom .actions files.  Special thanks to the author of the blog with the schema file download (I wasn’t able to find out the author’s name).  Also thanks to Dave Kehring for a template for what and where to add to the catalog files.  Hopefully this will help you get up and running SharePoint 2010 custom actions quicker and more effectively.  Enjoy!


 


      -Frog Out