Blog

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.

PowerApps 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

62 Członkowie Went

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 – 20:…

Check out this Meetup →

Enable Whistleblowing in your Office365

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

To CDS or not to CDS: that is the question

Common Data Services is an interesting service that is in the hands of Flow, PowerApps and PowerBI. Today I read an interesting blog post by Pieter Veenstra that inspired me to write this post.

What is Common Data Service?

CDS is a service that allows you to store data in a relational model integrated with Dynamics365 (because it comes from it), but also (and maybe most of all) PowerApps, Flow and PowerBI. This service allows you to securely store and manage business application data from the cloud. You can read more about CDS here.

For me, however, CDS is a defective relational database. In fact, we can connect to it from the level of the above platforms, but its management is unfortunately very limited:

  1. No access to the server on which this database stands. So we can not add resources to make it run more efficiently, as we can do for SQL server (adding space / CPU / memory) or DTU for Azure SQL Db.
  2. No access to data container. So, we can not, for example in the case of the SQL database, optimize its operation by changing the auto-shrink setting, reorganize the indexes in tables, etc.
  3. Limited access to the created OOB entities. That is, we can do everything (CRUD) with Custom entities and Custom fields, but with Standard entities / fields it is not so easy. We can expand them, but you can not remove them.
  4. Many functionalities are accessible only through API. So if you have not coded in C # or WebAPI or have already forgotten how to do it, you will not configure functions such as auditing entities or fields unfortunately. However, if it does not scare you, I recommend this part of the documentation explaining how to work with the API.
  5. CDS does not convince to itself in terms of efficiency. At the beginning of this post I referred to the Pieter Veestra’s post , who made a very interesting comparison of the performance of the Read-Write operation between the CDS and the SharePoint list on his blog. In this comparison CDS not only was slower, but also had a limit limited to 5,000 items on the list.

It’s worth to use CDS anyway!

Reading the above 5 points one can gets the impression that it is best to quickly forget about Common Data Services and focus on other data sources. But it does not have to be that way. What is on the one hand a nuisance to CDS is simply its characteristic feature on the other.

CDS is available OOB and is not intended to be another data source that requires configuration. It’s here, ready to be used just like that. Now. As it is. Simple and handy relational database(s).

And what about its performance? Remember that GA of the Common Data Services took place in November 2016. It was then a very basic product (I would say the beta of the MVP of the product) without the possibility of creating complex relationships between entities or defining option sets (which is possible from January this year). In this light, CDS is still a relatively young product. Observing the way of launching new products by companies such as Microsoft, and also having myself some experience on the startup market, I know that no serious company will take risk of making complete and final product from the start, and the process of maturing the solution takes at least a few years. The requirement to indicate the direction of development of a new product, repair its defects (eg efficiency) and confirmation of its actual usefulness is … using it by customers!

Use it consciously

Right, “use it consciously”. But what if the product is still not mature and its configuration is uncomfortable or impossible? Answer is: Use it wisely. Use the Common Data Service taking into account its advantages and disadvantages. Use wherever it is possible (and there is customer approval for extra licenses – Plan 1 for applications using CDS, Plan 2 for managing CDS entities). Only in this way can we influence its development. CDS will certainly be further developed, and I know from some sources (although this is not declarations or assurances) that even to the point of possibility to install CDS on more controlled environments (eg Azure web service instance).

If, however, someone still had doubts about the use of CDS as a relational database, stay tuned – I’m going to write few more blog posts about advantages of CDS, use cases and show some examples.

Oh and btw, just in case I’ll remind you that “SharePoint is not a relational database”;)

Do you want to know more about Common Data Service?

Follow my blog – there will be more about CDS. You can also check how I can help you or you can simply contact me!

06.04.2019 – SharePoint Saturday Warsaw

I will have honor to give a speech on 4th edition of SPS Warsaw among such stars as Gokan Ozcifci (Belgium), Vlad Catrinescu (Canada), Ahmad Najjar (Norway), Jussi Mori (Switzerland), Jon Levesque (United States) and more. I will talk about automation using Flow, PowerApps, SharePoint Online and more. Main message: Don’t waste time for silly things – automate!

Today’s world is so fast that if we do not filter it, we will become slaves to our tools. Gmail / Outlook, To-Do / Todoist or even Facebook can save time, but they also put a lot of information on us. How to avoid this? How much of this information can be automated and how? I would like to give you a little inspiration on how many solutions can be built relatively quickly and simply from the “IT lego blocks” like Microsoft Flow, PowerApps, SharePoint Online, Cognitive Services and Azure Functions.
We will make something productive so not to waste time any more.

Click here to register for the event