Recently we had a customer ask us to automate the creation of structured folders with Power Automate for new projects and to also generate and assign a project number. When a user creates a new folder for the project, the folder should automatically be renamed to "UKXXXX - Folder Name". For example, if the user creates a new folder named "Important Project" then it should be renamed to "UK1234 - Important Project". Then a set of subfolders should be created within it, as shown below.

When first looking at the requirements it seems relatively simple, but I soon realised that there was a complication that I had not considered. There is no action in Power Automate to rename a folder.

Before creating the Flow, I created a document library to store the project documents and a list to store the next project number.

I called the document library "Live Projects". No metadata columns are required but I can add these in the future if needed to assist with searching.

I called the List "Next LP Number", it is very simple and only contains one item. The item has an ID of 1. It is probably a good idea to apply some permissions on the list to ensure that nobody can delete the item. The list has a number column to store the next project number. Just add the first item with the number of the first project. As below.

The first step of the Flow is to add the trigger. In this case I used "When a file is created (properties only)". This is because I want the Flow to be triggered when a new folder is created.

Then I created some variable that I will use in the Flow.

'Current Name' is initialised with the path of the folder that has triggered the Flow. This is relative to the site address.

Next the Flow needs to check that the item that was created is a folder and that the folder is at the root of the document library. This bit is important because without the check, each folder in the set that is created later will cause the Flow to trigger again and again and again... I discovered this to my cost, and I will not make the same mistake again.

Checking that the item is a folder is simple. Checking that the new folder has been created at the root of the document library is more complex.

I used indexOf(variable('Current Name'), '/') is equal to lastIndexOf(variables('Current Name'), '/') to check that there is only the one '/' in the string variable 'Current Name. This works well for me, but I am sure there are other methods.

If the result of the condition is False, then the Flow does nothing and exits. If the result is True, then the Flow continues to the next step.

First get the next project number from the list we created earlier.

Now the variable 'job' has been set to the project number, so we have enough information to create the new name for the folder.

The variable 'New Name' is now set correctly, so the project number can be incremented ready to be saved back to the list for next time.

We can now rename the new folder, but there is no action available to do this, so we need to use "Send an HTTP request" as shown here.

Now we can save the next project number back to the list. This is done by updating list item 1 with the variable 'Job'.

All that remains now is to create the set of folders using the "Create new folder" action for each of the required folders. Repeat the action as many times as needed.

That is the end of the Flow, but as usual there is more that can be added. For example, a notification could be sent out to project managers and a set of tasks created to manage the project.

Power Automate is an incredibly powerful application that offers a variety of ready made templates to help you get started.

Read our recent post on the retirement of SharePoint 2010 Workflows here.

If you would like to learn more about Power Automate, or any products related to Microsoft 365 please visit our website. We are also available to speak to on 0800 6444 365 or via info@adepteq.com.

Phil Cave - Technical Director Adepteq

Working with dates has never been simple, just look at the issues surrounding the 'Millennium Bug'. There is always the time and date format to consider and then there are time zones. ISO 8601 does help by standardising the date to YYY-MM-DD, so that 2nd of March 2020 becomes 2020-03-02 no matter where you are located. Then there is the time portion, but I think i'll leave that alone for today.

Last week Microsoft announced that they will be turning off SharePoint 2010 workflows in SharePoint Online. This step will cause many organisations a huge amount of effort to identify and rectify where 2010 workflows are being used and then recreating them.

There is little point in recreating these workflows using SharePoint 2013 workflow as these are nowhere near as powerful or future proof as the Flows that can be created using Power Automate.

Power Automate also has the advantage that it is not locked into SharePoint but can work with data from over 300 other systems using the available connectors. New connected are being added all the time.

One of the things that we do at Adepteq is offer annual support contracts for SharePoint Online and also for the entire Office 365 suite. These contracts are stored in our helpdesk system which we created in SharePoint and have been using for many years now. I think we first created it on SharePoint 2010.

Our contract renewal reminders are managed using a long running SharePoint 2010 workflow. Unfortunately, this workflow will stop working in November, so I will have to rewrite it soon.

It is a simple workflow that uses the out of the box action "Find Interval Between Dates" to check how many days are remaining until the end of the contract. If there are 30 days remaining an email is sent to our sales team with a copy to the support team. We can then send a proposal for a new contract to the customer.

Extract from current 2010 workflow

Extract from current 2010 workflow

I shall recreate this functionality using Power Automate. This new Flow will run every night at 1am to check the number of days remaining. If there are 30 days remaining, then a notification will be sent to our sales team so that they can create the new proposal.

Power Automate could be used to create the proposal automatically and send it directly to the customer, but that is for a future blog post.

First create a new Flow using "Scheduled - From Blank"

Build a scheduled flow - Power Automate

We need the Flow to run once every day. Here I chose 3am as the start time but you can start at any time that is convenient, it just depends on when you want the email reminder to be delivered.

Next we need to add a step to get the list items from the contracts lists. You will then need to specify the site address and the list name.

Getting list items from contracts list - Power Automate

You need to remember that by default 'get items' will only return the first 100 items, which is not good if you have more than 100 contracts. To return more than 100 items you will need to go into settings and enable pagination and then set the threshold to a suitable value. In the example below I have used 5000.

Change the threshold for items retrieved from a list

let's initialise a variable to store the number of days remaining for the contract.

Initialising a variable

Now add an 'Apply to Each' step that we will use to enumerate each list item.

Apply to Each

We now need to calculate how many days are remaining for each contract so that we can send a reminder email when only 30 days remain.

This is more complicated than it is in the 2010 workflow because there is no 'Find Interval Between Dates' action in Power Automate. So instead we have to do the calculation using the Ticks function. This returns the number of nanoseconds since 1st January 1601. We need to get the difference in nanoseconds between now and the contract start date, then convert that into the number of days. Finally, take that number away from 365 to give the days remaining until the end of the current contract.

To convert nanoseconds to days we just have to divide by 864000000000.

This is all done using the expression 365-(div(sub(ticks(formatDateTime(utcNow(),'yyy-MM-dd')),ticks(item()?['ContractStartDate'])),864000000000)) Just remember to replace 'ContractStartDate' with the name of the column that contains your date.

Adding an expression

Now the variable 'daysLeft' contains the number of days remaining for the current contract, we can use a simple condition to check the value and take action. In my example below, if there are 30 days remaining an email reminder is sent. If not, then no action is taken. 

Condition example in a Flow

This Flow can now easily be developed further to create a task to the sales team and to follow up if the task is not completed. If the customer decides to renew the contract then the account team could be notified to raise an invoice...

Read our recent post on the retirement of SharePoint 2010 Workflows here.

If you would like to learn more about Power Automate, or any products related to Microsoft 365 please visit our website. We are also available to speak to on 0800 6444 365 or via info@adepteq.com.

London Head Office

Adepteq,
152 - 160 City Road,
London,
EC1V 2NX,
0203 805 4143

Aylesbury Development Centre

Adepteq,
7 Smeaton Close,
Brunel Park,
Aylesbury,
Buckinghamshire,
HP19 8SU
01296 323460

 
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram