Category Archives: PowerApps

woman holding book with blank pages

Add Another Screen to your Custom List Form with Power Apps

Note: This is more of an “idea that might be useful” than a “how-to” post.

Add a Second Screen… for Governance?

Adding a second screen is not rocket science. It’s super easy as you’ll see below. Navigating between two screens is just as easy. In most cases, you’re adding new screens as a part of a larger app with forms, views, and all sorts of wonderful functionality. In this case, we’re merely adding a second screen to inform the user, to provide a space for information not usually made available otherwise.

When customizing a list form in Microsoft Lists or SharePoint, Power Apps creates a special app to replace the default list forms. What makes it special from a Power Apps perspective is that it doesn’t show up in the Power Apps “Apps” listing. It is only available as a part of the list that it’s created for. It also includes a special component called “SharePointIntegration”. I’m not digging into that component in this post, but it’s used to control which Power Apps forms are stitched together with which list Actions.

The vast majority of apps I’ve seen extending lists do a one-to-one replacement of a custom Power App app for the default list forms. It makes sense, that’s the scope and context folks are thinking in at the time. What we’re talking about here merely extends that concept with a little more user-friendliness we haven’t been able to easily add before Power Apps was introduced.

The Quick Steps

  1. If you haven’t already, create the custom form
  2. Once in Power Apps, add a New screen. “FormScreen1” is the default screen created by the app wizard. If you haven’t added any screens to the app, the new one will be “Screen1”.
  3. On the new form, add a Text Label (see below for a suggested layout)
    • Add whatever text you’d like to the control. This is the super helpful text for the user to help them be successful using your form.
  4. On the new form, add a Button
    • Set the button Text to “Back” or whatever makes sense for you.
    • For the button OnSelect, set the formula value to “Navigate(FormScreen1)” – back to the original screen.
  5. On the original screen, add a Button
    Let your UX guidelines dictate where the button should be located on the screen.
    • For the button OnSelect, set the formula value to “Navigate(Screen1)” – navigate to the new screen.
    • Set the button text to “Info”, “More”, “About”, or whatever makes sense to your users.

Your new (second) screen might look something like this – as an extremely simple example. There’s obviously a LOT more you could do with this. The second screen merely unlocks the possibilities without cluttering up your custom form.

Your first screen might look similar to this, but likely with more fields displayed on the form. The key difference we’re adding here is the button that takes the user to the second screen. Check with your UX folks on how to properly position it.

It might be interesting to consider adding a second screen to the app and having it presented to the user *first* – along the lines of a disclaimer the user must accept before continuing. You’d likely need to tweak the SharePointIntegration control to get that working the way you want it. Just a thought.

Potential Uses

There are lots of potential uses here. Just as many as most other Power Apps as the customized list forms really are fully functional apps. The context, however, being part of a list – will likely drive how additional pages might be used.

  • Content and information sharing (as done here)
    • Who to contact with questions
    • Instructions for the form
  • Visibility to metrics about the list, open items, how fast requests are closed…
  • Additional functionality you don’t want to mix with the main form
  • Visibility to data from another data source
  • … and lots of other things

References

Note: Power Apps Week

So apparently this is Power Apps Week for followers of the Power Hype Machine 😉 as #PowerAddicts get ready for the Power Platform Conference this Fall. I don’t know exactly what that means, but I’m willing to bet David Warner can tell you.

In celebration, I’ll post a few short posts about Power Apps – most, or all, highlighting quick and easy ways to extend M365 capabilities for folks getting their feet wet with Power Apps. Enjoy!

We Don’t Need… Oh Hey, Badges!

(Yep, they added Credly badges this year.)

If one of the goals of the Microsoft MVP program was to build a crazy enthusiastic community of experts, marketers, evangelists, community leads, feedback conduits, and more, I’d say they’ve been successful. The Microsoft tech community continues to grow year over year and the momentum created by folks (MVPs, Microsoft employees, and lots of other folks…) brings all sorts of great content to learn from.

Do other technical communities have similar programs? Salesforce, Workday, Service Now, or others? (I’m legitimately asking and curious…Respond in comments if you know of other programs)

If you follow the Microsoft technical community on pretty much any social media platform, you likely saw the flurry of “excited, humbled, grateful, and honored” posts that coincided with the annual re-award cycle. New “MVPs” are awarded and announced throughout the year, but July 1st (or thereabouts) is when existing MVP award winners hear whether or not they’ve been re-awarded for another year – hence the flooding of your threads. (sorry)

Grateful and Honored

I am also honored to be re-awarded this year. I continue to work in the M365, Microsoft Teams, SharePoint, and Microsoft Lists spaces – but am also working to get a larger foothold in the Power Platform and Power Apps space as well. My favorite space is in the bridge between the two communities and platforms helping folks established in M365 expand their capabilities with Power Apps. There’s an interesting combination of a huge existing (M365) community using Lists and a dramatically expanded set of technical capabilities that come with the Power Platform that I love to share.

Notes and Expectations

With every year comes a number of “How to become and MVP” posts. Many are good intros and roadmaps for how to be good community contributors and leaders. It is important to remember, however, that following anyone’s guidance is not a guarantee to an MVP Award. The process remains subjective – there is no sure-fire recipe that equals a slam dunk award. I’m not saying this to dissuade anyone – far be it. I’m 100% supportive of anyone contributing in any way they can. I merely want to set expectations appropriately.

With that in mind, if you see something, say something. Wait… that’s not right… 😉 If you see folks doing awesome things in the community, please let folks know. We are as eager as you to identify and recognize them. Pull those Microsoft folks or MVPs aside and let us know who’s rocking it as these are the ones that can nominate people to be considered for the MVP award.

Alumni

Each year there are also folks that aren’t re-awarded for one reason or another. And while this change of award status can come with a sense of loss it also puts a spotlight on the relationships that are formed. Relationships often grow beyond just a collection of geeky content contributors. There are connections, partnerships, networks, and friendships that form and last well beyond the MVP program (not to mention even a few marriages) and our time as MVPs. So, when that time has ended and award years lapse, as it will eventually for all of us – either temporarily (plenty of folks get re-awarded later) or permanently as our careers and priorities change, be sure to reflect on all the great stuff they have contributed over the years. Thanks to all MVPs, current and alumni.

References

road landscape art street

Direct Action Buttons in Power Apps

No. “Direct action” buttons aren’t a new feature or button type. It’s just a term I’ve used in conversations and sessions talking about Power Apps and more often than not SharePoint lists or Microsoft Lists.

The default experiences users see with Lists or wizard-created apps in Power Apps are to act on a record or item as a whole (see image below). This by itself is fine as it accounts for plenty of use cases. However, there are also times where users want to take a specific action on a record. For example, in the case of managing requests, users may want to take ownership of a request, quickly assign a priority, or close a request. These can be accomplished with buttons build specifically for those use cases.

Microsoft list item editing. Showing the item menu selecting the "Edit" option.
Default list editing

In the default experience, operating on items in a data source (List, or other) the “close request” activity requires both technical knowledge and business knowledge.

  • The technical knowledge they need looks something like: Find the item, open/edit the item, change the value for the specific field from one value to another, and close/save the item.
  • The business knowledge users need, at a minimum, is to change *which field* to *which value*.

You’d hope both of these activities would be intuitive, but in many cases they are not. In most cases the process and user experience can be improved – even a little bit. Enter “direct action” buttons.

What does a simple, typical example look like? How about this? In a work request list, setting a request priority with a button click:

Two records with buttons to set each item to “High Priority”.

Logically, the example image probably has its priorities messed up (please rescue the raccoon before mowing the grass), but it illustrates a user wanting to take specific changes to an individual item.

Patch Command

The above items are in a Power Apps gallery. So, the code – in the OnSelect formula for the button might look something like this:

Patch('Work Requests List’, LookUp('Work Requests List',ID=Parent.Selected.ID),
{Priority: { Value: "1 - Needs immediate" }})

The Patch command is the key here. The example above breaks down as follows:

  • Patch('Work Requests List’, – The data source being updated
  • LookUp('Work Requests List',ID=Parent.Selected.ID), – Identify the specific item in the data source to update.
  • {Priority: { Value: "1 - Needs immediate" }}) – Set the “Priority” field to this value

For a more detailed look at the Patch command, check out the reference links below.

Note: In an example where the user selects an item in a Data Table, the formula will be slightly different:

Patch('Work Requests List’, LookUp('Work Requests List',ID=DataTable1.Selected.ID),
{Status: { Value: "Closed" }})

Crawl, Walk, Run

Once you get started down this path, there are so many options for makers to fulfill business needs with these approaches.

Intake lists (a generic term for request lists of all types) often evolve over time. These lists might start simply where “closing” a request is literally changing a status field from “open” to “closed”. As these processes mature, closing a request might turn into something like:

  • Change a status field to “closed”
  • Store the name of the person that closed the request (in a field separate from the modifiedby field)
  • Capture the date/time the request was closed (in a field separate from the modified field)
  • Send a notification to someone about the ticket closing (send an email, post to a Teams channel, etc.)

While the requirements get more complicated, all of these tasks can still be dealt with using Power Apps formulas or other approaches.

Alternate – JSON

There are ways to create buttons and actions within the List interface using JSON to implement view and column formatting. This is, by some, billed as another “no-code” approach, though it is not in my opinion. It is, however, an approved approach within the platform.

You can look into these approaches here: Advanced formatting concepts

Bonus Footage

A few extra notes.

DisplayMode for button

You may have noticed in the image above that the button can show up as enabled or disabled. This is accomplished by setting the DisplayMode formula to something like this:

If(ThisItem.Priority.Value="1 - Needs immediate", DisplayMode.Disabled ,DisplayMode.Edit)

The formula roughly translates to: If the item is already set to high priority, disable the button. If not, enable the button.

Refresh as needed

I haven’t looked into the details of the mechanics behind how screens and data sources work, but sometimes the screen updates right away after a Patch, other times it doesn’t. So, try the Patch and see if the screen and controls update the way you want. If they do, great! If the data doesn’t update quickly, you could add a Refresh to your button to force the data to refresh.

The “trick” here is that the refresh isn’t of the controls, but of the data source:
Refresh(DataSource)

Ramblings

I’ve been trying to figure out why it’s taken me so long to get a post out. It’s not technically challenging. In fact, it’s quite simple from the Power Apps perspective. From an experienced maker or pro-dev perspective, creating buttons is second nature. Power Apps offers plenty of power and flexibility in this department.

I think I just wanted to give enough context for makers to understand that while the concept is simple, the potential and implications can be huge – both in the capabilities makers have and in the improved experience for users. For makers, using something like Patch is a next step after working with the default form and the Save/Cancel options that come with the default experiences offered by Lists and wizard-created Power Apps.

It’s not rocket science. But plenty of folks just haven’t gone here yet. After almost every session where I mention “direct action” buttons, people are super excited about the possibilities. When they figure it out – it opens up so many cool options. Just a small step of growing in the Power Platform.

References

Updates

Note: The Patch function does NOT bypass Microsoft (and SharePoint) Lists updating the Modified date and Modified By fields. These fields do get updated when the Patch function updates anything in the item/record.

Connect Power Apps to SharePoint Online Site Pages Library

Trying to connect Microsoft Power Apps to a SharePoint Online library, specifically the Site Pages library ran into a little snag. Usually, when connecting to a data source you pick the site, then get a list of lists. Site Pages does not show up.

However, you can select the checkbox for the last/blank option, fill in “Site Pages” and proceed.

The connection should work just fine, and you can go about your Power Appy business.

SPC20 – Extending and Enriching Collaboration Platforms with Power Apps

The Event

The SharePoint Conference is the premier SharePoint and Office365 content event – held in Las Vegas, NV May 19-21 and will include a TON of awesome speakers, sessions, workshops, and vendors. It’s the largest SharePoint conference around and a unique opportunity to visit with Microsoft folks that are building the software tools we use every day. It’s also typically an event where Microsoft makes major announcements on new features, new products, and more!  Not only do you get the opportunity to hear those announcements first-hand, you can chat with Microsoft product team members to dig into details and provide your feedback.

The Session

This 60-minute session will dig in deeper than my 20-minute session on “7 Quick Wins”. I’ll be talking about opportunities within SharePoint and Microsoft Teams where extending the content, solutions, and interfaces with Power Apps enables users to be more productive and utilize more of the tools available to them in the workplace.

Why am I talking about this topic?

For years we’ve had SharePoint around and organizations have found varying degrees of success. In many ways their success depended on how invested they were with the platforms – how far adoption went in both breadth and depth. Some teams used document libraries instead of file shares, but never made use of things like metadata or views. Some organizations dug into SharePoint lists, built solutions, and took a few organizational steps forward, but rarely were platforms embraced holistically within organizations. Now, we have Microsoft Teams – which arguably is taking on the mantle of the monarch of the collaboration space. For organizations embracing Microsoft Teams it is becoming the one-stop-shop for where to work, where to find critical info or a path to other systems that maintain important information or functionality. One thing that’s been consistent is that there has almost always been a lot of functionality, capability, and ROI left on the table. This session is addressing some of that technical debt – helping people understand what capabilities are available or how those capabilities can be implemented for business benefit and ROI. In most cases that ROI comes from employee time, but it can easily extend into things like preventing or managing expenses or even preventing regulatory lapses or fines.    

What will be covered in this session?

The Power Platform – Power BI, Power Automate (aka “Flow), Power Apps, and Power Virtual Agents all have a lot to offer, but this session will focus on what Power Apps brings to the collaboration space. From my perspective that means access to information – visibility or accessibility to information – getting the information from whatever source (we’ll be talking mostly about SharePoint list data) to whoever needs the data, in the form that they can best consume the data, and on the platform that makes the most sense.

  • We can use Power Apps to build better SharePoint list forms
  • We can build Power Apps using SharePoint libraries or lists as data sources, building better user experiences, connecting to additional data sources, and still surface the solution in SharePoint
  • We can take SharePoint data solutions with Power Apps to the Microsoft Teams surface
  • We can take data mobile with Power Apps

With SharePoint we’ve been building single-page and multi-page solutions for a long time by stitching together the out of the box list interfaces and SharePoint pages and then extending them by customizing views, forms, and other controls. When Power Apps was first integrated into the SharePoint and O365 space it was pitched as all about forms – something we’d been wanting and needing for years. That scenario is still valid and relevant – finally allowing list owners to build interfaces within the SharePoint context. As SharePoint itself continues to evolve, both form and view solutions enter a bit of a gray area as SharePoint features start to overlap with capabilities that Power Apps also offer. Power Apps allows us to build apps, using one or more lists (or other data sources) and continuing to present those solutions via SharePoint pages, though Power Apps can also be used directly in the browser as well. Those same apps are also available via mobile devices in either phone or tablet layouts.

With the addition of Microsoft Teams to our suite of choices, we have another surface to make apps and solutions available. Tabs within Teams allow both surfacing of SharePoint lists and Power Apps. Power Apps are, in fact, easier to add to a Team than they are to a SharePoint page (though neither are difficult). 

The short story is that our toolbox as consumers of business data and builders of business solutions continues to expand within the Microsoft Office space by using Power Platform tools like Power Apps and its partner technologies.

Session Abstract

As SharePoint team sites move to Team sites, mobile workers demand solutions, and makers run into the limitations of lists, organizations look to Power Apps to deliver added value and capabilities to both traditional and emerging collaboration platforms. Explore approaches and strategies for Power Apps with SharePoint and Teams.

Still working in the cross section of Power Apps and other tools and platforms we’ve been working with in the collaboration space… SharePoint, Teams, O365

My speaker page

SPC Registration and Discount

Use code “PRESTON” when registering to save $50. (or click the image below)

SPC_SpeakerBanners_PRESTON_thumb2_th

Keep an eye out for more content and announcements on SharePointTV and the SharePoint Conference video page.

Conference Schedule 2020

OK. Apparently I need to re-up my blog game as last year’s “conference schedule” post is only a few posts down on my site. It is time to get things ramped up again with a bunch of conferences to attend and present at, with lots of news and platform changes, and tons of great information out there. So here’s what’s coming up so far:

April 2-3 – North American Collaboration Summit 2020 – aka “SharePointalooza”, Branson, MO

This is PAIT Group and Microsoft MVP Mark Rackley’s event. He does a fantastic job pulling together a multi-day workshop and conference event down in Branson that includes over 60 speakers including Microsoft folks, MVPs and MCMs. If you’re from the upper Midwest, this is a drivable event and one that in addition to all the great content and networking, gives those of us living in Winter wonderland a taste of the Spring weather to come. If you want a more personal-sized event that’s not as overwhelming as some larger events but still gets awesome content, speakers, and networking, check it out! Come for the content and networking – and get the weather as a bonus!

Save $50 using coupon code: “PRESTON”. Registration starts at $100.
NEW – Save 50% using coupon code: “SPSTC” 

April 13-17 – SharePoint Fest D.C. – Washington, D.C.

SharePoint Fest is a great opportunity for a week of intense training with 2 days of workshops, 3 days of sessions, and lots of opportunities to network with speakers, sponsors, and attendees. We usually see 3 SharePoint Fest events throughout the year and this is the first, with Seattle in late Summer and Chicago in December.

Join us for great Spring event in D.C.! If you do, use the code PRESTON100 when signing up for $100 off your registration.

Preston

April 19 – 23 – MicroConf – Minneapolis, MN

I’m just attending this one – no speaking. I’m really excited to check it out though as this is a conference that’s been around for a few years and had rave reviews from others in the community. It’s been in Vegas for a while and just this year is moving to Minneapolis – where one of the founders is from and conveniently is where I’m also located – so hard to pass up the opportunity to check it out. If you’re an entrepreneur or are thinking about it, make sure to check this out. They’re also expanding to more regional events going forward.

May 19-21 – SharePoint Conference – Las Vegas, NV

Still the premier Microsoft produced and partnered event focused on Office365, SharePoint, and all the tech that extends and supports it. Expect to see big announcements, lots of product team presenters, big names in the community and executives from the Microsoft teams that bring you these great tools.

Use code “PRESTON” when registering to save $50.

SPC_SpeakerBanners_PRESTON

May 30 – SharePoint Saturday Twin Cities [New Name??] – Minneapolis, MN

One of the biggest and best SharePoint Saturday events around. We’ve been going for over 10 yrs with over 20 events. It’s not just about SharePoint anymore. We cover Office365, Microsoft365, SharePoint, Microsoft Teams, the Power Platform, Azure, and lots of other platforms and apps that support and extend the O365 space. The last few years we’ve been averaging about 400 attendees and have a wonderful collection of sponsors and volunteer speakers from the local area, region, greater US and sometimes international folks. Join us for a wonderful free event!

No need for a coupon code because it’s FREE !!

July 22-23 – Enterprise Rising Conference 2020 – Minneapolis, MN

This is another local entrepreneur and startup conference here in the Twin Cities doing a lot to bring the startup community here together.

August 24-28 – SharePoint Fest Seattle – Washington, D.C.

More info will be available as we get closer.

September 21-25 – Microsoft Ignite – New Orleans, LA

It moved! Both the date and location. Microsoft’s ultimate tech conference event. More info as we get closer but you can pre-register already.

TBD – Fall – SharePoint Saturday Twin Cities – Minneapolis, MN

Just want to keep this on your radar. We won’t have a date until after our Spring event but we will be back for the Fall with lots of info and announcements fresh from Ignite.

SPC20 – 7 Quick Collaboration Wins with Power Apps

The Event

The SharePoint Conference is the premier SharePoint and Office365 content event – held in Las Vegas, NV May 19-21 and will include a TON of awesome speakers, sessions, workshops, and vendors. It’s the largest SharePoint conference around and a unique opportunity to visit with Microsoft folks that are building the software tools we use every day. It’s also typically an event where Microsoft makes major announcements on new features, new products, and more!  Not only do you get the opportunity to hear those announcements first-hand, you can chat with Microsoft product team members to dig into details and provide your feedback.

The Session

This 20-minute session will highlight and some quick wins, as examples or demos, that you can walk away with and use in your own environments. The plan is to show 7 scenarios, on a fairly quick pace with an average of a little more than 2 minutes per example. Don’t worry though, I’ll stick around after the session for any questions and follow-up the session with posts walking through examples as needed.

Why am I talking about this topic? I’m covering this material and these concepts because there are a LOT of folks using SharePoint, using Teams, even using Power Apps that haven’t seen the capabilities or don’t understand the value these platforms provide and how they can be potentially leveraged together. Sometimes, especially in the cases I’ll be highlighting, solutions can provide a lot of business value for relatively little effort and without additional licensing. There are even organizations out there licensed to use these products and not using them because the organizations don’t realize what capabilities they have available, how to use them, or what the benefits could be! Unfortunately, some of those folks have features or even whole platforms turned off because they’re afraid of what users may do or the organization doesn’t have the controls or governance in place to let them loose on the organization. This session may might illustrate a few reasons – via business value – why features should be enabled…  If they see some examples in action maybe a few will take that back home and make a difference.

So what examples are we talking about? There are so many options available and maybe even new scenarios that will pop up between now and the conference, so I’m holding off on finalizing the top 7 until we get a bit closer. The nature of online platforms and the rapid rate of change gives us new and evolving options all the time. My plan is to stay within the products and licensing covered under Office 365, SharePoint, Microsoft Teams, and Power Apps so if you’re already using SharePoint, you won’t need additional licensing to gain the benefits of the examples you’ll see.

Quick things I like to make sure folks are familiar with when combining these platforms and potential “wins” for the session:

  • Editing SharePoint forms with Power Apps. You can do lots of really powerful things and leverage the full power of Power Apps, but you can also make the quick and easy form changes that users have been requesting for years. Change the order of fields, change the formatting, add some text or even links to things like help pages, and more!
  • Take your SharePoint list data to the mobile platform.
    Yep, list data is available today via the SharePoint mobile app, but there are advantages to using a Power App instead…
  • Insert a Power App in Microsoft Teams
  • Add a Power App to a SharePoint page
  • Utilize the Power Apps graphing components. Not a replacement for Power BI, but maybe the “good” in a “Good, Better, Best” scale with other graphing options available.
  • Quick data views with the Data table control
  • Connecting multiple SharePoint lists for *real* relational data…
  • Something with Microsoft Automate integration? Eh… maybe, maybe not…
  • Create “action” buttons for your SharePoint list data. Instead of the old: Edit an item, change the status field value, click save steps. Use Power Apps to access and manage data in one (or more!) SharePoint lists and build an interface with buttons that change the status to what you want rather than using the SharePoint list manipulation methods.
  • Display a map to display an address on a page, form, app. I always thought this was cool.

What do *you* want to see?

Most of the content will be for folks new to Power Apps, but experienced with SharePoint and/or Microsoft Teams.  Although new users to SharePoint and Teams will also benefit.

Session Abstract

Quick take-away examples using Power Apps to extend user engagement and data availability with SharePoint, Office365, and Microsoft Teams, and more.

My speaker page

SPC Registration and Discount

Use code “PRESTON” when registering to save $50. (or click the image below)

SPC_SpeakerBanners_PRESTON_thumb[2]_thumb

Keep an eye out for more content and announcements on SharePointTV and the SharePoint Conference video page.

PowerApps for SharePoint Users – Quick Getting Started Resources

A few quick links to trials, sites, and step-by-step details on a few key things

Key PowerApps scenarios for SharePoint users:

Back at it: Spring Conference Schedule

Well, it’s been a nice little break for a few months, but time to ramp it up again:

North American Collaboration Summit, aka ‘SharePointalooza’

Preston - PowerApps New Approaches to the Traditional SharePoint List View

Branson, MO – March 14-15

http://www.collabsummit.org/ – Use code: Microsoft

 

SharePoint Saturday Twin Cities

Bloomington, MN – April 6

www.spstc.com
Register here: https://www.eventbrite.com/e/sharepoint-saturday-twin-cities-spring-2019-tickets-55007874032

What? You’d like to sponsor? Awesome! Contact me at sponsors@spstc.com

 

SharePoint Fest DC

DC19Banner728x90SPSTC15
Washington D.C. – April 29 – May 3
https://www.sharepointfest.com/DC/ – Use code: SPSTC15

https://www.sharepointfest.com/DC/index.php/ourspeakers

 

The SharePoint Conference

Wes Preston-Preston Social Banner-254
Las Vegas, NV – May 21-23

https://sharepointna.com – Use Speaker Code: PRESTON when you register.

https://sharepointna.com/#!/speaker/Wes%20Preston/1913

Using PowerApps to Solve SharePoint’s Column-Level Security Puzzle

Overview and Business Need

Users want to collect and manage data – be it an upgrade from a spreadsheet or something more formal like a request process. It’s still a need and where SharePoint (online or on-premises) continues to excel. While more and more SharePoint-specific features and capabilities have spun off into separate products, SharePoint Lists thus far continue to be core to the platform and something power users still use as a go-to solution for their business needs.

So power users spin up a SharePoint list. They identify the information that needs to be collected and often review the list of fields to find that it would be preferable if not all fields were available to all users: sensitive data. Some of the data, for one reason or another, is business sensitive but is still required and needs to be viewed and managed only by specific users.

Even with these requirement limitations, the power user keeps plugging away with a SharePoint list and looks to the interface and controls – either out of the box or via customizations – to limit access to the sensitive data. This is ‘security by obscurity’. At the end of the day, if data is in a SharePoint list and users have read or view access to that list – they have access to all the data in the list. Period. No matter how hard you try to hide it with configured forms and views.

Enter PowerApps and another path – one that allows for true security of data in SharePoint lists. No more ‘security by obscurity’.

Spoiler: The Short Version

If you want to jump to the guts of it, the solution I’m suggesting can be outlined as follows:

  1. Use two SharePoint lists (Simple version. You can have more lists as needed). Use a main/parent list to hold most of your columns and put sensitive data into a second list.
  2. Configure specific permissions for each list (They will be different). In a typical intake form example, requesters will have contribute access to the main list and no access to the second list. Process approvers, managers, admins will have contribute access to both lists.
  3. Create a PowerApps app based on the parent list
  4. Add the second SharePoint list as an additional data resource to the PowerApps app
  5. Add your sensitive data fields to the PowerApp screens – Detail and Edit screens for now
  6. Update the Save button on Edit screen as needed
  7. When publishing your App, only give access to your management group – the one with access to all data

There are variations and details of course, that I’ll discuss below.

Background: SharePoint Security

SharePoint security has flexibility and multiple levels of granularity. A notable limitation, however, is that you can’t secure individual fields. You can set permissions on a list, and have some control (Item-level permissions) over individual items if you like. If you have content in a specific field or column that you only want certain users to have access to, you’re out of luck without building a custom solution to handle it.

If you want to store sensitive data in a second SharePoint list and have some sort of parent child relationship between the lists, you could make that work, though you’ll still need to stitch together some of the interface. SharePoint is pretty effective at handling single lists, but isn’t great at interfaces with data from multiple lists. From a page and view perspective, Modern SharePoint is starting to catch up to Classic functionality with connected web parts and even then the experience is a bit kludgy depending on how you are trying to build the relationships.

Start with the Data

What fields does the solution need to capture and how do you need to structure your data? Can it all be in a single SharePoint list? Should SharePoint be the container at all?  For the purpose of this post we’ll assume SharePoint is where we’re storing our data. We’ll also assume that most of the data being collected is accessible by most of the folks using the system with the exception of a few fields we’d like to limit to a smaller group of users – usually admins, approvers, or the like.

To avoid the ‘security by obscurity’ issue, we need to pull the sensitive data out of the main list and put it in a second list. For those not familiar with database design, this is called a ‘Parent Child Relationship’.  With the data organized as such we can set permissions on each list separately, granting appropriate permissions to each list so that no matter how a user accesses the system they’re only seeing what they should.

image

image

A simple example of a request of some sort. Obviously missing typical fields like status, requester, etc. but enough to demonstrate our point. RequestList1 includes the columns requester users complete. RequestList2 has an Estimate field that folks have decided is sensitive data. In order to make our connection between RequestList1 and RequestList2 we need a column in List2 to reference List1. Two typical options for handling this connection – the parent/child thing – are to:

  1. Use a Lookup column in RequestList2 connected to the ID column in RequestList1
  2. Use a Number column in RequestList2 that also uses the ID column in RequestList1

There are pros and cons to using either approach (Some are listed below in ‘Decision Points’. Pick one. You don’t need both) but they do both work.

With the connection between lists established, we’re now confronted with a user interface issue. SharePoint does great with a single list, creating default forms and views for us. Outside of a Lookup column, SharePoint is NOT designed for relational data. So we need to find a better way to handle this shortcoming.

Creating Child Data

With the lists and relationship configured and users making requests (RequestList1), how does data get into RequestList2? Again, we’re faced with a number of options.

  1. You could have folks that manage the sensitive data create the data manually by adding items via the SharePoint interface to RequestList2 and manually selecting (Lookup column) or inserting (Number column) the ID value, but this is not realistic or good UX design.
  2. Use Microsoft Flow to create a list item in RequestList2 every time a new item is added to RequestList1.
  3. Add functionality to the PowerApps app to create and update items in RequestList2

Option 1 is not an acceptable production approach to our solution. It might be useful during design and testing, not beyond that.

Option 2 seems like an easy solution. Personally I’m not a fan though. When everything works as expected it may seem fine. However, adding a Flow to create list items introduces unnecessary complexity and unreliable delays when the same outcome can be delivered using PowerApps without adding Flows (possible licensing issue depending on scale) and a time delay because you don’t know how long a Flow may take to complete.

Option 3 uses PowerApps and gives the solution designer the most control. Child items in RequestList2 don’t need to be created immediately, but can be added to the list as the process demands – like when an estimate value is being added. PowerApps can handle there being no child item gracefully. There’s no time delay when items are created. Overall this seems like the best approach.

Create the PowerApp

For the sake of simplicity, I created an app from inside of SharePoint for the RequestList1 list. This automatically creates a 3 screen phone app.

Note: The same 3 screen functionality can be created as a tablet/PC layout that might be more appropriate to use cases.

There are lots of options available and plenty of technical ‘how-to’ details for how to create the PowerApp. I am not going to cover all of these here as I’m primarily talking about an approach. Regardless of the options selected the primary data source for the app will be RequestList1. RequestList2 will be added as a secondary data source. This is done at the app level – selecting ‘View’ and then ‘Data Sources’ in the top menu nav bars.

Again, for the sake of simplicity I’m limiting the number of fields displayed on each screen. There appears to be a technical limitation when using more than one Form control on a single PowerApps screen that requires scrolling. So, we’ll just avoid that for now by keeping the number of fields displayed to a minimum.

Note: More details about the field/form/scrolling limitation are available below in ‘Notes, etc.’.

By default, depending on the fields you choose to display, the DetailScreen (for RequestList1) starts out looking something like this:

image

We want to add a second form to a screen – corresponding to the additional Data Source.

On the screen, insert a new Form (Edit form on the EditScreen, Display form on the DetailScreen…). Set the DataSource to RequestList2.

The key here (yep, sort of a database pun…) is to sync up the selected item in RequestList1 with the correct item in RequestList2.

Set the Item on the new Form control to the following (assuming the ParentNumber column in SharePoint is a Number column type):
LookUp(RequestList2,ParentNumber = BrowseGallery1.Selected.ID)

Note: See alternate in notes below for an Item value using a Lookup column

If there is a matching item in the child table – RequestList2 – the data should be displayed, as seen below.

image

If there isn’t a matching record, the new Form control itself will not be displayed and a message will be shown in it’s place as seen here:

image

The form for the second data source can be included on either the DetailScreen (Display) or the EditScreen, or both. If changes are allowed (EditScreen), you’ll also need to add a ‘Save’ button or update the existing one.

The existing Save button is shown below:

image

By default, the OnSelect action is set to: SubmitForm(EditForm1)

If a second Form control is added to the screen and allows updates, the OnSelect action for the button will need to be changed to:
SubmitForm(EditForm1); SubmitForm(Form1)

With ‘Form1’ being the name of the new Form control. This will trigger both forms to save their data to their respective data sources.

Share the App

Finally, when sharing the PowerApp app, the system will show the data sources being used and remind the app creator to set permissions correctly.

Create the app only for folks that have access to both lists – the Admins, Managers, Approvers, etc.

You may need, or want, to create a separate app for the other users depending on your solution requirements.

image

That’s It!

I’d summarize what we’re doing but I covered that in the Spoiler section above to save folks time. I hope this is helpful. Please let me know if you have thoughts or questions via email, social media, or in the comments below. Thanks!

Notes, Nuances, and Decision Points

Loose Ends

As with any solution there may be other details and loose ends to tie up. This post doesn’t cover details that would need to be addressed with relational databases solutions such as:

  • If you delete a parent list item how do you make sure the child items are destroyed so orphans aren’t left over.
  • If you don’t automatically create a child list item when the parent is created (using a Flow, etc.), how do you handle it in PowerApps…
    • Check to see if a child item exists
    • If it doesn’t yet exist, display a button to create an item
  • etc.

Do you use Lookup columns to connect the lists, or straight Number fields populated with parent IDs?

What are the pros and cons? Personally, I find Lookup columns to be irritating for this type of solution. There are going to be a LOT of records in your main list (RequestList1). If you use a Lookup column the primary control in SharePoint is a dropdown of all items – which will be large. The Lookup column also comes with some baggage in what is actually being stored – a display value, an index value, etc. vs. a Number field that stores… a number.

Ultimately for what we’re doing here – they both technically work.

Using the Number column:
DataSource = RequestList2
Item = LookUp(RequestList2,ParentNumber = BrowseGallery1.Selected.ID)

Using a Lookup column:
Item = LookUp(RequestList2,Value(ParentLookup.Value) = BrowseGallery1.Selected.ID)

For either: Works pretty gracefully – if there’s no match it gives a ‘No item to display’ by default. This is one of the areas where a loose end could be tied up. If there’s a way in PowerApps to test for the existence of a child item, you could hide the Form control and show an ‘Add New’ button or something like that.

Scrolling Issues

There seems to be a technical limitation when using more than one Form control on a single PowerApps screen that requires scrolling.

You are not able to merge the scrolling action of a screen with two forms controls… Under the current limitations best practices would probably suggest that from both the DetailScreen and EditScreen that additional screens be added to allow for display and editing of the data in ResultList2. This forces the use of additional screens, but provides the cleanest UX. Limitations on scrolling prevent having multiple forms on the same screen, but if the screen is large enough (tablet format) that doesn’t require scrolling, both forms (and their fields) could be displayed at once.

Alternative: There are ways data from the second form could be displayed within the initial form as well by creating text fields in the existing form cards and setting them to values of the second form… easier on the displayscreen and requiring more setup on the editscreen to enable saving of updated values.

One-to-one or One-to-many

Is your sensitive data One-to-one or One-to-many?
The example shown here is one-to-one. If you want to do one-to-many you’d need to change your approach to using a gallery when displaying the child items and likely specific detail and edit forms for the child items.

Others

  • When first testing app sharing if a user didn’t have access to certain data a message of “Getting your data…” was displayed – which seemed a fairly graceful way to fail. After a longer period the same app was tested and had a blatant “You don’t have permission to view this data…” error plastered across the screen.
  • Note: With PowerApps and the rest of the Power Platform entering the picture with all the connectors available including custom connectors – whether or not SharePoint is the best platform for data is a question we need to ask ourselves again. Something for another time…
  • Special thanks to Sarah Haase for input on the title of this post. Smile

References