For a selected file

For documents, a flow is written with the trigger “For a selected file”, and the SharePoint site and library are specified in the trigger. To run the flow, you navigate to a file in the library, select the file and then open the Actions menu to click the desired flow.

That’s how it works for documents. But what if you want to run a flow on a specific folder? There is no Power Automate trigger “For a selected folder”. But I’m writing this because I found a way to run a workflow for a selected folder!!

A common scenario

My company uses a defined folder structure in our SharePoint library to organise the documents for our client projects . I have written a workflow that generates the correct folders and populates them with document templates. Now I would like to run this workflow when we start a new project with a client. I would like to select the client folder and start the workflow, so a new project folder is created inside the client folder.

Let’s write the flow that runs for the selected folder

I start with the trigger “For a selected file”. When we inspect the flow run for this trigger, we can see the output of the trigger. Click on “Show raw outputs” to display the side panel.

The output starts with a lot of lines in the headers section that are not of interest to us right now. We need only the section at the bottom that starts with “body”. The body contains the text field that I configured as the input parameter of the trigger. That is followed by an “entity” that contains the itemURL and the fileName.

The fileName contains the name of the selected file or folder. The itemURL contains valuable information about what kind of item was selected, file or folder. You can select and copy the itemURL content and paste it into a text editor for easier inspection.

Here is the structure of the output for a file:

https://tenant.sharepoint.com/sites/<SiteName>/_layouts/15/Doc.aspx?sourcedoc=<GUID>&action=default&uid=<GUID>&ListItemId=207345&ListId=<GUID>&odsp=1&env=prod

And here is the output for a folder:

https://tenant.sharepoint.com/sites/<SiteName>/<Library Name>/Forms/AllItems.aspx?id=%2Fsites%2F<SiteName>%2F<LibraryName>%2F<FolderName>%2F<SubFolderName>&viewid=<GUID>

We can clearly see differences:

• A selected file URL contains the library (ListID) as a GUID parameter, the document is identified with the sourcedoc GUID parameter and the URL contains the system view _layouts/15/Doc.aspx
• a selected folder URL contains the library name written out, the view name in the URL, and the viewid as a parameter

Flow logic

We can use these differences to determine if the selected item is a folder or a file, and then take corresponding action in the flow. My flow starts with the trigger “For a selected file”. The next action is a condition that checks if thes selected item is a document or not.

I’m checking for the existence of the text “/Doc.aspx” in the itemURL. There are different ways to approach this. I have decided to use the function indexOf(<where to look>, <what to look for>), which returns a positive number representing the position of the text if it is found in the string. If the text is not found, the value -1 is returned.

indexOf(triggerBody()?['entity']?['itemUrl'],'Doc.aspx?')

When writing this formula, you don’t need to know the syntax for the first parameter. You can click on the button Dynamic values and pick the itemURL from the list.

Type the search term manually. Make sure that you put the search text inside a pair of single quotes. (One of my pet peeves with the Power Platform tools is that there are so many differences between the formula languages for Power Automate and Power Apps. I’ve been bitten more than once, using single or double quotes in the wrong scenario.)

The comparison for my condition is “greater than 0”. The indexOf() function will return a zero if the search text is found at the beginning of the string, because it’s a zero index notation, but the text we’re looking for is always further along and its position will never be zero.

So, if the indexOf is greater than 0, we’re dealing with a document, otherwise we’re dealing with a folder.

In this particular workflow, I use a “Terminate” statement in the Yes branch of the condition, so the rest of the flow only runs if the selected item is not a document.

This is it. We can now run a workflow on a selected folder.

Further processing

You will need to develop your own approach for what you want to do next, based on your needs and business logic. In the following I’ll describe how to extract the path to the selected folder, so it can be used in an HTTP statement to create a sub folder.

The itemURL for a folder contains the full folder hierarchy up to the selected folder. The forward slash symbol “/” is written with the URL Encoding “%2F” and any space characters in the path will be showing as “%20”. You could use the replace() function to make the path more readable. A full list of URL Encoding can be found at the WWW3Schools site.

You can use other text functions to extract the path to the current folder for further processing, for example:

Get the position of the beginning of the path, starting from “/sites” – find the text “=id” and increase the result by the length of the string “=id”, which is three characters long.

add(indexOf(triggerBody()?['entity']?['itemUrl'],'=id'),3)

Get the position of the “&viewid” which marks the end of the path:

indexOf(triggerBody()?['entity']?['itemUrl'],'&viewid')

With a start and an end position, you can then extract a substring, for example the full path starting with “/sites” and ending with the selected folder:

substring(indexOf(triggerBody()?['entity']?['itemUrl'],
add(indexOf(triggerBody()?['entity']?['itemUrl'],'=id'),3),
indexOf(triggerBody()?['entity']?['itemUrl'],'&viewid')
)

Explore the other string functions like nthIndexOf() or lastIndexOf() to nail down specific places in the path.

My tip: start with writing separate Compose actions for each of these exploratory index functions. Run the flow and you can see what number is returned by the Compose actions. When you are happy with the resulting numbers, you can construct a more complex all-in-one formula to return your desired string.

Create a subfolder with HTTP

With the folder path extracted, you can now use it in other actions that require an exact folder location or in a SharePoint HTTP action to create a new folder inside the currently selected one. The screenshot below uses a Compose statement with the path components that follow the “/sites/sitename/library/”

That’s my workflow. Let me know in the comments if you found this useful and in what scenario you want to use this approach.

Enjoy.

Cover Photo by Sear Greyson on Unsplash

But each row is always as high as the highest card in the row, and it is not possible to have a card extend over several rows.

For example, I would like to arrange several smaller cards stacked vertically, side by side with a card that has a big text box.

Such a layout is very easy to do in Word or PowerPoint by merging rows and columns. HTML tables have parameters for colspan and rowspan. But in a PowerApps custom for for a SharePoint list, this is not easy to do. The table below illustrates what I mean. The green column spans are possible, but the red cell spanning several rows is not.

At least not by just arranging cards on the PowerApps form. In this blog I will show you what you need to do to get a form layout like this one:

I assume that you already know how to customize a SharePoint list form in PowerApps and that you have changed the screen size to a landscape orientation. Change the properties of the SharePointForm1 to use at least two columns and turn the Snap to columns off. A form with a few fields should then look similar to this:

You can see that the toggle control card is as high as the multi-line text card. I want the three toggles to be stacked on top of each other and arranged next to the multi-line text card, so the text card would have to span three rows.

Here is how I did it.

Create an empty custom card

Select the form (probably still called SharePointForm1, and in the right hand side Properties panel click Edit fields. Click the three dots at the top and then select Add a custom card.

Arrange the new custom card so it sits next to the card with the multi-level text field. You can do that by dragging the custom card in the field list, or by changing the X and Y position of the card in the Advanced settings of the card.

With the new custom card still selected, add three labels to the card, one for each toggle field, and arrange them vertically.

Copy the original control

Now select the control in the original card and copy it. You can use the keyboard shortcut Ctrl+C or right click and select Copy from the context menu.

Then click the empty custom card and use Ctrl_V or the right-click Paste command to paste the control into the custom card. You will notice that the control shows a red x to indicate that there is something wrong with it.

Click the down arrow on the error indicator and select Edit in the formula bar. The formula bar shows the formula Parent.Default. This property makes sense in the original card, which has a default setting for the toggle control. We need to re-establish the link back to that original card.

Whenever I reference cards or controls by their name, I usually edit their names and write in something more meaningful than the default that PowerApps generates. My three data cards for the three Yes/No fields are called ONE_DataCard, TWO_DataCard and THREE_DataCard, and I renamed the three original toggle controls to OneToggle, TwoToggle and ThreeToggle.

Change the Parent part of the formula to the name of the original card. In my case that is the ONE_DataCard, so the complete property now reads ONE_DataCard.Default and the red error indicator has gone away.

Repeat that process for the other controls that you want to place in that card.

Due to their default settings, these controls will now inherit their values from the original data cards. Next, we need to make sure that these controls send their changes back to the SharePoint list columns.

Link the input controls to list columns

Inspecting the original cards, there is one property that is responsible for updating the SharePoint list column when edits are made. It is visible in the Advanced section of the data card, but only after clicking on the button for More options and is called Update.

Out of the box, this command refers to the control in the current card and returns the value of that control. The exact command to return a control’s value will depend on the type of control. A text control will use ControlName.Text, a combobox dropdown will use ControlName.Selected and a toggle will use ControlName.Value. But we don’t really have to worry about that, since we only copied the control and did not change its type. So, all we need to do is update the control name that the command is referring to.

When copied and pasted the control to the custom card, the copied control name ended up with a _1 at the end. So copying OneToggle resulted in the name OneToggle_1. I’m not a big fan of numbering copies, because it is easy to lose track of what copy number is for what purpose. To stay in control (pardon the pun), I renamed the controls in the custom card to something more meaningful. I added a _fake at the end of the control copies.

Now it’s really easy to link the Update property of the original data card to the control that is used for data entry. Select each of the original controls and change the Update property to point to the copy of the control.

I did this for the remaining toggle controls. Just for kicks, I applied the same approach on the next row, this time using two custom cards side by side. The left hand custom card contains an image, and next to it, neatly stacked on top of each other, are three controls: a text box, a choice drop-down and a people picker.

Since I copied the controls from the original data cards, all the definitions of the control, for example the choices for the drop-down, were copied with the control and I didn’t have to make any effort to understand how the control must be configured. You could create a control from scratch but copy and paste is a lot easier.

Hide the original cards

The final step for my custom designed form is to hide the original controls. To do that, select each card in turn and set the Visibility property to False.

After that, the cards have disappeared from the canvas and you can only select those cards in the Tree view on the left.

Important: do not delete the original data cards. You still need them, since they have the connection to the SharePoint list. Only hide them, so they don’t mess up your carfully designed multi-column form that now appears to have a large card spanning several rows.

Here is the final form again:

Did you find this useful? Which of your list forms will you re-arrange this way?

I will update this dashboard over the next few weeks, so come back and check how New Zealand is doing.

Click the screenshot to open the Power BI report.

Example:

Cell E2 has an XLookup function to return the value from B2:B4 for a match found in A2:A4. The formula goes like this:

=XLOOKUP(D2,A2:A4,B2:B4)

The cell with the result is empty, so the XLookup function returns a zero. That does not look nice and I’d rather see a “not found” instead of the zero. The approach for that is one that Excel users have been applying for many years: Wrap the formula in an IF() statement, test if the formula returns a zero and if it doesn’t repeat the formula.

This is what that looks like:

=IF(XLOOKUP(D2,A2:A4,B2:B4)=0,"not found",XLOOKUP(D2,A2:A4,B2:B4))

The result shows “not found”, but the formula repeats the XLookup calculation. With very long and complicated formulas, this approach has challenges. The calculation is duplicated, which can impact workbook performance when there are many formulas that use that approach. Also, when the formula needs to be changed, the changes must be made in each duplicate of the copy.

The new LET() function removes both of these complications.

The first parameter is the name of a new variable, MyResult. The variable is assigned a value with the second parameter. This can be a constant, like a number or a text, or like in this case, a formula.

The third parameter is a calculation that can use the variable value.

=LET(MyResult,XLOOKUP(D2,A2:A4,B2:B4),IF(MyResult=0,"not found",MyResult))

It’s also possible to assign several variables, for example like this:

=LET(A,25,B,4,A*B)

As of writing this, the new LET() function is available in Insider builds of Office 365.

Read more details about the new LET() function and how you can get it in this Microsoft support article.

What will you do with the LET() function?

The plan is to start a Do Until loop as a parallel branch to the approval step. The loop runs until a variable value shows “complete”. Inside the loop is a delay, then the variable is checked. If the variable value is “pending”, an email reminder is sent to the approver.

When the approval has been processed, the next action sets the variable to “completed”, so after the current delay is over, the loop will end.

This approach is easy to set up, but it has a few issues.

• The delay period must be completed before the loop can be ended. This can lead to undesired lag. For example, if the delay period is one day, the following can happen: The flow sends a reminder email at, say,  1 pm and a new delay is started. The approver actions the approval at 1:30 pm, but the flow will still wait until 1 pm the next day before the loop is ended and the next action after the loop is carried out.
• If despite all reminders the approval times out, the next actions after the approval don’t run. That means the variable never gets set to “completed” and the reminders keep getting emailed until the flow itself times out.

Let’s see how to fix this.

Shorten the delay without spamming the approver

For the first issue, I’ll shorten the duration of the delay and use a counter variable that gets incremented in each iteration of the loop.  Let’s say one hour instead of one day (24 hours). We don’t want email reminders to go out every hour, though. The approver would certainly not appreciate getting hourly reminders. I only want to send the email once a day, so before I send the email, I divide the counter by 24 (the number of hours I want to wait between emails reminders) and look at the remainder. If the remainder is zero, the counter is a multiple of 24 hours and the email will be sent. The mathematical term for the “remainder of a division” calculation is the “modulo”. Very conveniently, PowerAutomate has a function to calculate that.

mod(variables('varCounter'),24)

I use that expression in a variable of the data type float and then check if that variable is zero. That way I can see the result of the mod() function in the run history after the Flow has run.

So here is what the Do Until loop looks like now.

Caveat! Be aware that the Do Until action has its own limits for how often it runs and when it times out. The default limits are 60 runs and a one hour timeout. If either of these limits are reached, the loop will end and no more email reminders will be sent. You can change the limits to a higher run count and a different time period, though. Just click the Change Limits drop down. In my scenario, my approval will be set to time out after 5 days, so I’ll set the Do Until to the same timeout limit. The duration is written in ISO 8601 duration format, so for 5 days I need to write P5D.  With 24 hrs in a day and hourly delays that means I need a count of no fewer than 120 hours.

Now the progress of the approval is checked every hour and a reminder is sent once a day while the approval is still pending.

Managing the approval timeout

By default, an approval times out after 30 days. It will then vanish from the approval portal page, as if it never happened. The Flow itself also times out after 30 days. When that happens, the Flow registers as “Failed” and conveniently (NOT!!) also disappears from the run history, which keeps only 30 days of Flows.

While you cannot do anything about the Flow timeouts, you can change the approval timeout to something shorter and take action while you are still in control. The timeout can be changed in the Settings of the approval action, which you can find in the three dot menu of the action. Change the duration to the value you want to use and set the Retry Policy to None.

The screenshot shows a timeout interval of 15 minutes, i.e. PT15M, which I used during testing the flow. I later changed it to five days, i.e. P5D to align with the loop timeout.

The next step after the approval is a condition that checks the approval outcome.  That will be either Approve or Reject if the approval is actioned in time, but if the approval times out, this next action step does not run unless it is set up with a Configure run after.  Click the three dots of the condition step and select Configure run after. Now tick two options, i.e. is successful and also has timed out.

The condition action will now be executed when the approval is completed (a Reject outcome is still a successful outcome). If the approval has timed out, there will be no outcome. I can exploit this behaviour with a nested condition in the No branch. In words: If the approval outcome is “Approve”, send an email with the approval news. Else, if the approval outcome is “Reject”,  send an email with the rejection news, otherwise it must be a timeout, so send an email with the timeout news.

This gives me a handle to deal with each outcome differently.

After that, the varApproval is set to “complete” and within the next hour the loop will exit and the Flow can process the actions following the parallel branches.

The nested conditions are not pretty, but this approach works. I tried a Switch instead of a Condition, but the Switch will fail after a timed out approval, since there is no outcome. The Condition handles that scenario much better.

That’s it. We can now send reminders, control the timeouts and process the result of the approval.

Below you can find my presentation and the Excel Form workbook to download and inspect.

DWCNZ Form workbook

Forms Plus Slide Deck

If you want to follow the steps here is the Excel file I used:

Summit Video Pie chart demo

The new features include:

• Collaborate – For Office 365 subscribers, you can edit at the same time with others for workbooks stored in OneDrive and SharePoint.
• New functions – For O365 subscribers, use the new IFS and SWITCH functions to simplify and shorten your formulas.
• Better support for charts with a PivotTable as the data source – charts will update if you update the PivotTable.
• Table Slicers – Add a Slicer to filter your tables quickly.
• New and modern chart types – for O365 subscribers, visualize your data with new chart types, including Box & Whisker, funnel, histogram, Pareto, sunburst, treemap, and waterfall.
• Updated Visual Basic for Applications (VBA) – Some additional methods, properties, and objects available to Windows Office users will now work on Mac as well. There are still differences between the Mac and Windows Object Model, so you will still see “not supported on this platform” for some elements. More details on the Object Model changes will be available soon.

Be aware that Insider Fast builds can still be a little bit unpolished and not everything may work perfectly. But if you try it out and provide feedback with the smiley icon, that feedback will go directly to the Excel team and will get their attention.

Once all people have returned their RSVPs, you can use Excel to organise the replies into a list with two columns: Name and Meal choice. Something like this:

The name of the table is MealOrders.

Now you need to work out how many meals of each type you need to order from your catering company.

You could build a pivot table, but since there is a fixed number of just seven meal choices, we can also just build a simple table with a formula. Place the seven meal choices into seven cells and use a Countif function. Turn that into a table by pressing Ctrl-T or click Insert > Table. (Tables are great for working with data!!)

Something like this: The blue table has the user data, the green table has the count per meal choice.

The formula to calculate how many meals to order (in the green table) is a simple Countif function, which you can see in the formula bar, but here it is if you want to copy it:

=COUNTIF(MealOrders[meal choice],[@[meal chosen]])

In plain words: In the table MealOrders (the blue table), look at the column meal choice and count how often the value of the column meal chosen from the current table (the green table) appears.

So far, so good. You know how many meals of each type to order to keep your GOT team happy, which is, as we all know, not easy at times.

Now the catering company delivers the food. You need to instruct your wait staff to deliver the right meal to the right person. Let’s say you have one waiter for each meal type. How can you work out who has ordered which meal, so you can give each of the wait staff a distinct list of people to deliver these meals to? One option is to sort the MealOrder table by the column meal choice. That will still result in quite a long list and require some scrolling to see all the data.

What if there were a way to list all names for kosher meals in one cell?  If you’ve been using Excel for a while, you know that this may not be so easy, but, …. drum roll ….

— Let me take you on a little excursion —

Excel 2016 with an Office 365 subscription has a new function called TextJoin().  The syntax for TextJoin is

=Textjoin(Delimiter, IgnoreBlank, Range)

Delimiter is a text string. You can specify any delimiter you want, and the delimiter can be more than one character. So, if you want to separate list entries with a comma and a space, then use a comma and a space, like “, “.

IgnoreBlank will take TRUE or FALSE. If the source data has blank cells and you want to ignore these, select TRUE.

Range is the list of cells that holds the values that you want to join or concatenate. \

Here are two examples:

You see that you can use cell references (formula in cell A9, written out in cell A10) as well as Structured References (formula in cell A12, written out in cell A13) like table names and table columns in the TextJoin function. With the IgnoreBlank parameter set to TRUE, blanks don’t feature in the result.

— That’s the end of our excursion. Thank you for taking the time. —

Now, back to our hungry Game of Thrones crowd.

Using TextJoin, we can create a comma separated list of characters for each meal choice in a single cell. But it requires a little twist.

Let’s add a column next to the How many column. We need to feed (pardon the pun) the TextJoin function only those cells that have the meal choice listed in our meal chosen column of the same row.

As the Range parameter, we need to use an IF function. If the meal choice in the MealOrder table is the same text as the meal chosen column in the current row, then return the Name from the MealOrders table, otherwise, return a blank cell.

So the parameter for the Range looks like this:

IF(MealOrders[meal choice]=[@[meal chosen]],MealOrders[Name],"")

This is not a complete formula, just the parameter for the Range. This IF function will return a list (or array) of values, which the TextJoin function can then interpret.  Let’s go back to the unsorted list of orders:

If we apply the IF function above to the meal choice column when looking at “regular”, then the IF function will return an array that starts like this:

{"","Robert","","Catelyn","","","Jorah","Petyr".......}

For every name that sits next to the “regular” meal choice, the list will show the name. For all other meal choices, the list will show a blank.

In the TextJoin function, we’re asking to ignore the blanks when we set the second parameter to TRUE, remember?

=Textjoin(Delimiter, IgnoreBlank, Range)

So that’s what we will exploit for the solution.

In the results table with the column for How many meals we need to order, we can add another column to show who these meals need to be delivered to in a nice, concise, comma separated list. Like this:

Here is the formula:

=TEXTJOIN(", ",TRUE,IF(MealOrders[meal choice]=[@[meal chosen]],MealOrders[Name],""))

Now, here comes the catch. You’ve been waiting for that, haven’t you? It was too good to be true, right?

The catch is that the nested IF function returns an array, i.e. multiple values, to the TextJoin function. This makes the whole formula in cell F3 a so-called array formula. In order to show all results properly, an array formula must be confirmed by holding down both the Ctrl and the Shift keys and then pressing Enter.

This special way of confirming a formula in a cell will enable the TextJoin function to check out all the values that the If function comes up with. If the formula is confirmed with just hitting Enter, then only the first value of the array (or list) served by the nested IF function will be looked at.

You can tell if a formula has been confirmed with Ctrl-Shift-Enter, because if it has, it will be wrapped in curly braces in the formula bar. Check the screenshot. Do you see the curly braces? They are easy to overlook but essential for an array formula to work.

Every time you edit an array formula, you have to remember to press Ctrl-Shift-Enter!!

If you forget that keystroke, all the formulas in the table will deliver wrong results. The wrath of Daenerys will be upon you and her dragons will engulf you in fire. To avoid that, you may want to download the sample file and check out how all this works.

Download the sample file

The background

Something was wrong.

For the last two years, we’ve had a procedure in place that imports event data from System A to System B. Now the users reported that the time stamps from System A were off in System B. All of them. By 12 hours.

OK, my employer’s company is in New Zealand, which means that time zones, regions and locales are a big problem for software that can be very US centric. Therefore, the “12 hours off” pointed towards a possible locale issue.  But we couldn’t really see what had changed in our environment, or when.

We run many different systems from many different vendors, most of them from the US (Looking at you, Microsoft). There’s often a lot of fine-tuning and workarounds involved, so things work in New Zealand. And when the vendor publishes an update, our workarounds may be superseded and become obsolete, or in the worst case, even revert the issue and turn against us. That’s why we suspected that some change in one of our systems was to blame.

But which change? And when did it happen?

The approach

Enter Power Query and Excel charting for some quick ad-hoc analysis.

We knew in December 2016 the imported dates were still fine. We knew by the end of March 2017 the imported dates were off by 12 hours. So, something must have happened between December and March.

There are hundreds of events every day, so just “downloading” a few months worth of data into Excel would probably have caused a major grind-to-a-halt of my machine. But with Power Query, things are different.

I created a connection to the system where the dates were showing “wrong”, which is an on-premise SharePoint 2010 list. With just a few clicks on commands in the ribbon of the Power Query editor, I specified that I wanted data after 1-Dec 2016, and divided the data for date and time into different columns.

With that data in my spreadsheet, I created an XY Scatter chart with the dates on the X axis and the time on the Y axis.

Our business is 24/7, but I know that most of our events are during the day, so I expected a pattern of data points between 6 am and 6 pm. And sure enough, that is what I saw when I filtered the data for December:

That looked pretty normal. Each event is a dot. Most events happen during the day.

I changed the filter to January, then to February. The pattern stayed the same. Then I changed the filter to March and saw this:

Gotcha! The pattern changed after 15-March.

Now we know WHEN the change happened and we can consult our change management system to find out more about the exact changes that were performed on our systems that day and take steps to fix the problem.

That’s what I call “data squinting”.

Sometimes you don’t need a perfect chart, as long as you can identify a pattern by just glancing sideways at the data.

Setting up the data with Power Query was a breeze.

Before Power Query, I would have had to create a view in SharePoint first, with all the trouble you have when a list has more than 5,000 items. Then, after exporting the view to Excel, I’d have to use Excel formulas to split date and time into two columns. With that many data points, I’d be looking at some calculation time.

With Power Query, I can quickly set up my filter parameters by clicking a few commands, then split out date and time into different columns and load the query.

Then set up a scatter chart and off we go data squinting.

Have you used Power Query for a quick ad-hoc analysis like that? Let me know what you found.

I’m excited to announce that I will be sharing my favourite Excel tips at CPA Australia’s newest conference, Unlock Excel, happening in Melbourne, Brisbane, Sydney and Auckland this May.

In conjunction with SumProduct, Unlock Excel is bringing together myself and six of my fellow Microsoft Excel Most Value Professionals (MVPs) to unlock the secrets, new spreadsheet features and tips on how to become an Excel master over two captivating days.

There will also be an opportunity for you to get the answers to your most challenging problems direct from the Microsoft’s Excel developers and to hear what the next version of Excel will include!

Tickets on sale now. See you at Unlock Excel.