Category

Power Platform

Category

Introduction

Let's say you have public iCal feed and you want to access the events from within SharePoint Online.

You could use the sample web part I created many months ago which allows you to display external event feeds from RSS, WordPress, Exchange, SharePoint and iCal. You'd get something that looks like it exists in SharePoint.

But what if you wanted to use the events from that iCal feed within SharePoint as a lookup for another list. Or what if that iCal feed contained statutory holidays and you wanted to skip scheduled Flows on those days?

In such situations, you need to actually import the data into SharePoint so that you can use it. My web part sample won't do!

Today, we'll explain how to use Flow to import events daily from an iCal feed into a SharePoint list.

The idea for this post came from an issue that Tejas kindly submitted. Thanks for the inspiration!

The problem with repetitive tasks

At first, I was very tempted to tweak my React Calendar Feed web part sample to display events and automatically add events to a SharePoint event list. After all, I was already parsing the iCal feeds in my code, so couldn't I just add the events that were not already in the list?

The problem with this idea is that it would require someone with sufficient permissions to add events to that event list to load that web part once a day -- or at least regularly enough to make sure the iCal events and the list were in sync.

Bad idea.

Any time you require someone to do a manual step regularly to keep a system going is not sustainable. It's like that countdown timer in the TV show Lost that required someone to enter a sequence of numbers or the world would end.

Lost countdown
The countdown clock from Lost -- I don't need that kind of responsibility!

It's what we called on a project I worked on "Death by a thousand cuts". It may not seem like a big deal to ask someone to do something every x days to keep a system going, but those little temporary solutions you take on eventually accumulate to a point where you need a person whose job is just to do those little things. It isn't a job that's very fulfilling for anyone.

Might as well use Homer Simpson's "stupid bird" to push a button every once in a while.

Homer Simpson's stupid bird
Homer's stupid bird

So, the lazy approach wouldn't work for me this time.

What could I use? A timer job on a server? Nah, I want a server-less solution.

How about a scheduled Azure web job? No, I want a no-code solution. Or at least low-code.

If only there was a way to schedule workflows to run regularly that wouldn't require any code...

Scheduled flows to the rescue!

Thankfully, Microsoft Flow allows you to create scheduled flows. We already discussed using schedule flows in a previous post, so I know with certainty that it will work.

Scheduled flows

I configured my flow to run every day. That's probably an overkill depending on how often your iCal feed gets updated, so feel free to adjust accordingly:

Schedule recurrence, every 1 day

Now here's the problem: Flow does not have a way to parse iCal feeds. I looked everywhere for a ready-to-use "import iCal" connector, but couldn't find any. As it turns out, I'm not the only one who wants this.

Note to Microsoft: if you accept open source contributions for connectors, let me know and I'll gladly submit an iCal connector.

But iCal feeds are really just text files with a very specific structure. Could we not just use the Flow HTTP connector and retrieve the feed as a simple string of text and parse it?

HTTP connector

Note: Unfortunately, HTTP is a Premium connector in Flow... but you get so much more with Flow Premium that it is worth it! Trust me!

This is how I configured my HTTP connector to retrieve my iCal feed:

HTTP call to iCal

Parsing the results to get a list of events

I configured an HTTP connector to do an HTTP GET with a public iCal feed. I used CalendarLabs.com's Canadian Holidays sample, but they have many other great sample calendar feeds.

And it worked. The response wasn't pretty, but it worked!

Here is an example of what I got. The full list is a lot longer, but you get the idea.

BEGIN:VCALENDAR
PRODID:fd29_Array_canada_country_holidays@calendarlabs.com
VERSION:2.0
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:Canada Holidays
X-WR-TIMEZONE:America/New_York
BEGIN:VEVENT
DTSTART;VALUE=DATE:20180101
DTEND;VALUE=DATE:20180102
DTSTAMP:20111213T124028Z
UID:5c60f18d0973d@calendarlabs.com
CREATED:20111213T123901Z
DESCRIPTION:Visit https://calendarlabs.com/holidays/us/new-years-day.php to know more about New Year's Day. 
 Like us on Facebook: http://fb.com/calendarlabs to get updates.
LAST-MODIFIED:20111213T123901Z
LOCATION:Canada
SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:New Year's Day
TRANSP:TRANSPARENT
END:VEVENT
BEGIN:VEVENT
DTSTART;VALUE=DATE:20180212
DTEND;VALUE=DATE:20180213
DTSTAMP:20111213T124028Z
UID:5c60f18d09784@calendarlabs.com
CREATED:20111213T123901Z
DESCRIPTION:Visit https://calendarlabs.com/holidays/canada/family-day.php to know more about Family Day (BC). 
 Like us on Facebook: http://fb.com/calendarlabs to get updates.
LAST-MODIFIED:20111213T123901Z
LOCATION:Canada
SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:Family Day (BC)
TRANSP:TRANSPARENT
END:VEVENT

As you can see, iCal feeds use lines as a delimiter for each field. Every new event start with BEGIN:VEVENT on a new line.

So I used the Initialize variable to create an array variable which would contain every event in the feed. I called the variable Events.

Initialize variable

For the initial value of the variable, I used the split() function, and I specified that it should create a new array element every time it found a BEGIN:VEVENT.

Some iCal feeds that I tested also had extra newline characters, so I took the opportunity to remove those while I was parsing the feed, by using the replace() function to replace all \\n with nothing ('') as follows:

split(replace(body('Get_events_from_iCal'), '\\n', ''), 'BEGIN:VEVENT')

Splitting the events

The problem is that the split() function just blindly creates array elements wherever it finds the delimiter you specify. It doesn't care what comes before or after the delimiter.

For example, my sample feed contains 8 lines before the first event, meaning that this text:

BEGIN:VCALENDAR
PRODID:fd29_Array_canada_country_holidays@calendarlabs.com
VERSION:2.0
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:Canada Holidays
X-WR-TIMEZONE:America/New_York
BEGIN:VEVENT
DTSTART;VALUE=DATE:20180101
DTEND;VALUE=DATE:20180102
DTSTAMP:20111213T124028Z
UID:5c60f18d0973d@calendarlabs.com
CREATED:20111213T123901Z
DESCRIPTION:Visit https://calendarlabs.com/holidays/us/new-years-day.php to know more about New Year's Day. 
 Like us on Facebook: http://fb.com/calendarlabs to get updates.
LAST-MODIFIED:20111213T123901Z
LOCATION:Canada
SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:New Year's Day
TRANSP:TRANSPARENT
END:VEVENT
BEGIN:VEVENT
DTSTART;VALUE=DATE:20180212
DTEND;VALUE=DATE:20180213
DTSTAMP:20111213T124028Z
UID:5c60f18d09784@calendarlabs.com
CREATED:20111213T123901Z
DESCRIPTION:Visit https://calendarlabs.com/holidays/canada/family-day.php to know more about Family Day (BC). 
 Like us on Facebook: http://fb.com/calendarlabs to get updates.
LAST-MODIFIED:20111213T123901Z
LOCATION:Canada
SEQUENCE:0
STATUS:CONFIRMED
SUMMARY:Family Day (BC)
TRANSP:TRANSPARENT
END:VEVENT

When the text is split where BEGIN:VEVENT is found, I get the following array:

[ "BEGIN:VCALENDAR\nPRODID:fd29_Array_canada_country_holidays@calendarlabs.com\nVERSION:2.0\nCALSCALE:GREGORIAN\nMETHOD:PUBLISH\nX-WR-CALNAME:Canada Holidays\nX-WR-TIMEZONE:America/New_York\n",

"\nDTSTART;VALUE=DATE:20180101\nDTEND;VALUE=DATE:20180102\nDTSTAMP:20111213T124028Z\nUID:5c60f18d0973d@calendarlabs.com\nCREATED:20111213T123901Z\nDESCRIPTION:Visit https://calendarlabs.com/holidays/us/new-years-day.php to know more about New Year's Day. \n Like us on Facebook: http://fb.com/calendarlabs to get updates.\nLAST-MODIFIED:20111213T123901Z\nLOCATION:Canada\nSEQUENCE:0\nSTATUS:CONFIRMED\nSUMMARY:New Year's Day\nTRANSP:TRANSPARENT\nEND:VEVENT\n",

"\nDTSTART;VALUE=DATE:20180212\nDTEND;VALUE=DATE:20180213\nDTSTAMP:20111213T124028Z\nUID:5c60f18d09784@calendarlabs.com\nCREATED:20111213T123901Z\nDESCRIPTION:Visit https://calendarlabs.com/holidays/canada/family-day.php to know more about Family Day (BC). \n Like us on Facebook: http://fb.com/calendarlabs to get updates.\nLAST-MODIFIED:20111213T123901Z\nLOCATION:Canada\nSEQUENCE:0\nSTATUS:CONFIRMED\nSUMMARY:Family Day (BC)\nTRANSP:TRANSPARENT\nEND:VEVENT\n",
...
]

Notice that the first array element is different than the others. It contains information about the calendar feed, which we don't care about for our needs.

To remove anything that isn't an event, I simply used the Filter action and kept only array elements that do not start with BEGIN:

Filtering array where the item doesn't start with BEGIN

(I could have filtered for events that start with DTSTART, but I didn't want to have to deal with the funny \n character at the start of every element)

This is the array the filter action returned:

[
"\nDTSTART;VALUE=DATE:20180101\nDTEND;VALUE=DATE:20180102\nDTSTAMP:20111213T124028Z\nUID:5c60f18d0973d@calendarlabs.com\nCREATED:20111213T123901Z\nDESCRIPTION:Visit https://calendarlabs.com/holidays/us/new-years-day.php to know more about New Year's Day. \n Like us on Facebook: http://fb.com/calendarlabs to get updates.\nLAST-MODIFIED:20111213T123901Z\nLOCATION:Canada\nSEQUENCE:0\nSTATUS:CONFIRMED\nSUMMARY:New Year's Day\nTRANSP:TRANSPARENT\nEND:VEVENT\n",

"\nDTSTART;VALUE=DATE:20180212\nDTEND;VALUE=DATE:20180213\nDTSTAMP:20111213T124028Z\nUID:5c60f18d09784@calendarlabs.com\nCREATED:20111213T123901Z\nDESCRIPTION:Visit https://calendarlabs.com/holidays/canada/family-day.php to know more about Family Day (BC). \n Like us on Facebook: http://fb.com/calendarlabs to get updates.\nLAST-MODIFIED:20111213T123901Z\nLOCATION:Canada\nSEQUENCE:0\nSTATUS:CONFIRMED\nSUMMARY:Family Day (BC)\nTRANSP:TRANSPARENT\nEND:VEVENT\n",

"\nDTSTART;VALUE=DATE:20180214\nDTEND;VALUE=DATE:20180215\nDTSTAMP:20111213T124028Z\nUID:5c60f18d097c3@calendarlabs.com\nCREATED:20111213T123901Z\nDESCRIPTION:Visit https://calendarlabs.com/holidays/us/valentines-day.php to know more about Valentine's Day. \n Like us on Facebook: http://fb.com/calendarlabs to get updates.\nLAST-MODIFIED:20111213T123901Z\nLOCATION:Canada\nSEQUENCE:0\nSTATUS:CONFIRMED\nSUMMARY:Valentine's Day\nTRANSP:TRANSPARENT\nEND:VEVENT\n",
...
]

Now all I needed to do was to loop through every event and parse the values...

Processing each event

So far, I have a scheduled flow which retrieves an iCal feed, splits the text into an array of events, and filters out things that aren't events.

To process every item in the array of events, I just used the Apply for each action:

Apply for each

When it asked me what I wanted to loop through, I specified the Body of the filter action from before.
Looping through events

Now that I have the flow looping through every event, I need to split the event into individual lines, using the split() function, like before.

Except that this time, instead of storing the array of lines in a variable, I use the Compose action to temporarily build my array.

Compose

The only problem is, I found it very difficult to write a split() function to divide by the newline character. Luckily, someone who is way smarter than I am (tre4B) came up with a solution.

tre4B's solution is to temporarily define a JSON object which defines the newline character as a JSON element, and use that JSON element, as follows:

json('{"NL":"\n"}')?['NL']

I must admit, it's almost like voodoo to me, but it works.

So, to split every event into individual lines, I used:

json('{"NL":"\n"}')?['NL']

If your feed uses both a newline and a carriage return, you would use this instead:

split(item(), json('{"NL":"\r\n"}')?['NL'])

Splitting by newline

Now, every event returns an array of lines that look like this:

[
  "",
  "DTSTART;VALUE=DATE:20180101",
  "DTEND;VALUE=DATE:20180102",
  "DTSTAMP:20111213T124028Z",
  "UID:5c60f18d0973d@calendarlabs.com",
  "CREATED:20111213T123901Z",
  "DESCRIPTION:Visit https://calendarlabs.com/holidays/us/new-years-day.php to know more about New Year's Day. ",
  " Like us on Facebook: http://fb.com/calendarlabs to get updates.",
  "LAST-MODIFIED:20111213T123901Z",
  "LOCATION:Canada",
  "SEQUENCE:0",
  "STATUS:CONFIRMED",
  "SUMMARY:New Year's Day",
  "TRANSP:TRANSPARENT",
  "END:VEVENT",
  ""
]

Once I have every line as an array element, I can just use the same filter() technique I used before to find each line for the Start Date, End Date, Summary and anything else I need.

For example, to find the Start Date, I find the line which starts with DTSTART:

Filtering for DTSTART

And to get the date value, I use the replace() function to remove everything before the date, which looks like this:

replace(first(body('Find_DTSTART')), 'DTSTART;VALUE=DATE:', '')

All that's left is a string that represents the date. I won't bother converting the string to a date because SharePoint will expect a very specific date format later anyway.

I just repeat the same thing with DTEND for the End Date and SUMMARY for the event title.

Also, I'll retrieve every event's UID which is a unique identifier that I'll be able to use later to verify if the event has already been created in SharePoint.

Once completed, my event parsing looks like this:

My event parsing loop

It looks like it's a lot of work, but thanks to Flow's new clipboard functionality, it was able to copy and paste the Find DTSTART and Get DTSTART actions for the DTEND, SUMMARY and UID.

Easy!

To be continued

Sorry if this post was long (and probably boring). We had a lot of stuff to cover, and we're not finished yet!

So far, we have created a scheduled workflow which reads an iCal feed and parses each event to retrieve their individual attributes.

I should point out that my approach isn't the only way to do this, I'm sure. This is the way I did it. For example, instead of using replace(), I could have used the substring() function to extract the parts of the strings I wanted, but I prefer replace() because it makes it easier to read what the function is doing. Feel free to use whichever approach you like.

Tomorrow, we'll use the same technique we used before to see if every event exists in a SharePoint list. If it doesn't, we'll create it.

I'll also share the step by step instructions tomorrow.

I hope you'll come back tomorrow for the second part!

Photo credit

Image by Karolina Grabowska from Pixabay

Introduction

A tale, inspired by a true story:

Once upon a time, there was an IT guy who had been tasked to buy a piece of software. He wasn't asked to do research or to investigate whether his company had the required infrastructure (or staff) to run the software. He was told to buy it and not to ask any questions.

The boss had already made up his mind. He wanted this software because the adverts in the magazine told him it would solve all his problems. He had already talked to the salespeople over a game of golf and a lavish dinner with copious amounts of alcohol.

The salespeople -- really nice and friendly folks -- had assured the boss that the software would not need any configuration or installation, and it wouldn't affect any of his systems or cause any downtime.

When the IT guy started asking questions about whether the software the boss wanted was the right choice, the boss pretty much told him that if he didn't want to take care of it, the boss would find someone who would. From what he had seen from the marketing videos on YouTube, the boss was pretty sure that even he could do it himself.

Eventually, the IT guy gave in and installed the software.

But not before discovering that the software required a whole bunch of new servers and needed to run on an operating system that no one in the IT department had any experience managing. So they hired a new IT guy that knows that particular operating system.

Meanwhile, the database administrators found out that the only database platform that was supported by this new software was not the database platform the company had standardized on. They bought more servers and hired a new DBA that was familiar with that database platform.

When it came time to customize the software to meet the company's needs, they found out that none of the application development team had the time or the skills required to do such customization. Luckily, one of the top contractors in that programming language happened to be available and could start immediately -- at a premium rate.

One day, as the exasperated IT guy was eating a sandwich and staring blankly at the lunchroom wall, the Webmaster guy -- who usually works on another floor, but was in the neighbourhood for a meeting -- walked in.

- "Whoa, you look like you have had a rough few months!" said the webmaster. It was meant as a joke, but it was also true.

The IT guy and the webmaster had known each other for a long time. They worked together when the IT department was just a handful of guys. Back then, the webmaster was just running the company's web site, but he had since started managing the company's intranet and portal.

- "Agh! I just found out that we're going to have to start a nightly export of our user data because the [expletives deleted] isn't compatible with our Active Directory. Of course!", was the IT guy's response. "And our first migration to the new system is going to take a lot longer than we expected and we'll need to ask the accounting department to stop working for two whole weeks while we migrate the system. And they're unhappy because it is the end of the fiscal quarter."

The webmaster asked cautiously: "And... what exactly does this new software do?"

The IT guy explained what the new software did.

- "Uh, you mean like what our current portal platform has available out of the box?" asked the webmaster. "That's what [redacted]'s team has been using for about two years now. The boss even sent an email congratulating the team for doing such a good job. I even demoed it to the boss!".

The rest of the tale isn't appropriate for this blog. But there was a lot of cursing and yelling. Let's just say that they lived miserably forever after, having to maintain that software that never truly worked the way it was intended.

Sadly, this kind of scenario happens more often than you'd think. You may have experienced this yourself where you work.

This post will explain some of the tips and tricks to use when buying new software that will help you make an educated decision.

Software acquisition model

I often hear people talk about "Buy vs Build" when discussing their software acquisition model.

In reality, you should always consider Re-use, Buy, and Build.

st=>start: Start
d1=>condition: Can I re-use?
d2=>condition: Can I buy?
e1=>end: Re-use
e2=>end: Buy
e3=>end: Build
st->d1(no)->d2(no)->e3
d1(yes)->e1
d2(yes)->e2

Re-use

Do you already own a piece of software that will meet your needs? It may be an unused part of something you already bought, or it may be an internal application that another department has already developed.

You should also consider open-source solutions as part of the re-use decision. Can you re-use open-source software that already exists out there, for free, to meet your needs?

Is there an add-on feature available for of the software you already own that would meet your needs? Even if it would cost you a little more to enable that feature?

If the answer is yes (or mostly yes), you should explore the possibility of re-using what's already available before buying.

Don't compromise, but don't miss what's already right in front of you either.

I recently went through this process with a client that uses Office 365 -- with SharePoint, Flow, and PowerApps at their disposal. They wanted to buy a piece of software because it called a third-party API and made it possible to trigger data workflows from the results of the API... which is something that they could already do with Flow.

They just didn't know that feature was available.

Before considering to buy a new piece of software, it is a good idea to take an inventory of what you already have.

Buy

If you can't re-use -- or doing so would deliver a less-than-optimal solution -- by all means buy something!

However, before you pull out your credit card, make sure to do a proper gap analysis. Compare what your existing software (if any) actually offers against what you really need it to do. Then use that same gap analysis criteria to compare with the software you want to purchase.

When considering buying, keep in mind the Total Cost of Ownership of that comes with every piece of software.

Don't know what Total Cost of Ownership means? Check this blog for an upcoming post on the subject.

After considering your gap analysis and total cost of ownership, if you can't find software that meets your needs (within the budget you've been given), consider building something.

Whatever you do, resist the urge to skip the buying option and go straight to building.

Build

Don't listen to developers (like me) who'll tell you "Oh, that'll take me a couple of weeks to build". Because it never does.

This may sound weird coming from someone who considers himself a developer at heart, but it is true.

They're not lying to you on purpose to protect their jobs. And it isn't a reflection of their skills. They really do mean well.

But most organizations suck at building software projects.

Don't take my word for it: The Standish Group is an organization that publishes a yearly Chaos Report. The report describes the state of the software development industry and seeks to identify the scope of software project failures, the major factors that cause software projects to fail, and the key ingredients that can reduce project failure. I highly recommend that you buy your own copy -- it is worth it. And I am not affiliated with The Standish Group in any way.

Last time I bought the report, a staggering 31.1% of software projects will be cancelled before they ever get completed. 52.7% of projects will cost 189% of their original estimates.

Back in 1995, the Standish Group estimated that American companies and government agencies spent $81 billion for cancelled software projects. They paid an additional $59 billion for software projects that were being completed but exceeded their original time estimates.

The average number of software projects that are completed on-time and on-budget is only 16.2% -- a number that goes a low as 9% in larger companies.

And once completed, those "successful" projects will only deliver approximately %42 of the originally-proposed features and functions.

It doesn't need to be an all-or-nothing situation

Nowadays, software is so much more open and versatile than it was many years ago. Yet, we still deal with software acquisitions as a giant monolith that cannot integrate with anything.

The ideal solution for your needs may very well be a hybrid solution: using the software you already own (re-use), adding a component or an app that meets most of your needs (buy), and making minor customizations to meet your exact needs.

Although this post isn't about Office 365, I often see organizations running Office 365, SharePoint Online and Dynamics 365, but they fail to fully recognize the capabilities available at their disposal.

Take a look at the various Office add-ins and Dynamics 365 AppSource for solutions that you can buy that will handle most of your needs. And, with Flow and PowerApps, you can easily configure your solutions to do exactly what you want. There are countless connectors available that may allow you to build a low-code or no-code solution that can adapt as your company's needs evolve.

The same applies to other products -- not just Office 365. Understand what you have so that you can fully leverage it before you look at buying or building something new.

Conclusion

Impulse buying is something that may be suitable for a pack of gum while you're waiting to pack at the grocery store, but it should never be an option when it comes to enterprise applications.

Make an educated decision, and follow an acquisition model that will help you find the ideal solution for your organization's needs.

I hope this helps?

Sources

Image Credit

Image by Arek Socha from Pixabay

Introduction

In my last two posts, I covered how to use the SharePoint Get items action in Flow and how to tell if the SharePoint Get items action returned items (by counting them).

I really wanted to provide a real-life sample how one would use the two concepts together in Flow.

Since we just had a national holiday and I completely forgot about it (got ready to go to work and everything), I thought I'd create a sample flow that automatically runs on a schedule and prompts users to do something, except when today's date is a holiday.

The workflow logic looks a little like this:

st=>start: Start (every n days)
e=>end: End
op2=>operation: Get today's date
op3=>operation: Find statutory holidays
with today's date
op4=>operation: Today is not a holiday
sub1=>subroutine: (Do something)
cond=>condition: Is today a holiday?
(Did you find any items)
io=>operation: Today is a holiday
(Do nothing)
st->op2->op3->cond
cond(yes)->io->e
cond(no)->op4->sub1->e

Let's get started by creating the environment we need for this workflow.

Creating a list of statutory holidays

In this example, we'll create a SharePoint list which will contain an entry for every statutory holiday. We'll use a list because it allows our HR folks to maintain it without needing a special app. We can also show the list on our SharePoint site so all employees can see what days are statutory holidays.

You can also use your own database, or an API, or even a static Excel spreadsheet if you want, but I wanted to use a SharePoint list to show how to use the SharePoint Get items action in Flow.

To create the list, follow these steps:

  1. From a SharePoint site, go to Site contents via the local navigation or the Settings option.
  2. In the Site contents, select + New then choose List from the drop-down menu.
  3. In the Create list pane, enter Statutory Holidays as the list's Name. Check or uncheck Show in site navigation depending if you want your users to see the list or not.
  4. Select Create to create the list
  5. In your newly created list, select + Add column then select Date from the drop-down list.
  6. In the Create a column pane, enter Date for the column Name. Set Include time to No and select Require that this column contains information to Yes under More options. This list doesn't make much sense if you don't require a date for each stat holiday.
  7. Select Save to create the column.

If you need to support statutory holidays for multiple states/provinces/countries, feel free to add more columns to your list to support your needs. I wanted to keep this list as simple as possible.

Why didn't I use a calendar list? I didn't want to add the extra columns that come with a calendar list. If you really want a calendar view, just add it as a custom view for your list.

Use your list's Quick edit to enter your statutory holidays. I use this site to get the list of statutory holidays for every year.

When you're done, you should have a list that looks like this:
Statutory Holidays, Canadian Style

Now let's create a scheduled flow that uses the list!

Creating a scheduled flow

  1. From https://flow.microsoft.com, go to My flows to view your list of flows.
  2. From the + New menu, select Scheduled--from blank
  3. In the Build a scheduled flow window, give your flow a descriptive Flow name. I named mine Prompt managers to approve timesheets.
  4. Under Run this flow, select the schedule that suits your needs. I want mine to go once a week on Mondays, so I selected 1 week under Repeat every, then selected M under On these days and unselected every other day.
    file.
  5. Select Create to create your workflow.

Your workflow will be created and open in the workflow editor. I renamed the Recurrence action to Every Monday because I always want my workflows to be easy to understand without having to expand every action.

Unfortunately, Flow won't let you save until you add another action.

Funny, cause that's exactly what we'll do next!

Connecting to the Statutory Holiday SharePoint list

Before we can access the Statutory Holidays list in SharePoint, we need to add a connection to SharePoint by following these steps:

  1. From within your flow editor, select +New step at the bottom of the flow.
  2. In the Choose an action prompt, type Get items in the Search connectors and actions. Search is case insensitive.
  3. Select the Get items action with a SharePoint logo from the list of Actions that appears. If the search query returns too many actions and you can't find the SharePoint Get items, you can filter out all other connectors by clicking on SharePoint just below the search bar.
  4. As soon as you select Get items, the Choose an action box will transform into the Get items box.
  5. If you haven't created a connection to SharePoint yet, you'll be prompted to Sign in to create a connection to SharePoint. Click Sign in to sign in with the account that you wish to use to access SharePoint.

    The account you use here specifies who will access SharePoint. Make sure that you use an account that can see the site and the list where you want to get items from. It is a good idea to use a service account that isn't using your own credentials to connect.

  6. Once connected, enter URL to the site that contains your list under Site address. If you experience problems typing or pasting the URL, try selecting Enter a custom value from the drop-down; it will turn the drop-down box into a text box.
  7. If the site URL you entered is valid and the credentials you supplied are correct, you should be able to pick the Statutory Holidays list from the List Name drop down.

Adding a filter to retrieve today's statutory holidays

If you ran the flow now, it would retrieve every statutory holiday in the list.

We want SharePoint to return only statutory holidays on the days the flow runs. To do this, we'll add an ODATA filter by following these steps:

  1. In the new Get items action you just created, select Show advanced options
  2. In the Filter Query field, enter Date eq datetime''.
  3. Place your cursor between the two single quotes you just typed and select Add dynamic content
  4. Select the Expression tab
  5. Scroll to the Date and time category and select See more, then select formateDateTime(timestamp, format) to insert it in the expression field.
  6. Making sure your cursor is between the two parentheses of the formatDateTime function, find the utcNow() function in the Date and time category.
  7. After utcNow() but before the last ), type ', 'yyyy-MM-ddT00:00:00') and select OK to insert the expression.
  8. In the Top Count field, enter 1 -- we only need to know if there is a statutory holiday or not, so we don't need to return more than one.
  9. In the Limit Columns by View, select All Items. This will ensure that we only return the Title and Date columns, instead of returning every single column in the list.

    If you want to test your flow, save it and use Test in the upper right corner. You can add a temporary list item in your statutory holidays list with today's date to see that SharePoint returned something.
    Test worked

If everything goes well, your flow is now able to retrieve statutory holidays from the SharePoint list every time your flow runs.

Now let's add logic to detect whether something was returned or not...

But before we do, let's rename the Get items action to Retrieve statutory holidays for today's date to make it easier to read. Hey, my blog, my naming conventions 🙂

Count how many statutory holidays were returned for today's date

As I explained in my previous post, I like using variables to make my flows easier to debug and easier to understand. We'll store the number of items returned in a variable called Number of statutory holidays.

Since this is the first time we set the variable, we'll use Initalize variable using the following steps:

  1. In the flow editor, select +New step
  2. From the Choose an action box, type variable in the search box.
  3. From the list of suggested actions, select Initialize variable.
    4.An Initialize variable box will replace the Choose an action box. Give your variable a descriptive Name. For example: Number of statutory holidays.
  4. In the Type field, select Integer -- because we'll be storing the number of items returned.
  5. We'll write the expression to calculate the number of items returned the Value field. If the dynamic content pane doesn't show, select Add dynamic content, the select Expression.
  6. Look for the length(collection) function in the Collection category and select it to insert it in the expression box. The length function is specifically designed to calculate how long a collection of items is -- and that's what the Get items action returns: a collection of items.
  7. Make sure your cursor is positioned between the two parantheses () in the length function. Select the Dynamic content tab and look for the value dynamic content for the Retrieve statutory holidays for today action.
  8. Flow will automatically insert body('Retrieve_statutory_holidays_for_today''s_date')?['value'] inside your length() function. The final expression should be:
    length(body('Retrieve_statutory_holidays_for_today''s_date')?['value'])
  9. Select OK to insert the value.

Save and test your flow. Mine returned 1 item:
1 item returned

Testing if any items were returned

Now that you have a variable that contains the number of statutory holidays, you can use it anywhere you want.

Let's create a conditional branch to do something if today is not a statutory holiday:

  1. In the flow editor, select +New step
  2. From the Choose an action box, select Control then Condition.
  3. A Condition box will replace the Choose an action box. Give your condition a descriptive name. For example: Is today a statutory holiday.
  4. If the Choose a value box, use Add dynamic content to select the variable you created earlier.
  5. In the next field, select is greater than
  6. In the next field (Choose a value) enter 0.
    Is today a stat holiday?

Save and test your flow. If everything worked well, the Expression value from your condition should return true if SharePoint found items, and false if nothing was found. My test returned true.

We have a stat holiday!

That's it! Now you can insert actions under If no to do something when today isn't a statutory holiday.

You could even add something under If yes to delay the flow until next day, but that's another post.

Conclusion

You can use Scheduled flows to run every n days and easily query a SharePoint list containing statutory holidays to skip running when the current date is a statutory holiday.

Note that in today's sample, I didn't deal with timezones by setting the start time of my workflow so that it is later than midnight in UTC time. If you run your workflow across multiple timezones, you should keep this into consideration.

I hope this helps you create workflows that know when to take it easy.

Because everyone deserves a vacation once in a while!

Photo credits

Image by Free-Photos from Pixabay

Introduction

Im my previous post, I explained how to use the SharePoint Get items action in Flow. As the name implies, it retrieves items from a SharePoint list.

Sometimes you need to know if your Get items action returned any items. For example, if you wanted to update an existing item or create a new item in none was found.

In this post, I'll show you how to count how many items were returned by SharePoint and how to test if any items were found.

And don't worry, this post won't be as long as the last one.

Counting results from SharePoint Get items

For the purpose of this example, we'll assume that you already created a flow with a SharePoint Get items action. If you haven't done so yet, take a look at my previous post.

Sample flow

When I have to use fancy formulas in many places within my flow, I like to define a variable. That way, I can just refer to the variable instead of re-entering the formula in many places.

You should always strive to make your flows easy to read so that if someone else has to maintain it (or if you have to come back to it later), it will be easy to understand what the flow does. Make sure to give your actions a descriptive name (not Get items like in my example, use something like Get existing responses from current user, for example). Using variables is another way to make your flows easier to use.

When using variables in flow, you use a different action to define a variable the first time (Initialize variable) than you would to set the variable or change its value (Set variable, Increment variable, and Decrement variable for example).

Since this is the first time we set the variable, we'll use Initalize variable using the following steps:

  1. In the flow editor, select +New step
  2. From the Choose an action box, type variable in the search box.
  3. From the list of suggested actions, select Initialize variable.
    Initialize variable
    4.An Initialize variable box will replace the Choose an action box. Give your variable a descriptive Name. For example: Number of existing items.
  4. In the Type field, select Integer -- because we'll be storing the number of items returned.
  5. We'll write the expression to calculate the number of items returned the Value field. If the dynamic content pane doesn't show, select Add dynamic content, the select Expression.
    Using an expression
  6. Look for the length(collection) function in the Collection category and select it to insert it in the expression box. The length function is specifically designed to calculate how long a collection of items is -- and that's what the Get items action returns: a collection of items.
    Length function
  7. Make sure your cursor is positioned between the two parantheses () in the length function. Select the Dynamic content tab and look for the value dynamic content for the Get items action (or whatever your SharePoint Get items action is called).
    Inserting body of get items
  8. Flow will automatically insert body('Get_items')?['value'] inside your length() function. The final expression should be:
    length(body('Get_items')?['value'])
  9. Select OK to insert the value.
    Formula inserted

Save and test your flow. Mine returned 1 item:
1 item returned

Testing if any items were returned

Now that you have a variable that contains the number of items, you can use it anywhere you want.

For example, if you wanted your flow to do something if any items were returned, and something else if nothing was returned you would follow these steps:

  1. In the flow editor, select +New step
  2. From the Choose an action box, select Control then Condition.
    Condition
  3. A Condition box will replace the Choose an action box. Give your condition a descriptive name. For example: Are there any existing items.
    Adding a condition
  4. If the Choose a value box, use Add dynamic content to select the variable you created earlier.
  5. In the next field, select is greater than
  6. In the next field (Choose a value) enter 0
    Condition for more than 0 items

Save and test your flow. If everything worked well, the Expression value from your condition should return true if SharePoint found items, and false if nothing was found.
We found items

Of course, you would want to add actions to your If yes and If no paths, but that's for another post.

Conclusion

The key to testing if the SharePoint Get items action returned items it to understand that Get items returns a collection of items. Using the length() function against the return value of your Get items action will tell you the length of your collection of items.

I could have avoided using a variable and just entered the length(body('Get_items')?['value']) formula directly in the condition, but I wouldn't be able to tell how many items were returned when I was testing the flow. This sample was an easy one, and I really didn't need to evaluate how many items were returned more than once -- so I really didn't need a variable -- but in more complicated flows, you'll find it a lot easier to define variables and use the variables throughout instead of copying the same formula every time.

I hope this helps?

Introduction

I love Flow (and Logic Apps)!

Favourite thing to do with Flow is doing demos and workshops!

When I meet a new customer who tells me they have a business problem, I love to put together a quick proof of concept how to solve their business problem using a no-code solution, involving SharePoint, PowerApps, and Flow -- right there, in front of them, while projecting. No safety nets, PowerPoint or scripted demos.

The SharePoint connector is by far my most frequently-used connector, because it allows me to quickly query, create, and update content in SharePoint as part of my solutions.

Even if I have used this connector many times, I sometimes get demo blindness and I forget how to use it when I'm in the middle of a demo.

This article explains how to use the SharePoint connector and the GetItems action to retrieve items from a SharePoint list.

Hopefully, next time I forget how to use it in the middle of a demo, this article will show up in the search results.

NOTE: This article focuses on Microsoft Flow, but you can use the connector in Logic Apps in (almost) the same way, and PowerApps with these instructions.

Creating a Test Flow

For this article, we'll assume you want to connect to SharePoint to get one or more items in a list from within an existing Flow.

If you already have an existing flow you can use to follow along, go ahead and skip to the next section.

If you don't have an existing flow, let's create a Flow that you can manually trigger by following these steps:

  1. From https://flow.microsoft.com, navigate to My Flows.
  2. Select New then Instant -- from blank to create a flow that we'll be able to trigger at any time to test. Feel free to use any other type of flow here.
    New | Instant -- from blank
  3. In the Build an instant flow dialog, enter a Flow name and select From Microsoft Flow when prompted Choose how to trigger this flow and select Create.
    Build an instant flow  dialog

Your new flow will be created. Note that you need to insert at least one step before you can save or test it.

Good thing that's what we're doing next!

Adding a SharePoint connection

Before we can access SharePoint items, we need to add a connection to SharePoint by following these steps:

  1. From within your flow editor, select +New step at the bottom of the flow. You can also click on the + button that appears between two existing flow steps.
    New step
  2. In the Choose an action prompt, type Get items in the Search connectors and actions. Search is case insensitive.
    Get items
  3. Select the Get items action with a SharePoint logo from the list of Actions that appears. If the search query returns too many actions and you can't find the SharePoint Get items, you can filter out all other connectors by clicking on SharePoint just below the search bar.
  4. As soon as you select Get items, the Choose an action box will transform into the Get items box.
  5. If you haven't created a connection to SharePoint yet, you'll be prompted to Sign in to create a connection to SharePoint. Click Sign in to sign in with the account that you wish to use to access SharePoint. If your instance of SharePoint is on-prem, you can check Connect via on-premises data gateway -- but that's for another post.
    Sign in to SharePoint

    The account you use here specifies who will access SharePoint. Make sure that you use an account that can see the site and the list where you want to get items from. It is a good idea to use a service account that isn't using your own credentials to connect.

  6. Once connected, enter URL to the site that contains your list under Site address. If you experience problems typing or pasting the URL, try selecting Enter a custom value from the drop-down; it will turn the drop-down box into a text box.
    file
  7. If the site URL you entered is valid and the credentials you supplied are correct, you should be able to pick the list you want to use from the List Name drop down. In my example, I only have one list called Parking Lot Passes.
    Selecting a list name

    If you get a GUID in your List Name instead of a friendly name, make sure that the connection you're using has permissions to access the list. You can change the connection by selecting the elipsis (***) at the top of the Get items box and using the My connections section to change or add a new connection.
    My connections

Let's test it!

  1. Select Test from the top toolbar. If it is disabled, you may need to Save it first.
    Test
  2. From the Test Flow pane, select I'll perform the trigger action and select Save & Test.
    Test Flow
  3. The Run Flow dialog will prompt you to confirm the conneciton information. Make sure everything is correct and select Continue
    Run Flow
  4. Once you confirmed the connection, you'll get prompted again. It usually only happens the first time you create or change a connection. Select Run flow.
    Run flow -- again
  5. If everything went well, you should see Your flow run successfully started. Select See flow run activity to see if everything went well.
    Your flow run successfully started.
  6. You'll get a Run history for your flow, usually sorted by newest at the top. Select the top (and most likely only) one by clicking on the start time.
    Run history
  7. From your flow history page, you should see green checkmarks next to every step in your workflow. If you get a red x, check your connection information.
    Success
  8. Click on the Get items action to see what SharePoint returned. You want to see a 200 Status code, and a Body that returns value items.
    Get Items results

You now have a connection to your SharePoint list. Now let's add a filter to get only the items you want from the list.

Building an ODATA filter

For this article, I'll use a list I had created for a Park Pass request application. It has a Text column called Make, a Person column called RequestedBy, and Date and Time columns called From and To. You can use whatever list you want in your workflow.

Pro Tip: when creating a column that has a space (or any other funny characters) in the name, create the column without the space first, then rename it with a space. That way, you'll avoid funny column names like Requestedx20By. In my example the Requested By, was created as RequestedBy (no spaces) first, then renamed it to Requested By.

The Get Items action allows you to specify an ODATA filter query to filter returned items from a list. To specify a filter, select Show advanced options from the Get items action.

Get Items Filter

If you aren't familiar with ODATA filters, you can read the article about using ODATA query operations in SharePoint REST requests, or read below to find how I build my ODATA filters.

In most cases, you can write your query as [columnname] [operator] [value]. Where [operator] is one of the following keywords:

  • Lt: Less than
  • Le: Less than or equal to
  • Gt: Greater than
  • Ge: Greater than or equal to
  • Eq: Equal to
  • Ne: Not equal to

For example, to retrieve all cars where the Make is Canyon Arrow, you would write:

Make eq 'Canyon Arrow`

If you wanted to retrieve any Make but the Canyon Arrow, you would write:

Make ne 'Canyon Arrow`

If you can't figure out why your ODATA filter doesn't work, here is a mostly foolproof way to build your ODATA filter:

  1. Using your browser, navigate to: https://[yourtenant].sharepoint.com/sites/[yoursite]/_api/lists/getbytitle('[Your list title']). For example, my if my tenant is ashbay16, my site is TestPowerApps and my list is titled Parking Lot Passes, my URL would be:
    https://**ashbay16**.sharepoint.com/sites/**TestPowerApps**/_api/lists/getbytitle('**Parking%20Lot%20Passes**').
  2. If you entered the right URL, you should see information about the list in XML format.
  3. Add /fields at the end of the URL you created in step 1 to get all the fields names in your list. For example, my URL is now https://ashbay16.sharepoint.com/sites/TestPowerApps/_api/lists/getbytitle('Parking%20Lot%20Passes')/fields
  4. You may want to use an XML editor (like Visual Studio Code) to view the XML results from the previous step. Find the field you want to filter on by searching the XML file for the field title.
  5. While you're looking at the XML definition for the field, take a look at the d:Filterable node to see if it is filterable (it should be true). Also, take a look at the d:TypeAsString node to see what type of field you're dealing with. Finally, look at d:EntityPropertyName -- that'll be how you refer to that field in your filter. For example, to filter by Approval Status, you would use OData__ModerationStatus. Note that the column names are case sensitive.
  6. Look at the ODATA query syntax chart below to see what possible filter you can use to build your filter. I've grayed out the parts that don't apply below:
    ODATA filter query
  7. Depending on your field's d:TypeAsString, you can use the following queries:
    • Text: lt, le, gt, ge, eq and ne, plus startswith() and substringof(). For example, substringof('S', Model) will return all entries where the Model column contains the letter S. Note that the field name is the second parameter with substringof and startswith.
    • Number: lt, le, gt, ge, eq or ne
    • DateTime: day(), month(), year(), hour(), minute(), second(). You can also use datetime to compare a date. For example SubmittedDate gt datetime'2019-06-14T00:00:00' to get items where the SubmittedDate column is greater than June 14, 2019.
    • User: Use lt, le, gt, ge, eq and ne, plus startswith() and substringof() to evaluate against the user's display name, or specify the user's attribute. For example: RequestedBy/EMail eq 'hugo.bernier@contoso.com' to find items where the email address of the RequestedBy user is hugo.bernier@contoso.com.
  8. Test your filter in your browser by replacing /fields with /items?$filter=[yourfilter]. For example, if my filter is Make eq 'Canyon Arrow', my URL would be https://ashbay16.sharepoint.com/sites/TestPowerApps/_api/lists/getbytitle('Parking%20Lot%20Passes')/items?$filter=Make%20eq%20%27Canyon Arrow%27. Notice that when I type spaces and single quotes, the browser will url encode the values for me -- meaning Canyon Arrow becomes Canyon%20Arrow.

Once you have built your ODATA filter, add the filter to your Get Items action.

Specifying the ODATA filter

Now that you have your ODATA filter, go back to your flow and:

  1. Select Show advanced options on your Get items action.
  2. In the Filter Query enter your ODATA filter. For example, I entered Make eq 'Canyon Arrow'
    Static filter
  3. Save and test again.

Your results should now be filtered!

Specifying a dynamic ODATA filter

Flow allows you to enter dynamic values pretty much anywhere. Let's say we wanted to filter items that were previously submitted by the user who triggered the workflow.

  1. In your Get items action, make sure that the advanced options are showing. If not, select Show advanced options.
  2. In the Filter Query field, enter SubmittedBy/EMail eq ''
  3. Position your cursor between the two single quotes and select Add dynamic content to show the list of possible dynamic values you can use.
  4. From the list of dynamic content, find User Email from the Manually trigger a flow category
    Adding dynamic content.
  5. Save and test your workflow.

Note, you could also have simply used SubmittedBy eq '[User name]' from the dynamic content list, but I specifically wanted to compare by email in this example.

Your results should contain only items that were submitted by you (since you triggered the workflow).

Dealing with results from Get Items

The SharePoint Get Items action always returns an array of items -- whether it found 1 record, zero records, or a whole bunch of records.

If you insert a new action that uses the results from Get Items, Flow will automatically wrap the action in a loop, iterating through each record that was returns from Get Items.

For example, let's pretend we wanted to email the person who submitted every SharePoint list item where the To column contains a date that is earlier than today (or utcNow() in Flow). You would first set your query as follows:

  1. In the Get items action, enter To lt datetime'' in the Filter Query field.
  2. Place your cursor between the two single quotes. This time, instead of using Dynamic content, select the Expression tab.
  3. In the list of possible expressions, select utcNow() from the Date and time category. Between the parentheses, type 'yyyy-MM-ddTHH:mm:ssZ'. Your final expression should be utcNow('yyyy-MM-ddTHH:mm:ssZ'). Click Update to insert your expression.
    Entering an expression
  4. Immediately below the Get items action, select +New step and insert a Send an email action from the Office 365 Outlook group.
    Send an email
  5. In the newly inserted Send an email action, select the To field and use Add dynamic content to select Requested by Email (or whatever field you want) from your Get items action.
  6. You'll notice that as soon as you select dynamic content from the Get items action, Flow converts your Send an email into an Apply to each loop.
    Apply to each loop.
  7. Finish writing your test email and test your workflow. (Be careful that you don't send emails to a whole bunch of people, they might not appreciate it!).

Caution: Throttling

When using the SharePoint Get items action, your Flow may get throttled (i.e.: slowed down) if you exceed more than 600 calls within 60 seconds. You may want to keep that in mind when designing your flow.

Conclusion

In this long post (too long!), I explained how to use the SharePoint Get items action within Flow.

There is still a lot to cover (for example, how to detect if any items were returned, how to reduce chances of throttling by specifying the columns to return and the number of items to return, etc.), but I hope that you'll be able to get started using Get Items.

Have fun!