Categories
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

OperatorFormatExample
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’

Joiners

OperatorFormatExamples
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 ‘joe.smith@123.com’ or
Accountant/Email eq ‘bob.brown@123.com’

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.

FunctionFormat
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”.

FunctionFormat
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”.

FunctionFormat
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 5.1.2.4.7 of the ODATA URL Conventions to find out more about index positions.

FunctionFormat
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.

FunctionFormat
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

FunctionFormatExample
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’).

Math

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.

FunctionFormatExample
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 ‘bob.smith@company123.com”.

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!

Categories
Uncategorized

Join the Party: Letting Guests into your Office 365 Group

You’re a good chef. Cooking food is a favorite pastime, and you are constantly making delicious meals and treats. You’ll often host dinner parties for friends and family, and they come with their own concoctions and unique dishes. Everyone shares in the feast, and your inner circle are professionals at hosting a damn good pot luck!

But you want to share the delicious food with the neighborhood; after all, you have leftovers and you don’t mind new guests joining the party. So when the new family that moved in next door is mulling about on their front lawn, you open your window, invite them over to chat and give them samples of your food. They give the samples a thumbs up, chat with you about their favorite dishes, and even share with you a can’t-miss family recipe. In turn, you give them access to add the recipe to a new online cookbook you’re putting together that will be shared among your pot luck peeps – including your now freshly minted next-door friends.

What just happened here? Well, besides food bringing the neighbors together, this is analogous to providing Guests access to your Office 365 Group. Guests can be invited to join your Group conversations, consume team files, and even join team events.

Often times I have clients that ask about external access for vendors and the plausibility of having people outside the organization participating in the day-to-day activities. And it ranges from just having people “aware” of the conversations happening among the client team all the way to workflow-based document collaboration.

Every group of clients are different for a variety of reasons, but external sharing is a growing request as people become more comfortable with the cloud and managing online content collaboratively. However, sometimes the idea of setting up extranet capabilities can seem daunting. And no one wants to spin up a random cloud space just to share files; there has to be an easier way.

Here’s the cool thing about Guests in Office 365 – Guests do not require an Office 365 license to participate if you have Business Premium or Enterprise subscriptions. This is not a scenario where you have to set up an extranet environment just to get your Guests collaborating in Office 365.

Picture3

Adding Guests is pretty easy. You add a new Member to a Group like normal, but just put in their email address of choice. Office 365 will do the rest for you. And the new Guest will get an email shortly afterwards confirming their Guest status with access links.

Picture2

And much like a pot luck, there are some house rules and standards for being a Guest:

  • The first time a Guest accesses the Group site, they will be prompted with an Office 365 message about first-time logging in. However, there is no password to set up – Guests can get straight to the content areas.
  • Group members can only access certain parts of the Group site such as the team Home Page, Notebook, Documents, Pages, and Site Contents. This includes uploading and interacting with files.
  • Guests cannot access the Conversations from the Group Site. This can only be done through email conversations (you have to email the Group email address). If you try and click on the Conversations tab, you will get a friendly error message (and this makes sense, because there is no record for a Guest as a fully-licensed user of Exchange).
  • Regular people in your organization are already Members in Office 365. They don’t need to be invited as Guests. But they will need to be added as Group Members to get them into the Group activities.
  • If you feel like adding a Profile photo or changing the Display Name for a Guest, you’ll have to do it in Azure Active Directory.

The cool thing with Guests is that the functionality allows for a decent amount of collaboration; it does not simply pay lip service to being collaborative and then having a very restricting Guest experience. The Guest can get the full GUI and most functionality much like a normal user.

Picture4

With the speed, scope, and continual growth of the Office 365 platform, Guest access may tend to get lost as a handy little feature to start collaborating with the right people, right now. But make sure to get organizational buy-in before you start; for as cool as the Guest functionality is, you certain want to align with your organization’s collaboration strategy as well as your I.T. policies and rules.

Hey, maybe a food truck is in your future because of your chef and people skills (and how cool would that be?) But for now, adding Guests is an easy and neighborly way to get more friends in the door having fun at the party.