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.

Office 365 SharePoint Workflows

How to do Workflows in SharePoint: Part 3 – Scheduling a Workflow

This is part 3 in a blog series of basic workflow design using SharePoint Designer and Microsoft Flow. See How to do Workflows in SharePoint: Part 2 – Parallel Actions for the previous entry in this series.

Sometimes its necessary to run a workflow on a continual basis. For example, to check for updates or changes to items in a library, or to do a monthly tally of list items with conditional statuses.

SharePoint Designer

Unfortunately there is no “silver bullet” easy answer to do this – kind of like if a video game only had a hard setting. In SPD there is no overt trigger action or condition for a scheduling scenario. However, crafting a workflow is definitely possible, and it depends on your use cases and objectives for your business process.

Let’s say you want create a workflow that runs every day at 2:00 A.M. (starting tomorrow). The workflow will update a title with a new value .

  1. Create a SharePoint 2013 workflow on the library or list.
  2. Insert a Stage to kick-start the workflow at 2:00 A.M. (use the Pause Until action).
  3. Insert a Stage called “Field Update”, and have a variable continually update with every loop (e.g. variable +1). Then have the Title set to this variable value.
  4. Insert a Stage at the end of the workflow and call it “Timed Loop” or something similar (it’s best practice to name your workflow stages with descriptive titles as accurately as possible).
  5. Insert a Pause for Duration action and have it set to 24 hours.
An example of a scheduled workflow using SharePoint Designer. Thank the heavens for Stage management, otherwise this would be a dozy!

This is a simple example of how one could have a scheduled workflow built with SharePoint Designer. However, it’s not a silver bullet. This workflow needs to be started manually (once), and it will also never technically “end” as it keeps looping for infinity (Loop steps can be used if it only need to loop X number of times, or given certain conditions). I like to think of this as a proverbial MacGyver job, and really only used if SPD is either required or your workflow jam.

Microsoft Flow

Comparatively speaking, building schedule jobs in Microsoft Flow is like taking a warp zone to the end of the game. It’s fantastically easy.

  1. After creating a new Flow, chose the Recurrence trigger as the first item. Set up the recurrence interval, and set the Start time (you’ll have to know the format for your start time, which could be a bit easier, but c’est la vie).
  2. Attach your normal workflow steps.
Use the Recurrence trigger to set a scheduled Flow. Much easier!

Which one is easier: No doubt Microsoft Flow is easier to use for scheduling actions on SharePoint lists. However, there are third-party options for workflows that make the setup of scheduled workflows much easier, such as Nintex Workflows or even work it in with Windows Task Scheduler. But without investing in third-party applications, Microsoft Flow is the clear winner. Happy Scheduled Flowing!

Please visit my blog Ion All the Things for helpful information about SharePoint and Office 365!

Office 365 SharePoint Workflows

How to do Workflows in SharePoint: Part 2 – Parallel Actions

This is part 2 in a blog series of basic workflow design using SharePoint Designer and Microsoft Flow. See How to do Workflows in SharePoint: Part 1 – Conditional Starts for the first entry in this series.

Sometimes you want a workflow to do more than one thing at the same time. It is not an uncommon business case to require simultaneous activities, especially if the workflow needs to do different things with different groups of people (e.g. getting separate streams of approvals between Supply Chain and HR). 

SharePoint Designer (SPD) and Microsoft Flow will always conduct workflow steps in sequence. If you require that certain things get done simultaneously, both applications can provide the controls to do it.

SharePoint Designer 

Use a SharePoint 2013 workflow on the library or list. Parallel blocks are not available for SharePoint 2010 workflows.

  1. Insert a Parallel Block (inside a Stage). You can do this from the Insert section of the Ribbon (or by right-clicking).
  2. Write the logic you need in each block (there is a slight indent into the parallel actions).
  3. That’s it! Consider using Steps inside Parallel Blocks to organize your parallel actions into manageable blocks (and to see your actions visually separated from one another!)

Nested Parallel Blocks (Waiting for one of multiple things to happen)

Parallel Blocks can also be used for Conditional Starts in waiting for a field to change in order to proceed a workflow. This is handy if you want to wait for certain values in a field before doing the next step (e.g. approval statuses).

  1. Add a Parallel Block to your workflow.
  2. Add as many nested Parallel Blocks inside the parent Parallel Block as you need.
  3. For the parent Parallel Block, right-click on it and click Advanced Properties.
  4. Set the “CompletionCondition” to a new boolean variable. By default, the new variable will continue further if the variable equals “true”.
  5. In each nested Parallel Block, add a “Set Variable” status so that the new variable equals true.
  6. That’s it!

What you will get is the workflow “waiting” for one of the nested Parallel Blocks to be true before proceeding. This is very handy if only certain conditions have to be met, and not working within the limitations of the operators provided (e.g. equals, not equals).

Microsoft Flow

On a workflow that already has a trigger and an action, hover your mouse about the connector line and click the + button. You can add a parallel branch. When the new parallel branch is created, it will be create adjacent to the main workflow stream. Add all of the actions you need within this branch. When you want the parallel actions to end, just at a new step at the after both branches are complete (it will auto-join).

  1. On a workflow that already has a trigger and an action, hover your mouse about the connector line and click the + button. You can add a parallel branch (1).
  2. When the new parallel branch is created, it will be create the new branch adjacent to the first workflow stream. Add all of the actions you need within this branch in sequence as required (2).
  3. When you want the parallel actions to end, just add a New Step. It will conjoin the two (or multiple parallel blocks) together (3). This is not the same thing as “adding a step” under a current Parallel branch; two different function!

Which one is easier? Microsoft Flow, for the simple fact that it is visually easier to look at when doing branch design (this just doesn’t look as appealing in text-based logic, but to each their own). Of course with that said, you can always view and edit your workflow in Visio (click the View button and pick “Visual Designer”).

In the next entry in this series, we look at basic steps to scheduling workflows (a bit more advanced, depending on which tool you are using!).

Please visit my blog Ion All the Things for helpful information about SharePoint and Office 365.

Office 365 SharePoint Workflows

How to do Workflows in SharePoint: Part 1 – Conditional Starts

Workflows are the lifeblood of organizations. Business processes – and their efficacy – are critical to the successful operation of a team, business or organization. This is why the workflows you use in your collaboration tools should be easy to build, deploy, and use by people of various responsibilities and technical chops. SharePoint workflows should follow suit.

In the first of this three-part series, I will address some common scenarios you may need build into your SharePoint workflow and how to go about building what you need in both SharePoint Designer (still kickin’) and Microsoft Flow (the shiny new object and getting shinier!) Below are simple ways to begin your workflows that need have conditional starts, where certain conditions must be met on a document or item before the workflow can proceed.

SharePoint Designer

  1. Create a new SharePoint 2013 workflow on your library or list.
  2. Set how the workflow starts (created, modified, manually) in the options menu.
  3. In the Logic screen, insert an “If” statement in the first stage. Add another “If” statement right below it to create multiple conditions (something not so obvious in SPD unfortunately). This is where you can select your operator (“and” or “or”).
  4. Pick the fields and pick the values it must meet conditionally.
  5. Complete the If/Then logic you require. Don’t forget to use an “Else” statement to tell SharePoint what to do if the “If” statement isn’t realized, otherwise it will continue to follow through in chronological order. It is also good practice to include a “Log a Message to the Workflow History List” so that any audits done on the workflow for a given list item can be reviewed with more context and see how the workflow followed the logic.
A basic workflows in SharePoint Designer with a conditional start.

Microsoft Flow

  1. Start a new workflow (either from a template or from scratch).
  2. Create a Trigger. Your trigger should be how you want the workflow to begin (on item or document creation, modified, manually, for a selected item, etc.)
  3. Create a Condition action (use Basic mode for one condition, and Advanced Mode or parallel or nested conditions for more conditions).
  4. Add your subsequent workflow steps in sequence.

A basic workflow in Microsoft Flow with conditional start.

Which one is easier? I feelSharePoint Designer is easier on the whole, especially for starts that require multiple conditions (you don’t have to write logic in Advanced Mode or do nested conditions). However, the graphical user interface may be more appealing to some workflow builders. Try both and see what you like!

Next in the series is how to handle Parallel Actions (having more than one action run simultaneously).

Please visit my blog Ion All the Things for helpful information about SharePoint and Office 365!