PowerApps: Setting a SharePoint List Lookup Column

PowerApps is really cool, and I really mean it. Unfortunately, it’s been moving forward so fast that it’s really hard to find clear information about how to do things. Examples you may find from just a few months ago may not look at all like what you see on your screen. (This post may be obsolete by the time I finish writing it!) Because of this, seemingly simple things can become a time consuming headache.

Here’s how I solved one of my recent headaches. There are lots of piece parts about this out there, but I haven’t found a soup to nuts description of how to do it.

I have a simple demo list called Beach Inspections – which are nice to think about in February in New England! It’s a simple list, and for this discussion, we’re only going to care about one column: Country. Country is a lookup column into the Countries list’s Title, which contains all the world’s countries. When we create a new beach inspection, we’ll usually want to preset the Country based on what we already know, whether it be the user or the beach name or whatever; it doesn’t really matter what. The challenge is in setting the value of the lookup field in PowerApps.

Let’s look at this from the initial setup of the PowerApp for the list. When you first click on Customize forms, you’ll see a bunch of screens and will arrive on one which looks something like this:

PowerApps generates a pretty decent form for you right off the bat – in theory, it may be what you need, but I expect in most cases we will edit this to make it our own.

To set things up to preset the Country lookup column, click on SharePointForm1 in the left panel (1) to display the forms properties in the right panel. You’ll see the data connection on in the right panel (2) at the top.

With the Data panel open, you can see the available columns from the data connection (your list – my Beach Inspections list). Many of them will already be checked, meaning they are included in the default form.

Next to the Country column, there’s a little icon showing what type of control is rendered on the form. When you click it, you’ll see the options below.

By default – even though the column is a Lookup column – the Edit selection control is set. This is the part I missed in all of the posts I found. Laura Rogers post (in the References section below) was the closest, but this part was omitted. You need to switch to the Edit lookup control to make things work.

Now, to set the value in the field, you can add a JSON object to the filed. In my case, I’m keeping it simple. I’m setting the default value to the United States with the JSON below:

This works because the item for United States in the Countries list has an ID of 236. If you’re familiar with the way lookup columns are stored in lists, this should make a lot of sense to you. In my Beach Inspections list, the Country value will be stored as:

236;#United States

I know, I’m being very USA-centric. I could just have easily used:

as the default, because the item in the Countries list for Andorra has an ID of 6.

Of course, the values for Id and Value could be provided by values in other fields, a calculation, or many other things. Again, I’m trying to keep things simple here.

One problem we see right away is that the field looks weird in PowerApps when we add this JSON:

That’s because PowerApps by default displays the value for '@odata.type'. I’m going to go out on a limb here and guess that no one will ever want to see that value in their form. The way to fix it is to change the setting in Value1 below the Default setting for the field.

You can decide to display the Value or the Id you’ve provided in the JSON. Generally speaking, I’m guessing you’ll want to show the Value, so select it from the dropdown.

Now, when I create a new item in my Beach Inspections list using the PowerApps form, I see this:

The default for Country is set to United States, but I can decide to change it if I’m inspecting all the beautiful beaches in Andorra. (Hint: Andorra is a land-locked country, so there’s very little work for us beach inspectors.)


PowerApps: Set SharePoint Lookup Field by Laura Rogers (@wonderlaura)


When Using Angular, Be Sure to Convert to JSON Using `angular.toJson`

This is a quick one, but if it has the potential to bite you, you’ll thank me for it later.

Image from https://memegenerator.net/Slap-My-Head

I use AngularJS generally these days when I want to write solutions in SharePoint, whether on premises or in SharePoint Online. Yes, that’s the older version of Angular, meaning versions in the 1.x range.

When you’re using any JavaScript to work with SharePoint data, though, you’ll probably end up converting the data from string-based JSON to JavaScript objects and if you want to save them back to SharePoint, you’ll then convert the data from JavaScript objects back to string-based JSON.

There are two primary ways for do the second conversion:

  • JSON.stringify – All “modern” browsers support this function and it works great. If you’ve been doing JavaScript development for a while, you’ll know this function very well – so well that you may rarely think about it when you use it.
  • angular.toJson – This is AngularJS’s version of the idea, but it’s smart specifically for objects which are under Angular’s control. In many cases, Angular augments objects with its own properties to keep track of things going on in the page. For example, $$hashkey helps Angular keep track of what objects have changed since a digest cycle.

Depending on how you manage the objects which contain your SharePoint data, you may end up with some of these additional properties on things. If you try to save a list or library item with one of these unrecognizable properties (to SharePoint, at least), you may get an error.

Because of this, it’s important to either:

  1. Delete the properties which will be seen by SharePoint as invalid before you use JSON.stringify in advance of the writes, or
  2. Use angular.toJson instead

I would recommend the latter, after getting bitten by my own code earlier this week. I had taken a great function Julie (@jfj1997) had adapted from Andrew Connell’s (@andrewconnell) earlier work on REST batching (See the article Make batch requests with the REST APIs for a link to AC’s examples.) and embedded it into a solution. Everything worked just fine for quite a while, and then we started noticing a few items missing when we expected them to have been loaded into a list – and it was our favorite type of bug: sporadic. It turned out that several of the items had Angular properties and were throwing errors in the batch loads. This was my fault – when I decided to use the function, I didn’t change JSON.stringify to angular.toJson. Once I did, all was right as rain.

Lesson learned for me (yet again!): be sure you understand exactly what the code you borrow really does.

Sending Emails from SharePoint Solutions Using Microsoft Flow

It’s not at all unusual to want to send an email from some page in SharePoint where there isn’t a native capability available. While there are lots of external services which can send email for us – like SendGrid – we usually want to keep the email “in house” in an enterprise environment. (In fact, there’s even a connector for SendGrid in Flow.)

While using an external service may not be appealing, even Flow is an “external” service. Once you’re in the cloud, you’re in the cloud. However, it seems to make more sense in many cases to simply use Flow to send emails via the Outlook Send an email action.

I’ve started setting up a generic Send an email Flow to support several client projects, so I figured I’d document what I’ve been doing.

The basic idea here is that I want to be able to call a REST endpoint with the details of the email I’d like to send and have it go out to the recipient(s). There’s no validation or tracking here; I just assume it happens. It wouldn’t be all that hard to add another few steps to log the emails to a SharePoint list or something if that were useful for you.

Flow has a perfect trigger for this: When a HTTP request is received. (I would have gone with When an HTTP request is received, personally.)

Start by creating a Flow “from blank”, meaning without using a template. If you search for HTTP, you’ll find the When a HTTP request is received trigger.

This trigger is a great way to create a poor man’s Web Service, just like we need here. Note that the REST endpoint will be provisioned after you save the Flow, so at this point, we don’t know what it will be, but no matter.

I had decided that my payload for the emails was going to be pretty simple, made up only of the To (recipient), Subject, and Body. That’s all it really takes to make a useful email. To set up a sample payload, I decided what I’d like to send in one of my client side SPAs (Single Page Applications) and dummied up some data, like so:

The nice thing here is that Flow will generate the schema it needs from sample data like this. Click on Use sample payload to generate schema and paste in your sample data, like so:When you click Done, you’ll see the schema the trigger needs to “understand” the data you will be sending. If you wanted to make changes to the structure of the incoming data, you could paste in a new sample or edit the schema manually.

Next we simply add a new step to send the email by clicking on New step, then Add an action. I find it by searching for “email”.

Once you’ve chosen that action, you can add the incoming data properties to the properties the Send an email action uses to form the email.

I like to have the option to send HTML emails (all pretty and everything), so I set the Is HTML advanced option to Yes.

It’s also worth noting – mainly because John Liu (@johnnliu) just reminded me after he read this post – that emails go out with Low priority unless you change the setting.

It’s not at all intuitive that this wold be the case, but it may well be how you’d want it set. If not, it’s a simply change in the dropdown in the advanced options.


Once you’ve saved the Flow, you can go back into it to get the REST endpoint you need to invoke it.

Finally, you’ll want to call the REST endpoint whenever you want to send an email. Here’s an example function I use in one of my Angular-based SPAs. It’s a pretty generic function, and I simply pass in the to, subject, and body values. Any work I need to do to build up those values happens elsewhere. (Lest you think you can use my Flow, I’ve edited the endpoint inventively. My Flow is still in an undisclosed location.)

And voila! Now you have an easy to use, generic emailing function. One downside to this approach is that the email will be sent using your own connection. For this reason, you may want to set up some fake “users” for the connections in Flow – maybe “SharePoint administrator” or something. I’m hoping the Flow team comes up with a better way for us to manage who the Flow runs as in the future, but for now this works.

Addendum: Eagle-eyed reader Charles Phillips (aka Grumpy Old SharePoint Guy) asked via email why I didn’t just send the email via JavaScript. Call me lame, but to be honest, I wasn’t aware of the method Charles covered in his post How do I send an email in JavaScript?, specifically the /_api/SP.Utilities.Utility.SendEmail method. I guess the other benefit of using Flow is that we can layer in additional generic capabilities as we need them, perhaps logging to a list, informing the sender, etc. without writing any code to do it. I like having a generic call that can do this stuff for me. However, if you’re on premises or can’t use Flow for some reason, the SP.Utilities.Utility.SendEmail endpoint could work well for you.

Using Security & Compliance Labels for Content Rollup in “Modern” SharePoint

It’s almost the normal course of events that we SharePointilists have to bend SharePoint’s capabilities to our whim to accomplish business requirements. This post is about one of these sort of bastardizations of the platform which allow us to get something important done. I’m writing it up because it’s what I do, but also to get some SharePoint Product Group eyeballs on it to make sure it’ll stand the test of time.

When I first saw how Security & Compliance labels worked, this is exactly the sort of thing I thought it might enable well, but I’ve not seen anyone try something like this. That is, I sort of ignored the words “security” and “compliance” and saw a overarching way to label content for knowledge management in Office 365.

Here’s the basic scenario:

  1. We have a set of “modern” sites – let’s call them Group A, Group B, etc. – which are effectively subsites to a Communications site – let’s call it the Department Site.
  2. We want to be able to roll up content from the Group sites where a certain piece of metadata has been applied to mark the document as Important.
  3. We don’t know how many Document Libraries or Content Types might exist in the Group sites, since people are likely to use the sites to get work done, adding content repos as they need them over time.
  4. We’d like to stick to “out-of-the-box”. It’s tempting to want to go and write some code in a custom SharePoint Framework Web Part, but that doesn’t necessarily cover some of the other requirements here.

Ideally, we’d have a great Information Architecture in place using the Content Type Hub, but usually we don’t have the luxury for that across a large organization – the discussions run on far longer than the requirement dictates we act. Plus, number 3 above. Plus, the Content Type Hub is way long in the tooth and really isn’t an effective tool in the “modern” era. Reasons for this include:

  • Management of Content Types is rudimentary, at best
  • The new flat site topology (subsites are now considered the “spawn of the devil” – to me this is throwing babies out with bath water) means that it’s MUCH harder to share Content Types across “modern” “sites” (which are now Site Collections)
  • Content Types have to be published to all Site Collections, which means that a large organization is likely to create an incredibly large set of Content Types – i.e., a real IA mess

Given the scenario and the new capabilities in the Security & Compiance center with labels, it seemed to make sense to try:

  • Creating an Important label. This label will effectively be used only as a tagging mechanism.
  • Publish the label only to the “modern” “sites” which actually need item. The requirements for one Department may well be different than those for other departments in a large organization. Of course, we can also choose the publish to all site. Even better, the label can be published for use in Exchange, SharePoint, OneDrive, and Groups! In other words, it transcends the normal bounds of SharePoint.
  • Any document in any of the target sites can have the label applied once the label is published. (This is supposed to take up to 24 hours, but in my testing, it was less than one hour.)
  • Use the Highlighted Content Web Part (HCWP) in the “modern” Department Site to display content which has the Important label applied.

The hard part was figuring out the mechanics of all of this. We don’t want to just start plopping labels into Security & Compliance willy-nilly, for – as noted above – their reach and scope can be quite wide.

The other thing that wasn’t at all apparent was whether the label data was available as a managed property for filtering in the HCWP. There were some complications here:

  • What managed property might contain the data? Was there actually a managed property to serve this purpose?
  • Would the managed property be available for filtering in the HCWP? The documentation for the HCWP says that only managed properties which are searchable would be available.

Well, it turns out there is a managed property: it’s called ComplianceTag and it’s mapped to the crawled property ows_ComplianceTag. I’ll admit I didn’t figure this out – someone at my client clued me in. The only references for this that I’ve found are developer references, so it wouldn’t be at all obvious to a normal person setting up filtering in a HCWP.

Based on the settings we see above, it’s not “searchable”, but read on…

Create the Label

You have to have access to the Security & Compliance center for this, which is in the Admin center. This access is often limited to IT, so yes, you’ll have to have one of those conversations. Once you are there, click on Classifications, then Labels, then Create a Label.

You’ll give you label a name and probably two descriptions: one for admins and one for users. It makes sense that they might not be the same.

Next, we can decide if we want to specify any retention policies for this label. I’m going to keep this simple and gloss over that part – leaving retention off for this label.

Finally, we review and save.

Now the label is in place, but it isn’t available anywhere yet, thus we need to publish it, and there’s a convenient button for that: Publish label.

Publish the Label

First we make sure we’re dealing with the right label(s).

Next, we decide where we want top publish the label. The default is EVERYWHERE. In a smaller organization, that might make perfect sense.

In a larger organization, you may want to publish to very specific places, and the capabilities here should have you covered.

When we publish the label, we’re actually creating a new label policy, and we have to give it a name and optionally, a description.

Finally, we review the settings and publish. Note the important message at the top of the screen:  It will take up to 1 day for labels to appear to your users. Labels will appear in Outlook and Outlook web app only for mailboxes that have at least 10 MB of data. As I mentioned, it took only less than an hour in my tenant, but clearly it can take longer.

Apply the Label

Once the label is available where you’ve published it, you can add it to content. The nice thing about this, is that applying a label is no different than working with any other metadata; the label capability is simply there in the Properties panel for each list item. Yes, this works the same in lists and libraries. Note that here I’m applying a the Final label because I didn’t want to wait for the Important label to proliferate.

Retrieve the Items

My items and documents with labels were indexed overnight (yes, indexing can still be problematic for these things), and I can now do a search with “ComplanceTag:Final” and retrieve them in the “classic” search center…

…and in the “modern” search results. Note that the list item is not displayed here – to me that is a bug.

Add the Highlighted Content Web Part

Finally, let’s display this content in our Department Site using the HCWP. Edit a page and add the HCWP to it. In the settings, we need to choose All sites, as it’s the only way to reach across Site Collections.

In the Filter and sort section, choose a Managed property filter and set it to use the ComplianceTag and your specific value.

And voila! You’re displaying the content you want in a “roll up”.


  • The Source in the HCWP has to be All Sites – which could become inefficient over time. There’s no option currently to specify a site or a library in another site.
  • Because of the above, we can add a SitePath filter for sites which contain something specific in their path. Not a great method, but it should suffice until we can create a Hub Site with its own Search Scope (assuming the Highlighted Content Web Part) . Alternatively (and perhaps in this case preferably), we can add a Highlighted Content Web Part per Group site with the specific URL as a filter.
  • The display is limited to these columns: DocType, Title, Modified, Modified By.
  • We can’t rename the HCWP, so we’re stuck with whatever title it gives us. Adding a Text Web Part above each of the HCWPs could be a workaround. John Sanders (@johnsanders) pointed out that we can indeed change the HCWP title. I tried in vain to do it, but once you know that you can type right in the title location (though the value is auto-populated based on the sort when you create the Web Part), you can most definitely change it.
  • The documents displayed come from the search index, and as with my experience testing this, that index can take a while to populate. So people who label their document as Important (or Final) and attempt to search for it or see it in the Highlighted Content Web Parts will not see that content until the index catches up. This is an indeterminate period of time in SharePoint Online, and often creates a lot of frustration.
  • This does not scale as your content corpus grows. I’d like to think that the HCWP will gain new capabilities over time which will help us with this, just as with the Content Search Web Part and the Content Query Web Part before it.


So there you have it – basically a cheat to enable knowledge management using Security & Compliance capabilities. I think  it’s truly powerful, as it transcends SharePoint alone and can work across Office 365 to a large degree, but I’m not sure if I’ve stumbled on something here which will fall apart if Microsoft makes changes to the way all this works. Stay tuned to this post and I’ll update it if I find out more.

SharePoint List Forms with PowerApps Now Available in First Release – and more!

Another day, another promise from the Ignite conference fulfilled. It’s great to see so many fantastic new capabilities rolling out to Office 365 – and quickly. At Ignite, the Product Group really seemed to focus on near term enhancements and improvements, which makes this last part of the year a fertile one for new goodies. I don’t usually do “here’s a new feature” posts, but some of the new features are just too good to acknowledge – plus, we’ve been waiting for the cloud fulfillment to come on some of this stuff for a long time.

This one is a little bittersweet for me, though, as having PowerApps for building list forms truly makes my value-added functions in SPServices obsolete. On the one hand, that makes me sad, but on the other hand, it should have happened long ago. Things like cascading dropdowns are just too important not to be a part of the product somehow.

This capability actually started rolling out last week, but I only noticed it over the weekend. The PowerApps team did a blog post last Wednesday, November 15,  entitled Announcing availability of custom forms, multi-value choice and read-only attachments support for SharePoint with PowerApps.

There are a number of announcements in the post, but here’s an executive summary:

  • Custom SharePoint list forms with PowerApps – More about this below.
  • Multi-select support for Choice, Lookup & People columns – My guess is these column types were what were holding up the PowerApps list forms capability, as these are unique to SharePoint. Since PowerApps has been built to be useful across many different parts of the Microsoft ecosystem, getting these column types into it was probably not high enough on the list for us SharePointilists.
  • Read-only attachments support – Well, it’s a start. At least we can display attachments in our PowerApps forms, even though we can’t upload them. Hopefully that will come soon, and the PowerApps team acknowledges its importance.

The big story here is using PowerApps for SharePoint list forms. They have been teasing us with this one for a while now, and the screenshots (like the one above – maps always get people excited!) made many of us excited for this day.

The out of the box forms for lists (and libraries) have never been all that sexy. Their utilitarian nature belied the almost magical capability we were getting in that the forms adapted immediately to any changes to the list structure and settings. We’ve had that capability for so long now that many people have lost site of how cool that actually is.

Most people want their forms to be pretty, or more importantly to better represent the look or flow of a business process. Forms are in a sense where the rubber meats the road for content management. We want them to work well for collecting metadata so that we can have as friction-less an experience as possible. If adding metadata doesn’t feel artificial or burdensome, we gain so many important benefits down the road. Metadata is NOT dead!

I believe that forms and process ought to be decoupled, and that’s what the PowerApps and Flow split does for us. Many forms are just forms and that’s it. Other times, we may need to layer in some process, and that’s where Flow comes in.


Creating the example I tweeted above showing PowerApps on one of my test lists was about a 3 minute process. Here’s how it works.

First, you must be in First Release for Tenant. First Release for Select Users will NOT get you this capability. Most organizations will not have First Release for Tenant enabled in “production”, so you may need to spin up a side tenant to play around with this.

The distinction between these two types of First Release is a constant source of confusion, and I’ll keep pushing for more clarity – even in the face of the change from First Release the the Targeted Release terminology. Leave it to Microsoft to rename things!

When you go into a list, you’ll see PowerApps on the toolbar, as you have for a while now. What’s new is the Customize forms option.

When you click Customize forms, you’ll be launched through a series of animated and flashing screens to land you in PowerApps with a default form already set up for you. It’s likely to feel a little disconcerting, frankly, especially if you haven’t used PowerApps before.

But the upshot is that you now have a fancy new PowerApps-based form – all ready to use, even if you don’t make any modifications. Note the Back to SharePoint link in the upper left. Once you’re done making any changes, you can click that link and you’ll see this dialog:

You’ll want to click Save and Publish. This will lead you through some more flashing screens – be patient – and you’ll land back on your list with your new form magically in place. If you select an item in the list and click Edit, you’ll see it.

I’m not going to go into how PowerApps works here, but there are some excellent tutorials out there from folks like Laura Rogers (@wonderlaura).

If you decide you’d like to switch back to the default list forms – or even back to Infopath – you’ll find that setting under List Settings / Form Settings. You can also delete the PowerApps form you’ve created here in case you want to start over (which I did to get some of these screenshots).

I expect this new capability is going to usher in a sort of forms renaissance in SharePoint. Don’t let anyone tell you otherwise, PowerApps is indeed the successor to Infopath, though don’t expect a one to one feature comparison just yet. Happy form-ulating!