Power Automate SharePoint

Reminder — SharePoint 2010 Workflow Retirement for SharePoint Online

Just posting a quick little reminder for anyone who may still have SharePoint 2010 Workflows kicking around.

On November 1, 2020 SharePoint 2010 workflows will be retired. It is a good time to start looking into Power Automate to replace those workflows.

Take a look at using the SharePoint Modernization Scanner tool to scan your tenants for legacy workflow usage.

Communications Development Office 365 SharePoint

Customizing Modern SharePoint: Don’t Forget About the Low Coders!

The move towards the SPFx tool chain is good news for web development standards, but can be a headache for casual users expected to learn, implement, and handle the supporting business activities in its’ use. The client malaise – and frustration – is real.

For better or for worse, Microsoft has maligned the casual front-end developer. The advisor on the communications team, the marketing coordinator, the social media manager, the brand specialist, the technical analyst, and the summer intern doubling as an all-knowing web guru. While not always full-time developers or with a rich coding background, these “low coders” are among the target users of Office 365 – a marketing push to the casual resources that they, too, can user SharePoint, Forms, PowerApps, Flow, and other tools in the platform to build purpose-driven and valuable solutions. As a resource who helps clients adopt and use the power of Office 365, I try and identify the capabilities of client resources ability to deliver “low code” solutions in their workplaces, and it can vary dramatically based on budget, in-house skills, company size, and willingness to change.

Often, it is not just developers and trained User Experience (UX) designers that customize the branding of websites, intranets, portals and team sites. It is common in organizations large and small for resources to wear many hats, including web developer when convenient. And with that comes varying levels of developer skill sets, each approaching their code, UX and branding needs with various levels of expertise in CSS, Sass, Less, Bootstrap and other web frameworks. These folks have had various levels of development education, from formal post-secondary training to self-taught on the job because of circumstance.

Whether their skills in code are novice or advanced, one thing is for certain – the new modern sites on SharePoint require much more technical prowess than classic sites if they must be customized. This is simply because the technology has moved away from the master pages and page layouts that ruled the roost of SharePoint yester-years. SharePoint – and more specifically the SharePoint Framework (SPFx), has moved to an open-source tool chain that is more in line with modern web development standards.

And that’s all well and good – for developers.

But if I am a marketing or I.T. resource where updating styles and functions is one of my many daily tasks, it’s not an easy ask to suddenly and quickly switch to knowing the tool chain of SPFx and expect to continue development with client-side web parts and extensions. This is especially true in an area where Hub Sites are rising the ranks to tenant kings and all other sites are the spokes, where site proliferation – and maintaining corporate branding and functionality – doesn’t necessarily slow down.

Microsoft has done this before. In the last two years, they have advertised both Flow and PowerApps as “no code” solutions. But this just simply isn’t true (and have since moved to “low code” solutions that still require considerable training to be familiar with). To have any meaningful custom solution for workflows or apps for your organization, you really have to know Workflow Definition Language, Excel logic, JSON, XML, and other supporting languages. Without knowing at least some of these, it’s an uphill learning curve to become familiar with these tools and to drive true value for your organization, and make the most out of your subscription to Office 365.

Why Would I Need to Edit Modern Sites Anyways?

Modern SharePoint sites, both team sites and Communication Sites – are not really intended to be branded. Microsoft encourages the customization of their SharePoint environment by following open-source standards, but without the proper change management and training strategies available (for free), they’re assuming client organizations can sort themselves out and/or should have an advanced resource on site to manage all of this. Having to run – and know how to use – node.js, npm, Yeoman, and the rest of the SPFx tool chain – is a stacked request for regular people who need to do casual branding and functional activities, especially with people such as small business owners who need to wear many hats and have precious little available time. Customizations have been a staple part of SharePoint projects and consulting over the course of my career, and they don’t seem to be subsiding, nor can we ignore specific functionality that match the unique requirements that change from client to client.

Microsoft is doing great things putting new web parts into SharePoint Online and allowing strong APIs to grab data and feed it into intranets and team sites, but it doesn’t account for everything that a client needs. Even the absence of a Script Editor web part has caused may inconveniences and frustration among clients, something they have been able to quickly build, test, and release in previous versions of SharePoint.

Real-World Frustrations

Recently I worked with a client where I performed consulting services that included custom branding, business analysis, migrations, workflow design, etc. The client had a technical resource on their Communications team, a resource that was technically skilled but had more pressing duties as a communicator committed to other projects. While familiar with some web development, he was not necessarily an experienced developer, but rather a go-to for the rest of the Communications team to implement technical solutions when needed.

One of the requests was to inject Google Analytics onto a new Communications site. Previously, they had done this on classic sites using SharePoint Designer, and could easily handle this request for other business units so that they could track users click paths and engagement.

With the introduction of modern sites and SPFx, the technical resource now needed to implement an entire business process with the Communications and I.T. services team just to inject the necessary code they were easily able to do before. They just did not have the cycles to perform this, and would likely have to employ consultants and take time away from other projects just to handle this request. Because of these constraints, and with little time to spare for training (and being understaffed to begin with), it just simply wasn’t a priority any more to put it on sites. Not because they didn’t want it or wouldn’t use the metrics, but because it was too much of a hassle to install it and adopt a new tool chain. The client was frustrated, including not being able to inject code or custom solutions without outside help and with little time to put together a new business process and governance strategy to manage such activities.

One could blame the client for not “changing with the times” or having the right resources in place, but this story is not unlike many companies of various sizes with limited time and budget. Not every client has the cycles to devote to technical training, and the “low code” advertising provides a false sense of security when the technology changes and comes with business workflow design, additional training, change management, and other related activities. The client felt as if they had been “blindsided” amid all of the changes that are constantly pushed to SharePoint and Office 365.

Don’t Forget the Low Coders

Microsoft is doing the right thing by moving SharePoint customizations to the world of open-source tools to manage client-side projects. It is in line with modern web development standards, and keeps the focus at the client-side level. But they cannot lose sight the folks they are sending into the trenches to perform the down-and-dirty of SharePoint development. It’s just as much the UX designer, brander, communicator, social media coordinator, marketing manager, and small business owner as much as it is a trained and knowledgeable developer. And SharePoint is not at a level of sophistication (yet) where all client requirements no longer need a customized solution. There just isn’t a Web Part for all of that. And for all of the great things they pack into SharePoint and Office 365, it would be great if they could wrap up the tool chain into one, all-encompassing and easy-to-use code deployment tool that makes it easy for the “low coders” to jump in and out from.

I am definitely interested in hearing your opinion on this matter. Feel free to leave comments or reach out on Twitter to continue the conversation.

Flow Office 365 SharePoint Workflows

Common Filters for Microsoft Flow: A Reference Chart for Users of all Kinds

Recently I have been helping clients migrate workflows from on-premise SharePoint sites to Office 365. Quite often I find myself re-building these workflows in Microsoft Flow, the successor in many ways to SharePoint Designer. The tool itself has been a great solution for many types of workflows, particularly for re-creating Site workflows that need to query multiple items in a list and perform actions on them

In order to do this, you use the Get Files (Properties Only) or Get Items actions quite frequently, which means you have to instruct Flow what to grab from the list.

The last thing you want to do is retrieve every item in a SharePoint list or library, especially if those lists and libraries are quite large and you only need to grab a few items with specific metadata conditions. In order to do this, you will need to use ODATA filters in your query (not the most friendly way for business users but something we’ll just have to live with for now).

Below is a chart of ODATA commands that you can use to help build your queries with the Get Items or Get Files actions.

Basic Operators

EqualseqDepartment eq ‘Human Resources’
Not EqualsneDepartment ne ‘Accounting’
Less ThanltItemStock lt ‘500’
Greater ThangtReserveFund gt ‘10000’
Less Than or Equal Tole Due_x00200_Date le ‘2019-03-01’
Greater Than or Equal TogeWidgetCount ge ’50’


And (ensuring both values have to be true)andDepartment eq ‘Human Resources’ and
Country ne ‘Canada’
Or (either value could be true)orDirector/EMail eq ‘’ or
Accountant/Email eq ‘’

Note – you can use multiple and and or conditions in the same query.

String Adjustments

The below filters can be used to find and/or adjust values (strings) in a column. While not a complete list, these are commonly-used operators I have used in the past.

Starts Withstartswith(column,’string’)

Example: startswith(Department,’A’)
– this produces a result that returns any item where the Department field has a value that starts with “A”.

Ends Withstartswith(column,’string’)

Example: endswith(Accountant/DislayName,’Jones’)
– this produces a result that returns any item where the Display Name value of a People Picker field named “Accountant” has a value that ends with “Jones”.

Contains (also known as substring) substringof(column,’string’)

Example: substringof(ProjectPhase,’Test’)
– this produces a result of getting any items where “Test” is somewhere in the ProjectPhase field of the item.

Note: You can also be more specific in where the string lies using targeted positions. See section of the ODATA URL Conventions to find out more about index positions.

Index Of indexof(column,’string’) operator ‘value’

Example: indexof(Title, ‘Red’) eq 0
– this produces a result to grab items in the Title field that contain the value “red” and look at the first position in the string (the beginning). If you set the eq to 1, it will find “ed” from the “Red” value.

Concatenateconcat(concat(Column1,’, ‘), Column2)

Example: concat(concat(LastName,’,’), FirstName)
– this produces “Chomik, Andrew”.

Note: You can use any character as you see fit to concatenate with (I happen to use the comma in the above example).

Dates and Time

Secondsecond(‘Column’) eq valueday(‘Created’) eq 12
Minuteminute(‘Column’) eq value minute(‘Modified’) eq 31
Hourhour(‘Column’) eq value hour(‘ApprovedDate’) eq 3
Dayday(‘Column’) eq value day(‘ReviewDate’) eq 12
Monthmonth(‘Column’) eq value month(‘SubmissionDate’) eq 8
Yearyear(‘Column’) eq value year(‘Birthday’) eq 2019

Note: Numeric (integer) values do not require any delimiting punctuation (e.g. the single quotations). And you must use the military time format.

Additionally, you can use the full string provided in a Date/Time column (depending on the format of your Date and Time column in SharePoint). You can just use the format provided in the basic operators section (e.g. DateField eq ‘2018-03-02’).


Sometimes you may need to do a bit of math on a SharePoint column. The Math operators can help you find specific values. Combine the following operators with your required result using the basic operators.

AdditionaddWidgetCount add 10 eq 50
SubtractionsubWidgetCount sub 15 lt 30
MultiplicationmulPrice mul 2 gt 5.50
DivisiondivWidgetCount div 4 gt 5
Modulo (use this to specific the remainder amounts you may want). The example shows when you want to divide a column value by 5 and have no remainder left over.modWidgetCount mod 5 eq 0

Other ODATA Filter Things to Know when using Flow

  • Flow will only recognize the Internal column names to be used in the ODATA Filters. You might have a Column with the Display Name called “Department” but the Internal name may have been created as “Company Department”. You will have to use the latter value, and ensure that your reference is encoded correctly when using it because of the space. Best thing to do is go to the SharePoint list settings, click on the column you want to use, and grab the Internal Name from the end of the URL.
  • Use the “null” value if you want to operate with items have an empty field. For example, you could input “Department eq null”. This is retrievable command from the Expressions area in Dynamic Content.
  • If you have already fetched items, you can use Dynamic Content in between the single quotes for your filter values (see picture below)
  • When returning some values, there may be more than one value you need in the item. For example, filtering on a People column will mean you need to target a particular piece of the profile. In this case, use the “column/value”. If you want the email of an Accountant, you would create the filter “Accountant/EMail eq ‘”.

In the future, Ion Works will show you how to use common expressions in your Get Items actions that can be useful to get the data through the workflow and give you targeted results for your data (which also applies for usage across Flow actions, including Compose, Variables, Conditions, etc.)

Stay tuned!

Flow Office 365 SharePoint Workflows

The User Experience of Microsoft Flow with SharePoint: A Bumpy Ride on a Winter Road

If you have ever been frustrated using Microsoft Flow to perform functions inside SharePoint, you’re not alone.The User Experience can be better, and here’s where you’ll notice it.

Living in a northern climate at a higher altitude in a northern country, there are some harsh daily realities we tend to deal with frequently – cracked skin from extremely dry air, formidable snowstorms that can happen eight months a year on a whim, and blowing, cold winds that streak down the east side of the Rocky Mountains and straight into your vulnerable pores, crushing your spirits along the way.

But the bane of my existence, and one that provides no shortage of anxiety, is driving in unforgiving and hostile road conditions. Getting behind the wheel when the weather wants to go full Canada is an exercise in caution. I know, I’ve been riding in cars and driving long distances for 35 years.

Image result for pothole snow
The dreaded and maligned pothole. Commonly found in Canada and feeds on unsuspecting sedans.

When I hit a mound of snow, a patch of black ice, or (god forbid) a huge – yet unavoidable – pothole (a Canadian special), I wince and bite my tongue, hoping that my journey doesn’t end with a broken down car and a damaged ego.

So you’ll have to forgive me when I say I can’t help but get the same feeling of dread when I set out on the open road of Microsoft Flow when I’m trying to migrate workflows or build processes in SharePoint.

Here’s another analogy: if doing SharePoint things in SharePoint Designer is a summer road in July, then doing SharePoint things in Microsoft Flow is a Canadian highway in February: you cross your fingers and hope for the best.

Don’t get me wrong; Microsoft Flow is a wonderful tool. It is the proverbial ‘glue’ of the Office 365 world when it comes to having Office apps and the larger connected app community speak to each other and pass data around in the Office 365 platform. It can do amazing things. And I use it critically with clients regularly, whether it is setting up new team sites, migrating workflows, or finding new ways to enhance the User Experience (UX).

But here’s the rub – pretending that it is friendly for users to casually use and that it does the same thing as SharePoint Designer (SPD) in all faucets of site, IA and workflow is a fool’s errand. It does not.

Just because a user can, doesn’t mean a user actually can.

Here are three common potholes …. er, scenarios in Microsoft Flow .

Flow Cannot Trigger when an Item or Document is Exclusively Modified

Well actually it can . . . But also when the item is created. The trigger action involved is “only when an item/file is created or modified”. This is a significant gap in multiple use cases, especially considering that some items need to be handled differently upon creation (e.g. an approval item, for example, may require different handling procedures). One might argue that items and documents are modified more often than they are created, especially in collaborative or team-based team sites that have working documents.

Workaround: If you’re cool to let the trigger handle the same workflow every time with no difference when an item is created or modified, then go for it. Otherwise you might consider using a Switch Case action to handle different procedures based on conditions that are checked right after the trigger action. That condition may include comparing the Created and Modified dates, and look something like this:

if(equals(triggerBody()?['Created'], triggerBody()?['Modified'],"Yes","No")

And in the Graphical User Interface (GUI), it might look something like this:

Some users have also had gripes about items not being modified until a second item is modified, which is just a straight-up bug.

That expression you see above – get used to it . . .

Flow makes Expression Writing as Easy as Rocket Science

I recently had to build a Flow that sent reminders on the first and fifth days of the month. Seems like a simple procedure, right? Well to perform such a simple procedure, you need to compose outputs that can handle such requests. Easy you say? ehhhhh …..

As a casual user, you’ll quickly find that in building a functional workflow that can pass information around and manipulate data, you’ll soon find yourself dabbling with Data Operations, Scheduling, or Variable handling actions (trial and error, of course). This inevitably leads you to performing some serious Google Fu just to see if you can find anything remotely accurate that resembles the exact expression you’re looking for. What you’ll find is piecemeal examples of Workflow Definition Language on discussion boards and tech communities that may have solutions (a hit or miss expectation). But it’s really a game of roulette; you’re not going to win every time.

Even the Microsoft help documentation is ironically unhelpful. Just look at the expression needed to replace a character in a string that can “easily be accomplished”:


Expecting average users to know Workflow Definition Language is a non-starter. It’s basically gibberish to anyone who doesn’t care that much, and expecting them to know this language – much like Excel formulas – is not really understanding the larger business audience that needs to use and support these flows. And the format just gets more complicated the more you have to manipulate the data:

Nested(nested(nested(obnoxiously nested(so nested its a bird egg))))

Even just using today’s date and time means you have to use “utcNow()” and have a basic understanding of ISO 8601 standards for date string formats . This is terribly inefficient for casual Jane and once-in-a-while Joe, especially with a GUI that promotes a “no-code” solution.

Workaround: Sadly, this is an unavoidable pitfall with Flow that probably won’t be going away for a while. One could minimize the use of expressions by handling data inside SharePoint list or library columns (calculated or otherwise), but having to blow out your list or library just to handle expression alternatives seems counter-intuitive. I recommend identifying and training a Flow champion internal to your company that is familiar with Workflow Definition Language and general expression assembly, at least until Flow comes around with a better GUI and UX for expression building. In the meantime, try to use Dynamic content fields where possible.

Flow Does Not Handle the Check-in/Check-out Process Very Well

Checking-in or checking-out items is a very common scenario with SharePoint. The point of it is to reserve rights to editing to one person so others have to wait their turn. This is usually true with working documents, such as proposals, statements of work, or accounting spreadsheets.

Flow does not have an overt action to check-in or check-out. Rather, one would have to utilize the SharePoint REST API and the Send an HTTP Request to SharePoint action in order to get what you need.

You can see where I’m going with this – it is already far more convoluted than this needs to be:

Again, the UX behind this procedure is already beyond what most regularly-skilled people could handle or want to try.

Workaround: Either get familiar with HTTP requests, have your SharePoint developer handle this Flow build (and maintain it), or go back to a workflow designed in SPD. There just is no pretty way to handle this, especially if your workflow has multiple different stages where check-in and check-outs are required multiple times.


Microsoft Flow will continue to grow in use, and does have avenues for submitting idea requests. Microsoft also provides a Roadmap for developments and improvements which is very handy to follow. However until multiple features get implemented (an ongoing that takes time and user feedback) I recommend having Flow champions or specialists who can help arbitrate your Flow needs into some thing functional (or have an Office 365 guy like myself show you the ropes).

Unless you want more potholes. But no one wants that. I hope.

Microsoft Teams Office 365 Project Management SharePoint

Managing projects in a modern world (Part 1)

Last week I wrote a blog post and it got a lot of interest. Just not for the reasons I would of thought. One of the screen shots on the blog post contained an image of dashboard.

Everyone was quite intrigued by this and were wondering what it was. So that is what today is about. This is the first of multiple blog posts showing how you can leverage Azure DevOps, Teams and Project Online to manage projects.

That dashboard is Azure Devops. Microsoft’s developer collaboration tools. It includes repositories(Azure Repos) for code, Kanban boards(Azure Boards), Test Plans(Azure Test Plans) as well as a few other tools targeted to developers.

In our case we are building a brand new intranet and plan to use Azure Boards to manage our workload across a variety teams, Azure Repos to store code for our Custom web parts as well as any scripts we create including site themes and site designs. Last but not least we will use Azure Test Plans to mange the quality of our new intranet.

So how can we setup our environment to ensure:

  • Has a place to communicate
  • Has a place to upload documents
  • Can manage their tasks
  • manage their sprint
  • See the overall project health

Step 1 Setup Azure Devops

  1. Create a new project called Intranet
  2. Create the teams in Azure Devops: “Communications”, “Training”, “Developers”, “Testers”
  3. Define the sprints in our case they are two week long
  4. Assign the sprints to each team
  5. Add your backlog of tasks
  6. Assign tasks to sprints
  7. Add the Delivery Plan extension for Azure DevOps

You will end up with something that looks like this.

Azure DevOps will play the basis for how we manage and plan our tasks. It is more powerful than Planner but can require significantly more effort to setup.

Step 2 Setup Up Teams

  • Create a new Team called Intranet
  • Create four new Channels “Communications”, “Training”, “Developers”, “Testing”
  • * Add a Azure DevOps KanBan board to each Channel aligning the Azure DevOps Teams Kanban to the Microsoft Teams Channel
  • * Configure Azure DevOps connector

* Azure DevOps was called Vistual Studio Team Services. Some of the documentation still references the old name.

All of your Channels should now look something like this

You will also be able have conversations about specific tasks

Step 3 Create a Roadmap

  • Turn on the Project Roadmap feature
  • Create a new Roadmap
  • Create Rows that align to each Azure DevOps Team
  • Add Features from DevOps to track the status and timeline

You will end up with something like the roadmap below.

The next post in this series will go into detail on how to setup Step 1 Azure DevOps. Configuring the iterations, teams and backlogs to work properly with Project and teams.