Category

Microsoft Flow

Category

Introduction

In my previous post I showed how you can use Flow to trigger a scheduled flow and retrieve an iCal feed over HTTP.

I also spent a bit of time explaining how to parse the text returned from the iCal feed into individual events and extract the information required to be able to import and synchronize the events in a SharePoint list. I mostly covered at a high-level and didn't give you detailed steps -- which is a good thing, because the article was already pretty long!

In today's post, we'll set up a SharePoint list and walk you through how to implement the flow, step-by-step.

Let's get going!

Setting up a SharePoint list

To synchronize an iCal feed to SharePoint, we need two things:

  • A feed
  • A SharePoint list

And we'll need a flow to combine the two.

For the feed, I'll use one of CalendarLabs.com's iCal feeds. Being Canadian, I'll use the Canadian Holidays feed, but feel free to use the US Holidays or any other that suits you. (Make sure that you get the URL from the Download button). I have to admit that I only learned about CalendarLabs.com when I was researching this post, but I'll definitely be recommending it to everyone!

There are all sorts of useful iCal feeds out there. For example, if your company uses People HR to for your HR software, you can get an iCal feed of employee vacations directly from People HR and synchronize with a list called Staff Calendar.

Let's assume you have found an iCal feed you want to sync and you tested it to make sure it returns something.

For the SharePoint list, we'll create one. Since the iCal feed I'm using is a list of Statutory Holidays, I'll call my new list Statutory Holidays (I know, right? How do I come up with this stuff?!)

To create the list, follow these steps:

  1. From a SharePoint site of your choice, go to Settings then Site contents
  2. From the Site contents page, select + New and pick App. We use App instead of List because we want to create an events list, not a regular list.
  3. From the Site contents > Your Apps page, pick Calendar
  4. From the Adding calendar dialog, in the Name field, enter Statutory Holidays (or whichever name you picked for your event list, I'm flexible!) then select Create.

Your list should automatically get created. Now we need to add one more field called UID to keep track of the unique identifiers for each event. The UIDs are provided by the iCal feed.

To add the column, follow these steps:

  1. From the list you just created, go to List settings
  2. In the Settings page, in the Columns section, select Create column.
  3. In the Settings > Create column page, type UID for the Column name.
  4. Un-check Add to default view, but leave everything else. We just want a simple text column to store the unique identifier, no need to get fancy.
  5. Select Ok to create the column.
  6. If you want, you can remove the columns that you won't use, but for the sake of brevity, I'll leave the existing fields as it.

To set up the Flow

Yesterday, I briefly touched how I build the flow to extract the data. Today, we'll do it step-by-step, starting with these steps:

  1. Log-in to https://flow.microsoft.com
  2. From the left navigation, select + Create
  3. From the Start from blank section, select Scheduled flow
  4. In the Build a scheduled flow dialog, pick a Flow name -- something like Sync Statutory Holidays from iCal Feed
  5. Under Run this flow, pick a Starting time that suits you, and select how often you want to repeat the flow. I picked 1 day under Repeat every.
  6. Select Create.
    Creating a Scheduled Flow

To retrieve the iCal feed

Once your flow is created, follow these steps:

  1. Select the + New step from the flow editor and type HTTP in the Choose an action window
  2. From the list of Actions, select HTTP
  3. Rename the new action you added to Get events from iCal
  4. In the HTTP action's details, set the Method to GET, and put your iCal feed's URL in the URI field.
  5. Leave everything else as is.
    HTTP Action

Try your flow by using the Test button in the upper right corner. Your Get events from iCal should return events. If it doesn't, check the URL.

To get the list of events

  1. Select the + New step and type Initialize in the Choose an action window.
  2. From the list of Actions, select Initialize variable
  3. Rename your new action to Get list of events
  4. In the initalize action's details, set the Name to Events, the Type to Array and the Value to:
    split(replace(body('Get_events_from_iCal'), '\\n', ''), 'BEGIN:VEVENT')

    Getting list of events

This will split the iCal feed into an array of events where BEGIN:VEVENT can be found.

To filter array elements that aren't events

  1. Select the + New step and type Filter in the Choose an action window
  2. From the list of Actions, select Filter array
  3. Rename the new action to Remove rows that are not events
  4. In the From field, select the Events variable using the Dynamic content
  5. Below the From field, select Edit in advanced mode and type:
    @not(startsWith(item(), 'BEGIN'))

Not events

This will keep only array items that don't start with BEGIN, thus removing all the iCal header information and leaving you with only events.

To create a loop to process all events

  1. Select the + New step button and type apply to each in the Choose an action window.
  2. From the list of Actions, select Apply to each
  3. Rename your action Loop through every event
  4. In the Select an output from previous steps field, select the Body of Remove rows that are not events in the Dynamic content picker.

This will create a loop for every event in the array.

Loop

To split every event into lines

  1. Within the Loop through every event loop, select Add an action
  2. In the Choose an action window, type compose
  3. From the list of Actions, select Compose
  4. Rename your new action Get all lines in event
  5. In the Input field, you'll want to use:
    split(item(), json('{"NL":"\n"}')?['NL'])

Splitting into lines

Now, all we need to do is extract the data from every relevant line

To get the Start Date

  1. Still within the loop, select Add an action and type Filter in the Choose an action window
  2. From the list of Actions, select Filter
  3. Rename the new action Find DTSTART
  4. In the Inputs field's Expression tab, type:
    @startsWith(item(), 'DTSTART')
  5. Add another action using Add an action and type Compose
  6. From the list of actions, select Compose
  7. Rename the action to Get DTSTART
  8. In the Inputs field, type the following expression:
    replace(first(body('Find_DTSTART')), 'DTSTART;VALUE=DATE:', '')

Getting Start Date

To get the End Date

Repeat the same steps as above except that you should call the Filter action Find DTEND and use the following expression:

@startsWith(item(), 'DTEND;VALUE=')

And in the Compose action, call it Get DTEND and use the following expression

replace(first(body('Find_DTEND')), 'DTEND;VALUE=DATE:', '')

Getting End Date

To get the Summary

You guessed it, repeat same as above, but name the Filter action Find SUMMARY and use the following expression:

@startsWith(item(), 'SUMMARY:')

And set your Compose action to Get SUMMARY and use this expression:

replace(first(body('Find_SUMMARY')), 'SUMMARY:', '')

Getting Summary

To get the Unique Identifier

One last time! Repeat same as above, but name the Filter action Find UID and use the following expression:

@startsWith(item(), 'UID:')

And set your Compose action to Get UID and use this expression:

replace(first(body('Find_UID')), 'UID:', '')

Getting UID

Now you have all the properties we need. You should test your flow to make sure everything works.

Everything we need

Note that if your iCal feed has different fields that you need, you may need to adjust your actions above. For example, some feeds will return DATE-TIME and DATE events, so you may want to add a little condition up there to deal with such events. To keep things simple, we won't do that here.

To verify if the event is already in SharePoint

  1. Still within the loop, select Add an action and type SharePoint in the Choose an action window
  2. From the list of Actions, select Get items
  3. In the Site Address field, type your site's URL
  4. From the List Name pick Statutory Holidays.
  5. Under Filter Query type UID eq ' then select the Output from Get UID in the Dynamic content window.
  6. Add ' to close the filter query
  7. Under Top Count, enter 1. We only care if there is already an event with a matching UID, so returning only 1 will do for us.

Getting SharePoint items

If you test your flow now, every Get Items action should return the following:

{
[]
}

Because there are no events to retrieve. Let's fix that.

To create a list item if there are no matches found

  1. Still within the loop, select Add an action and type Condition in the Choose an action window

  2. From the list of Actions, select Condition

  3. Rename the condition Any existing events found

  4. In the expression below type:

    length(body('Get_items')?['value'])
  5. Select by is greater than in the next field

  6. In the next field, type 0

  7. This will cause the condition to go to If yes when there is an existing event, and If no if there isn't an existing event. We'll only worry about If no for now, but you could always update the existing item in the If yes side if you wanted to. Just no today, ok?

  8. In the If no site, select Add an action

  9. In the Choose an action field, type Create item

  10. In the Actions list, select Create item from SharePoint

  11. In the Create item dialog, type your Site Address and pick Statutory Holidays from the List Name

  12. In the Title field, bind to the Output from Get SUMMARY

  13. We'll skip the Start Time and End Time for now. In the UID field, set it to the Output of the Get UID action.

  14. For the Start Time, we'll need to do some surgery because SharePoint expects the value to be formatted as yyyy-MM-ddThh:mm:ss. To do this, we'll use substring() to extract the year, month, and day from the event's date but using the following expression:

    concat(substring(outputs('Get_DTSTART'),0,4),'-',substring(outputs('Get_DTSTART'),4,2),'-',substring(body('Get_DTSTART'),6,2),'T00:00:00-00:00')

    Which essentially combines the first 4 characters of the Start Date with a -, followed by the next 2 characters of Start Date, followed by another -, and the last two characters of Start Date, followed by T00:00:00 to set the time to midnight. If your iCal returns DATE-TIME values, you'll also want to parse the time element instead of setting it to 00:00:00.
    The last part -00:00 is for the timezone. If you find that your events are coming in at the wrong time, you can adjust the time zone accordingly (e.g.: +01:00 or -01:00).

  15. Repeat the same formula for End Time, except that you should use GET_DTEND instead of GET_DTSTART.

SharePoint create items

Test your workflow, and you should have a whole bunch of new events! Then try again, and you should not get more events until the iCal feed adds a new event.

Conclusion

(Sigh of relief!) That was a long post!

This post showed you how to get an iCal feed in Flow and import events from that feed into a SharePoint list.

You can use a similar approach for other types of feeds.

There are a few more opportunities to improve the resiliency of this flow, and to deal with all-day events that span over two days when they should really last one day... but that'll have to be for another post.

I hope this helped?

Photo credit

Image by Free-Photos 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?