Overview of SharePoint Virtual Summit 2019

In this article I’ll put short glimpse of what has been announced on SharePoint Virtual Summit 2019. Be aware that some of following features may be still in preview but are going to appear later this year. I skipped features that has been announced on Microsoft Build 2019

flow SharePoint Document Libraries

  • Build custom forms using PowerApps
  • Microsoft Teams will gets enriched metadata experience
  • Bulk Actions

    This feature was requested for a long time. And now here it is: you can select multiple items/document and take an action for all of them: update properties, download, delete, approve, move etc.

  • New Flow actions

    There are scenarios in which you need to check-in/check-out documents, get version information, grant access or create folders as a part of larger business process in Flow. Until now you could do that only by calling SharePoint HTTP REST API. But from now on you can do all of above simply by using Flow actions!

  • File request feature

    This is a bomb! You will be able to request files from other users directly from the place where you store your files! The recipients will get email with link. Once they click on it they will see consistent UI with built-in files upload.

  • Organization document templates

    governance becomes even easier!

flow Collaboration

  • Organization Home site

    It’s a communication site with some extra superpowers. It searches for data tenant wide, mark site news as organizational news, enables special SP mobile app. For me it completes perfectly org-wide Teams team. Just add tenant Home site as a tab in an Ord-wide Teams team

  • New page designs
  • New webparts – i.e. Yammer!
  • News links
  • Audience targeting
  • Sync Microsoft Teams files to you PC or Mac
  • Teams apps in SharePoint sites
  • Enhanced SharePoint list experience embedded in Teams
  • Enhanced co-authoring acroos mobile, web and desktop versions of Word, PowerPoint and Excel (only for Office 365 and files in the cloud)

flow Governance

  • Rename your SharePoint site (including its URL)
  • Replace the root site within a tenant

    you’ll be able to build a new org site on the side and once completed it swap it with a root site…it couldn’t be simpler.

  • Create SharePoint list using excel file with an option to configure columns types!
  • Share & Forget with external access expiration
  • File restore for SharePoint just as it is already for OneDrive
  • Sensitivity labels

    the mechanism known from Azure Information Protection is now being supported by SharePoint and OneDrive (and it is called Microsoft Information Protection)

  • Decide where your data related to SharePoint and O365 groups will be stored using multi-geo capabilities

flow Search

  • Customizable Search to rule them all!

    The same search experience will be shared across any Microsoft platform. Top, middle, search. Oh and there will be available to add custom verticals, custom refiners and custom display templates! What is more you will be able to search for conversations from Yammer and Microsoft Teams in any search endpoint!

  • Search in Office

    Discover your network of apps, files, folders, people, organization charts, SharePoint sites, site pages, lists and list items

  • Search in SharePoint

    Catch up on news and announcements. Find the sites that are relevant to you without scrolling through endless bookmarks. Pick up on that shared document you were working on

  • Search in OneDrive

    Discover relevant information to help you get work done where you’re working through intelligent results and sophisticated refinement

  • Search in Windows

    Search right from your Windows desktop. This way you can search not only inside your local files but also in Office365, person in organization with smart suggestions based on the people you work with the most

  • Administer your Microsoft Search

    Control organization search using provided powerful admin center and manage all of Microsoft Search endpoints!

flow Misc

As you can see SharePoint is not stopping in getting new capabilities. But what may not see and I have to tell you – many of above changes came from UserVoice. UserVoice is a forum where everyone can submit a bug, an idea or new feature request and Microsoft will implement it if only the post gets enough amount of community support (represented by likes). Changes presented on SharePoint Virtual Summit 2019 are the best prove that Microsoft is listening to its users!

How to overcome 500 items limit in PowerApps

I planned to publish another topic for today but I decided that this one will fits better to the post I’ve published a week ago.

So if you work with PowerApps connected to ANY data source you had to experienced or at least read about 500 items limitation. In this post I will show you 6 ways how you can exceed that limitation.

Limit? What limit?

If you’re one of those happy devs that have never heard of such limit I’ll make quick reminder:

In PowerApps every data source (SharePoint, Common Data Service, OneDrive) is under limitation of 500 items. It means you cannot get more that 500 items from a data source but even more than that – PowerApps won’t even “be aware” of any rows above 500.

Imagine simple case: On your OneDrive for Business you have an excel file with ~600 rows. Even though you need only last 10 rows you will get rows 490-500 instead of 590-600. As I told you in previous paragraph – it’s not the limit of total fetched items. It’s the limit of items that PowerApps knows about.

But before you think that’s a killer for PowerApps and “oh those architects, they’ve never use their tools so how they can design something that actually works” let’s imagine another example:

You have an excel with 2mln rows (yep, I saw over 2GB excel file in one company from financial sector). One of app users opens your app and…probably in an eyeblink you will get tons of emails that your app is not working (but of course it is working but the loading time is huge). That’s why this limitation make sense.

Anyway there are many occasions where you will need more than 500 items. Maybe not 2mln but a simple task list for a company with ~1000 employees can easily generate around 50000 rows in total. So how you can overcome this 500 items limitation in PowerApps? In following sections I describe 6 options in the order from easiest/fastest to the most powerful (but also time consuming to setup):

  1. Increase the total limit items you can fetch
  2. Use static data
  3. OneDrive for Business connector specifics
  4. Use delegation
  5. Use delegation + iterative function
  6. Combine PowerApps with Flow

1. Increase the total limit items you can fetch

Okey so this method is the easiest and really quick to setup. Click file (top left corner) > App settings > Advanced settings > set value for non-delegable queries.

This method has one hard limit (limitation of the limitation) – 2000 is a maximum value you can set which means you can’t get more that 2000 items on the same rules as for 500 items (PowerApps won’t know about 2001 item). So if this method does not work for you let’s move to option 2.

2. Use static data

In some specific cases static data may be the best solution. Such data can be imported to your app and will be kept within assets of your app. You can have 10000 rows and still users will have access to all rows. However remember following notes:

  1. Static data are static – you cannot modify it from within PowerApp. But for some scenarios it may still do the job i.e. you build company travel app and you want to have index of all countries in the world (192) along with bigger cities (~30 x 192=5760) so your users can search and select to which city they’re traveling to. Countries and cities typically don’t change dynamically so we can freely import them as static data instead of using SQL database (for which you have to spend extra cash).
  2. Static data are attached to your app which means they’re enlarging total size of app which may affect app loading time.

If you still think static data is a best choice for you here is how you can add it:

  1. View > Data Sources > Add data source
  2. Click Import from Excel
  3. Select excel file and a its table that you want to import

Ok, now, what if this option also doesn’t suit to your needs: Excel is good but you need to be able to modify it from within app so all app users have access to same data. So you googled and decided to use OneDrive for Business data source connector. It uses excel file as a container which data can be modified, sounds perfect, right? So here is the deal…

3. OneDrive for Business connector specifics

Accessing Excel files using OneDrive for Business connector does not support delegations (I’m explaining delegations in section 4). Long story short here are the implications:

  • You can’t get more than 2000 rows
  • 2000 is the total number of rows despite of the number of tables or worksheets in excel file
  • If you have more than 2 tables, O4B connector firstly access all rows from Table1, then from Table2, then from Table3 and so on until reach the number of items equivalent to value for non-delegable queries.

So if you know you may need more than 2000 rows stored in one data source you should switch from OneDrive for Business to any other like SharePoint, Common Data Service, SQL etc. and take use from delegable queries. What are these? Let’s see.

4. Use delegation

Delegation is a mechanism to access all data from a data source in a performance friendly manner.

Speaking a bit more clearly it’s a situation when your PowerApp app says to a data source:
“Ok, you know what, I need items that match these conditions but hey, can you do all the computations by yourself? I need to use my network bandwidth, memory and CPU power for something else…I just need results.”

Now, there are 3 catches:

  1. All results are fetched in a maximum of 100 items bundles. The next bundle is being fetched once a user scroll to the end of a gallery/table list (check out delegation demo gif down below)…
  2. …which means if you need to fetch more than 100 items (! not process but fetch. You can process 10000 of items but fetch only 15 of them as result) you can only use gallery or table. Collections are not supported and works under non-delegable queries limitation (Collect() or ClearCollect() functions breaks delegation!)
  3. Your query need to be supported by the data source. Figuratively speaking the data source must understand what PowerApps app is saying to it (check out example queries on the image down below). To complicate it a bit more not every data source supports all functions and operations – each data source connector documentation outlines delegable support (i.e. here you can find SharePoint Online delegation support). For better understanding of delegations in PowerApps I recommend you to read delegation documentation.
Delegation demo. Left gallery is sourced by OneDrive for Business (which is non-delegable). Right gallery is sourced by SPO list with 500+ items.
Look what happens once I scrolled to the bottom of the SPO items gallery.
Example delegable and non-delegable queries in PowerApps

Ok but what if need more than 2000 items here and now? There are 2 options for doing that. But before you read them please consider below:

Following approaches should be treated as potentially bad practices and should be used only under specific circumstances and with proper caution since they may have bad impact on your application performance or other O365 tenant services. It’s like with medicines – they can solve your problems but harm you in wrong dosage or used inadequate to needs. So in 95% of cases delegation will perfectly do the job. It may requires read & learn a bit but trust me – it’s easier than struggling with performance issues that may appeared if you misuse options 5 and 6.

Ok, so now we can safely move to options for those 5% of cases 🙂

5. Use delegation + iterative function

General idea is like this: for a delegable query build a loop and in each iteration filter data chunks. I.e. in 1st iteration you get rows 1-500, in 2nd 501-1000 and so on.

I won’t explain it in detail or show you code snippets because this method is not mine. Its author is MS employee Brian a.k.a Mr.Dang() and I would feel bad if I get his credits for this workaround. Read it and just in case it won’t meet your needs…

There is also another way of which I’m the author. Option 6. Here it comes.

6. Combine PowerApps with Flow

The last option is to use Flow as a middle man that was asked this:
“Dear Flow, since your data source connectors don’t care about this whole delegation stuff, can you please do me a favor, get all rows, join them in 1 big string and send me back please?”

A simple demo with excel and outlook email may look like this:

To make this work for more than 256 results I had to make few configuration tweaks of the “List rows present in a table” action:

Fetching 682 rows took 3 seconds.

Fetching 2101 rows took 11 seconds so that may be an issue.

However if you use SharePoint as a data source I have a good news for you – I’ve made a demo that fetches 1000 items in 2 seconds. In this blog post I describe step by step how to:

  • Build a PowerApp from scratch
    • Add input fields
    • Pass input fields values to Flow
    • Parse results from flow
    • Display all results in a gallery
  • Build a Flow from scratch
    • Get parameters from PowerApps
    • Integrate Flow with SharePoint
    • Send all results back to PowerApps

And that’s it! I hope you enjoy this blog post. If so please let me know in the comments down below. If not or maybe you know a better solution – let me know as well!

Performance – is Flow faster than PowerApps?

In this post I’ve showed you how you can create PowerApps app that utilize SharePoint Search for your business. I used there PowerApps for parsing big string containing our results from SharePoint Search.

But one o my readers asked me an interesting question

“Mike, wouldn’t it be easier and faster if we parse results in Flow and pass to PowerApps just raw field values?”

Jacek M.

Hm….at that time I didn’t know the correct answer. Neither I could find it in the PowerApps Canvas App Coding Standards and Guidelines . That’s why I’ve decided to make quick test on my own and publish its results on my blog.

Time measurement 1: parsing on PowerApps app side

It’s pretty straight forward to measure parsing time for the application we’ve built in my previous post. Just add timer control, start the timer before firing flow, fetch results from flow, parse it and stop the timer right after it. It will look like this:


I’ve setup my test to fetch 10 results. Result of it: 1 second! 1 second is the time that PowerApps need to fetch 10 results from flow and extracting required field values (Title in this case). But that was not enough to me. I thought: “let’s see how long will it takes to get 100 results and extract 4 fields”. The results surprised me. 1 second again!

3 of 100 fetched items from SharePoint Search via Flow. PowerApps extracted 4 fields from received string: Title, URL, Ranking Sum and UID of the item.

It will be very hard to beat it but let’s give it a shot! Let’s check how Microsoft Flow will manage to extracting field values.

Time measurement 2: parsing on Flow side

First let’s start from small test that is fetching 10 items and only Title field. The modification of the flow is simple: I will iterate through each row of the SP search results, then through each cell in a row (each row contains multiple cells) and then once I find the cell I’m looking for (Title in this case) I’ll append cell value to an array variable. Modified flow will look like this:

Parse Cell action allows for using Key, Value, ValueType attributes in farther actions. The Join action that is in the bottom of the screenshot is used to convert array to text value (because PowerApps does not accepts arrays 🙁 ).

For start I will just take 10 results and extract only Title field. Let’s test this configuration:

WHAT?! 32 seconds!? I must admit – I expected that Flow will be slower that PowerApps but not ~30 times (it’s obvious why it’s a common pattern to use your end user CPU time, via browser or mobile memory, rather than your own) . That’s huge difference and strong argument for using PowerApps for all kind of parsing and any CPU expensive calculations and not using Flow for that.

Further testing (more fields, more items) has no point. Let’s jump right to the conclutions.

Conclusions

Personally I find this small test really informative.

First: It’s more efficient to use PowerApps for calculations that Flow. It’s worth to tell it even if most of us know that common good practice is to distribute calculations and delegate it to user device.

Second: In the light of above note, consider making more than 1 call to Flow and back to make most of the expensive calculation on the PowerApps side for the sake of whole process time consumption. E.g.:

  1. PowerApps asks Flow to get results from SPO Search
  2. Flow get results and pass it back to the app
  3. PowerApps extract some data (specific fields, values range, values sum etc) and pass it back to Flow
  4. Flow gets additional data for selected items or fields and pass it back to PowerApps again
  5. PowerApps display results to user
Check how I can help you or contact me.

What do you think about that? Does this test was also informative for you? Or maybe I missed something that may change whole results – let me know!

Search in SharePoint from PowerApps

Inspired by this post I realized that built-in connectors does not allow to utilize SharePoint Search. So theoretically it is not possible to use SharePoint search from PowerApps. However using Flow as a middle layer you can call SharePoint Search and parse results for your needs. Let’s see how to do that.

Why you may find SharePoint Search useful in your app?

Let’s consider is it worth to have such possibility as using SharePoint Search from your PowerApps app. To answer this question let’s see what we can and cannot do using built-in connectors.

You can:

  • Get content of any list or library in a single SharePoint site
  • Read, Update, Delete elements of list or library
  • Use specific subsets of items (filtering, sorting) in delegate manner
  • Get content of Office 365 Groups
  • Get OneDrive content (only excel files)

You cannot:

  • Find all items of a specific Content Type across whole tenant
  • Get elements from list or library in a site – even if the list or library was created after the connector has been declared
  • List SP sites in your tenant
  • Get OneDrive for Business content (all kind of)

All above are limitation coming from OOB SharePoint connectors. So if you want workaround them this post will tell you how to achieve it.

PowerApp SharePoint Search Architecture

We need 3 components:

  • PowerApps: acts as front-end, provides screen and controls to user and also display results,
  • Flow: acts as back-end, call SharePoint search (also keeps SPO url) and pass results to PowerApps
  • SharePoint: contains all the data of course

Those components will communicate each other in following manner:

  1. PowerApps uses Flow connector to make a call to it
  2. Flow uses “Send an HTTP request to SharePoint” and then…
  3. …parse returned result to JSON
  4. Flow join all rows as string variable and pass it as to PowerApps as respond
  5. PowerApps filters out the data it needs

Let’s start building it! To not make this post too much long I’ll focus only on most important part skipping parts like header creation or pagination functionality.

Follow this link to download complete demo app (remember that url to SPO is in the flow)

Step 1: Create a PowerApp

First we need to create an app, add screen to it and add controls to the screen (if you don’t know how to do any of those please check out this post). Some parts that may need additional explanation:

  • numRowLimitNumber – number of items to return
  • btnCallFlow – this is invisible button that call Flow on OnSelect event. Other controls (like previous/next page buttons) can fire it using Select() function instead of duplicating code in it.
  • galSPSearchResultItems – gallery displaying results items
    • imgOpenInNewWindow – clicking it will open SharePoint item in browser
    • lblSPItemTitle – displays item Title
    • lblSPItemUniqueId – displays item UniqueId
    • lblSPItemRank – displays item ranking sum. Because why not 😉

Now let’s configure behavior of our controls.

  • On OnSelect action of the “Search” button put as follow:
Set(gblItemsToSkip,0);
Set(gblPaginationVisibility,false);
Select(btnCallFlow)
  • As you can see it fires btnCallFlow. On OnSelect action of that button we will call the flow
Clear(colSPSearchResultItems);
Set(
    gblRowLimitNumber,
    numRowLimitNumber.Text
);
Set(
    gblSearchResults,
    Searchforitemsintenant.Run(
        txtSearchPhrase.Text,
        numRowLimitNumber.Text,
        Text(gblItemsToSkip)
    )
);
<HERE WE WILL PUT CODE TO PARSE SP SEARCH RESULTS>
Set(
    gblPaginationVisibility,
    true
)

Now let’s add flow to our app. To do that click:
Action tab -> Flows button -> “Create a new flow”

Step 2: Create a Flow

On Microsoft Flow side add variables that will keep input parameters from PowerApps. Then add “Send and HTTP request to SharePoint”.

The Uri attribute contains function that replace apostrophe (‘) to empty sign in case user put ‘ in the search phrase (accidentally or intentionally) which will break the query:

_api/search/query?querytext='@{replace(variables('SearchQuery'),'''','')}'&clienttype='ContentSearchRegular'&selectproperties='UniqueId, Title, OriginalPath'&rowlimit=@{variables('RowLimit')}&startrow=@{variables('RowsToSkip')}

Next add Parse JSON with Body output in Content field and a Schema.

The schema however is a bit tricky because using sample (i.e. from search REST call using browser or Postman) may not include all variations of results (that was my case). So after quick debugging I’ve successfully created following schema – feel free to copy-paste it.

{
    "type": "object",
    "properties": {
        "odata.metadata": {
            "type": "string"
        },
        "ElapsedTime": {
            "type": "integer"
        },
        "PrimaryQueryResult": {
            "type": "object",
            "properties": {
                "CustomResults": {
                    "type": "array"
                },
                "QueryId": {
                    "type": "string"
                },
                "QueryRuleId": {
                    "type": "string"
                },
                "RefinementResults": {},
                "RelevantResults": {
                    "type": "object",
                    "properties": {
                        "GroupTemplateId": {},
                        "ItemTemplateId": {},
                        "Properties": {
                            "type": "array",
                            "items": {
                                "type": "object",
                                "properties": {
                                    "Key": {
                                        "type": "string"
                                    },
                                    "Value": {
                                        "type": "string"
                                    },
                                    "ValueType": {
                                        "type": "string"
                                    }
                                },
                                "required": [
                                    "Key",
                                    "Value",
                                    "ValueType"
                                ]
                            }
                        },
                        "ResultTitle": {},
                        "ResultTitleUrl": {},
                        "RowCount": {
                            "type": "integer"
                        },
                        "Table": {
                            "type": "object",
                            "properties": {
                                "Rows": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "Cells": {
                                                "type": "array",
                                                "items": {
                                                    "type": "object",
                                                    "properties": {
                                                        "Key": {
                                                            "type": "string"
                                                        },
                                                        "Value": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        },
                                                        "ValueType": {
                                                            "type": [
                                                                "null",
                                                                "string"
                                                            ]
                                                        }
                                                    },
                                                    "required": [
                                                        "Key",
                                                        "Value",
                                                        "ValueType"
                                                    ]
                                                }
                                            }
                                        },
                                        "required": [
                                            "Cells"
                                        ]
                                    }
                                }
                            }
                        },
                        "TotalRows": {
                            "type": "integer"
                        },
                        "TotalRowsIncludingDuplicates": {
                            "type": "integer"
                        }
                    }
                },
                "SpecialTermResults": {}
            }
        },
        "Properties": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "Key": {
                        "type": "string"
                    },
                    "Value": {
                        "type": "string"
                    },
                    "ValueType": {
                        "type": "string"
                    }
                },
                "required": [
                    "Key",
                    "Value",
                    "ValueType"
                ]
            }
        },
        "SecondaryQueryResults": {
            "type": "array"
        },
        "SpellingSuggestion": {
            "type": "string"
        },
        "TriggeredRules": {
            "type": "array"
        }
    }
}

At the end, we just have to join rows (using some special separator like “/n” which is unique enough) and pass it back to PowerApps along with totalRows number

If you did everything properly your Flow should look like this

Now we can get back to PowerApps and parse the results.

Step 3: Parsing results in the PowerApps

Flow pass result to PowerApps in a string field (called “results”). But we cannot use it in a gallery right away. Just look at it:

{"Cells":[{"Key":"Rank","Value":"17.1283779144287","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461461","ValueType":"Edm.Int64"},{"Key":"Title","Value":"Michał Guzowski Team Site","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595142086123","ValueType":"Edm.Int64"},{"Key":"Title","Value":"pl","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595383574480","ValueType":"Edm.Int64"},{"Key":"Title","Value":"en","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595390074495","ValueType":"Edm.Int64"},{"Key":"Title","Value":"fr","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412154536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"da","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595412284536","ValueType":"Edm.Int64"},{"Key":"Title","Value":"de","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595451564436","ValueType":"Edm.Int64"},{"Key":"Title","Value":"sv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595498094501","ValueType":"Edm.Int64"},{"Key":"Title","Value":"lv","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0755615234375","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17595513184435","ValueType":"Edm.Int64"},{"Key":"Title","Value":"ro","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}"/n"{"Cells":[{"Key":"Rank","Value":"17.0754928588867","ValueType":"Edm.Double"},{"Key":"DocId","Value":"17594717461464","ValueType":"Edm.Int64"},{"Key":"Title","Value":"How To Use This Library","ValueType":"Edm.String"},{"Key":"PartitionId","Value":"2037f0e8-05e7-4df7-bbe9-b8cf7c8af72a","ValueType":"Edm.Guid"},{"Key":"UrlZone","Value":"0","ValueType":"Edm.Int32"},{"Key":"Culture","Value":"en-US","ValueType":"Edm.String"},{"Key":"ResultTypeId","Value":"0","ValueType":"Edm.Int32"},{"Key":"RenderTemplateId","Value":"~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_Default.js","ValueType":"Edm.String"}]}

How to extract specific parts of that?

  • Split string with our special delimiter: “/n”
    • For each string part we split it once again using following scheme:
      • “_FieldName_”,”Value”:
      • I.e. for Title field it will looks like this “Title”,”Value”:
      • Also we need to encode quote mark (“) so example after encoding looks like this: Char(34)&”Title”&Char(34)&”,”&Char(34)&”Value”&Char(34)&Char(58)&Char(34)
    • We take second element (above split always returns only 2 elements) and split one more time using following scheme:
      • “,”ValueType
      • After encoding it look like this: Char(34)&”,”&Char(34)&”ValueType”
    • We take first element (above split always returns 2 elements) and add it as value of a specific column.

Wow, felt a little dizzy? Don’t worry. Just copy below code, paste it in your app and replace name “Title” with field name you want to extract.

ClearCollect(
    sposearchitems,
    AddColumns(
        Split(
            Items,
            "/n"
        ),
        "Title",        First(Split(Last(Split(Result,Char(34)&"Title"&Char(34)&","&Char(34)&"Value"&Char(34)&Char(58)&Char(34))).Result,Char(34)&","&Char(34)&"ValueType")).Result
    )
)

In my case (for fields UniqueId, Title, OriginalPath and Rank) my parse operation looks like this:

Step 4: Display results

Finally! We made up to this part. Just set the values of labels in the gallery and enjoy the results!

For me the final result looks like below but your can be totally different (and better than mine ;] )

THAT’S IT! Wasn’t that hard, was it? 😉 If it was, remember that you can download my Demo SharePoint Search PowerApps application to compare or just copy paste some parts – just click here!

Hope you find this post useful. If so please share it and comment if you like.

Thanks and have a great coding 🙂

Update 22.02.2019 – If you’re curious if you can use Flow for parse computations I’ve done such comparison in my next blog post.

26.02.2019 – O365 User Group PL Warszawa #1

Breaking news: I will be a speaker on the first O365 User Group PL Warszawa meeting. Woohoo!

I will tell about how to build smart app that enthused MVPs and Microsoft employees around the world (also known as “the story of 1 tweet” 😉). But I’m just a shadow of the others: hosts Michał Słowikowski and Kamil Bączyk and another speaker: Microsoft Teams Product Group representative: Jace Moreno !

O365 User Group PL Warszawa #1

Tuesday, Feb 26, 2019, 6:00 PM

Microsoft Polska
al. Jerozolimskie 195A Warszawa, PL

57 Członkowie Attending

Zapraszamy na inauguracyjne spotkanie grupy Office 365 w Warszawie. Jako, że mamy wyjątkową okazję to sesje i agenda również takie będą. Podczas tego spotkania poruszymy tematy związane z platformami Teams oraz PowerApps. Agenda: 18:00 – 18:15 Intro 18:15 – 19:05 Unleashing the Power of the Microsoft Teams Platform 19:05 – 19:15 Przerwa 19:15 – 2…

Check out this Meetup →

Enable Whistleblowing in your Office365 tenant with PowerApps

PowerApps can participate in organization modernization in many ways. And I’d like to show you some example. Imagine following scenarios:

  • You’ve seen how a colleague sends confidential documents to his private email. Or…
  • By accident, you heard two colleagues talking about “dorabianiu” at the expense of the health of patients by ordering unnecessary radiological examinations. Or…
  • In the network, one of your colleagues has published an offensive text that hits the brand of the company in which you work. Or…
  • You and a few colleagues from work are the object of mobbing and microaggressive behaviors

I hope you don’t know what I’m writing about but unfortunately many of the above situations really happened: example1, example2, example3.

Such incidents may put significant questions marks on your organization reputation, reliability and honesty followed by financial penalties depending on the seriousness of the offense. So to protect your organization Microsoft provides multiple different tools such as Azure RMS (Azure Rights Management), DLP (data loss prevention) and retention policies. But non of those tools gives you an easily accessible way to pass on information concerning wrongdoing in safe and anonymous manner. That inspired me to create a solution that will fill the gap.

Enable whistleblowing

72 per cent of Canadian survey respondents recognize cyber crime as a risk, many still don’t fully understand the potential impact a cyber breach can have on the business

Financial Post article

The original article of the above quote also noticed that enabling whistleblowing allow for early identification of issues and is critical for ability to manage risk. That inspired me to create a solution integrated with O365 that will respect user anonymity – at least on the data access level.

I’m a huge fun of PowerApps but unfortunately they do not allow for guest access (at least not yet! 😉) and I was wondering if that’s hard limitation. Maybe there is some workaround? There must be. And with a small help of Microsoft Flow I was able to create a solution that:

  • Allow for anonymized creation of new submission
  • View, Edit ones submissions in anonymized manner
  • Correspond with assigned admin in anonymized manner
  • As Admin you can access all submissions as well as filter submissions by status (i.e. only those submissions that waits for your action)

Power Whistleblowing app – user view

This is animated view – If gif is not animating click here

Another view (this time just a screenshot)

Power Whistleblowing app has also an admin view

This is animated view – If gif is not animating click here

Power Whistleblowing architecture

The architecture of above solution is simple:

  1. PowerApp gets information from a user and pass to flow. On this stage everything is personalized. We know who send what.
  2. Flow pass over HTTP request to another flow with parameters of newly created item (for submission it’s: Title, Description, Category; for Comment it’s: Author GUID, Submission GUID, Comment). This is the moment where we lost all context information (except data that are essential for the business logic) and imply anonimization
  3. For newly created submission we need to generate Author GUID (I’ll explain later what is its role) and pass it back to first Flow.
  4. For newly created submissions the Flow expects the Author GUID and pass it back to PowerApps app
  5. Both for new submission and new comment all information are saved in SharePoint Online impersonated as Service Account. We don’t know who is original creator of the record, we only have some Author GUID

Why do we need Author GUID?

Author GUID is the new credentials for submitter to:

  • Check status of his submissions
  • View all his submissions
  • Leave a comment in any of the submissions (to correspond with the admin)

I find this solution really useful for an organization – what do you think? Leave me a comment! Oh and also feel free to ask freely on any other topics i.e.:

  • How to create HTTP connected Flows
  • Is it possible to build admin panel with vertical tabs (yep, it’s tricky 😉)
  • How to build Regular Expression to check GUID cohesion

I don’t bite but do drink beer. You can also catch me on my fb, twitter, linkedIn or PowerUsers forum.

Just in case you want to:

  • Deploy this solution on your environment
  • Customize it for your own needs
  • Create new solution based on this one

Contact me and I will help you

…Oh, and I have a small gift for all of you that read until now – you can download this solution here. Sharing is Caring. Enjoy!

How to trigger Microsoft Flow in SharePoint List

In this article I’ll show you how you can quickly and easily trigger Microsoft Flow in your SharePoint list. I’ll cover following topics:

  • Manually triggered Flow for selected item on list
  • Automatically triggered Flow for created or updated item on list

Manually triggered Flow for selected item on list

For this case I’ve created a simple custom list on my SharePoint site. The use case I want to cover is to copy selected item with a new title. My list looks like this:


I will walk you through the whole process but as you can see on this gif it takes few seconds in total to complete the whole integration


Then I click Flow > “Create a flow”

That will show a panel on the right side of the screen with multiple different ready-to-use examples of a flows for your site. I strongly recommend to play with them in a spare time. But since we want to build our custom one flow, let’s click “Show more” button and on the far bottom pick a template with the name “Complete a custom action for the selected item”

This will open new tab with an information page about the template and connectors it uses. Click “continue”.

The flow is as simple as our list: a trigger (for a selected item) and an action (Get item). Click “Edit” to view more details about the action and then “+ Add an input”.

From here you can add multiple different input types (of course followed by specific field behavior and validation). For provisioning new item title we need “Text” field.

A new parameter row will appear under List name dropdown. Remember to change the name and description of the input field since those will be displayed to your user

Great! Now let’s add a “Create item” SharePoint action. To do this you need to:

In the search box write “Create item” and pick item with “SharePoint” icon

That shows a new action block. Fill the Site address and List Name. If dropdowns does not show the values you’re looking for click “Enter custom value” and then(!) provide the name manually

After selecting a list wait few seconds for the fields to load – that shouldn’t take too long since there is just one field.

Once your custom list fields will be loaded click on the Title field. On the right side scroll down to the “For a selected item” section (it’s the name of the trigger so if you changed its name it may be different) and select the field name we’ve added (“New Title” in my case)

Now select “Copy this value” field, and in the right panel in the “Get item” section select “Copy this value” – this will get the value from the “Get item action” (which contains original item) and put it in the “Copy this value” field of your new item.

Last thing is to put a descriptive name of our flow. Click on its name in the top-left corner and change it.

Hit save and go back to your list. Now when you select your item (and only then) a new flow action will be displayed. TEST IT 🙂

Automatically triggered Flow for created or updated item on list

For this case we’ll send an email notification as soon as someone creates new item on this list. Of course this might not be very useful and such integration could quickly filled up our mailbox. But it’s simple to imagine some real examples like:

  • Send a mobile notification if the field “accepted” has been set to true manually
  • As soon as someone upload non-english document to the library translate it right away to english
  • Send an email to supervisor if someone delete file from “Archive” library
  • And so on…

Back to work! So first on your list view click Flow > Create new flow > “show more” (you know the drill from previous section) > select template “When a new item is added in SharePoint, complete a custom”


Add an action just like in the previous use case but this time pick “Send me an email notification” from the actions dropdown menu

In the action fields put a proper subject message and body message. Please note that in the body field we mixed normal text (just write it) with fields from the item (pick them from the right panel)

Also this time remember about giving descriptive name to the flow title

Now you can test it!

Please take a note that using notification will notify only creator of such flow. Like in the example screen: even though serviceaccount user has created an item only me (that is Michał Guzowski) got the email notification

Epilog

Remember that in SharePoint Online (or even SharePoint On-premise if you use data gateways) you can significantly extend functionalities of your site or list by integrating it with other ready-to-use services of your Office 365 tenant. There are multiple of them and each one can be configured without writing a line of code! Some of those are:

  • Microsoft Flow
  • PowerApps
  • PowerBI
  • Planner

If you’re not afraid of coding you can also take a look on these:

  • Columns conditional formatting
  • Site Scripts
  • SPFx (SharePoint Framework) apps
  • PnP Provisioning framework