Category Archives: SharePoint Online

Representing Events in Microsoft SharePoint – Collision between Modern and Functional

Background

There are a number of use cases for having visibility to one or more calendars in SharePoint intranet pages – calendars or events lists where a small groups of folks maintain the list of events and a large group consumes the info.

What are the recommendation on how to achieve this?

Calendars in an intranet. That’s the main detail being considered here. How big of a deal is it?
(No really, I’m curious, please let me know in the comments.)

Initial Thoughts and Letdowns

When it comes to the data, Outlook or Exchange handle dates and events best. However, the only surfacing of data between Exchange and SharePoint is the Group Calendar web part. Group Calendars allow everyone to see it, but also allow anyone to add, edit, and delete from the calendar. Not what we want.

SharePoint has historically had an Events list, that also does a good job of managing events – including All-day and Recurring event capabilities. A SharePoint list has list permissions that allow it to be configured with certain editors and certain viewers. However, the only way to surface the calendar view of this list is using a “classic experience” with the functionality we want but with a very dated interface that not only clashes with SharePoint’s modern look, but is only available on a modern page by embedding the view using an iframe. No web part available.

When using the “new experience” of the Events list, there is a new Calendar view type.

showlist

While this calendar looks great in the modern page, it currently seems to be buggy, does not carry through useful features of the classic view like overlays, has issues with filtering items, and does not currently work with the List View web part (but can be displayed with an iframe embed similar to the classic view). Some of these issues may be resolved with a reported update rolling out “soon” (September CY2021).

NOTE: The calendar view for modern lists isn’t just for event-like lists (which is what this post is focused on). The view can be used for *any* list type with a date column and in many cases is a really nice stop forward for those lists.

A bigger problem with the new experience and the Events list is that when using the new experience the All-day and recurring capabilities are broken. These properties and functionality do not show up in forms, though the fields still exist in the list. To make matters more confusing, while in the new experience, click ‘New’ and get the New item form. The form does not show the All Day Event or Recurrence fields, but if you edit the form, both fields are listed and checked as if they should be visible. Not cool for users or power users…

My guess is that this may be happening because of the integration of Microsoft Lists with the SharePoint interface and that Microsoft Lists do not seem to be able to handle these features either – creating a bit of an inconsistency in the interface. If you create a Microsoft List, you can create Start Date and End Date columns and align them with the new Calendar view, but there doesn’t seem to be a capacity to handle other date/event specific functionality that was available with the classic Events list.

SharePoint does also offer a Events web part, which attaches to the Events list. Rather than displaying in a traditional calendar view, the web part offers two different ‘tile’ views – following that little UX trend. While it may be a change for users, the web parts are a nice offering and definitely has its place within the intranet, highlighting upcoming events rather than leaving users to sort through a calendar view. Clicking on events also leads to a few special pages: Events.aspx and Event.aspx that are clean and easy to read. They also DO respect the event All-day and recurrence feature – which is good, but inconsistent with the modern calendar view mentioned above.

With that, Events appear to be at a bit of a crossroads – with some messy collisions – between classic and new/modern functionality that I hope Microsoft will clean up soon.

Conclusion for now

For now, the best solution seems to be continuing to use the Events list as long as it is available, adding the Events web part to the toolbox (previously only used calendar views), and embedding a calendar view until an updated List View web part comes along. It seems like there are a few “broken windows” here that I hope Microsoft repairs soon. A functional calendar view web part will take a nice step or two forward. Knowing what the plans are for Microsoft List support for events would also be nice.

Am I missing some critical feature or capability that solves these needs? Does the enterprise have have these calendar/event requirements?

What are you using in your intranet?

Questions

  • What is the Microsoft roadmap for Events going forward?
  • Events lists are shown as “Classic” features.
    • Will they continue to be supported?
    • Is there a timeline for support, replacement, or deprecation?
  • Will events be supported in Microsoft Lists?
    • You can create a list with start date and end dates, but no “Recurrence” or “All Day” functionality.

References

Modern SharePoint Page Approval with Power Automate: Message Optional

Overview

The current iteration of page approvals in SharePoint Online using Modern pages and news is handled by Power Automate – and the integration is pretty slick. Kudos to the Microsoft teams responsible for it (SharePoint + Power Automate).

Approval Flow

Once an approval flow has been configured and a user submits a page, the user is prompted by the following panel:

Approval Panel

Note here that the Message field is required. In many cases, this is fine. There are some circumstances however where site owners may want to make the Message field optional.

The Quick Steps

Without getting into all the nitty gritty, here’s how it’s done (at least one approach):

  1. Go into Power Automate and open the approval flow
  2. On the first step of the flow, expand the “For a selected item” step
  3. For the “Message” property, select the menu (three dots) button and select “Make the field optional”)
    Flow Field Optional
  4. Expand the scope (mine is called “Scope 2”), and the “Start and approval” step
    Approval Details
  5. Click on the “Details” expression (circled above) – which opens the panel for updates
    Approval Expression
  6. Update the expression to include a question mark (“?”) after “triggerBody()” and before “[‘text’]”.
    Expression Text
  7. Click “Update” (see above)
  8. Save the flow, and test

When an approval is submitted, the Message field in the panel should now show as “not required” – no asterisk should be visible. The panel should allow users to leave the field blank and “Submit” the page for approval without error.

Not Required

The Details

One example I’ve seen where this is applicable is a site where there is a content management team that does a significant amount of the page creation, but also has folks outside the normal content team that submit content from time to time. Approval is needed. When the main content team is creating pages – they want to get through it and keep moving. The Message field turns into a “junk” field, they keyboard smash or put some useless text in the field in order to submit the page. This is irritating for users and inserts bad/silly data into the system. Rather than remove the field outright, we’d still like to have it available when needed – by folks outside the team, for example.  

Let’s review how this works. Open up the flow. Then expand the “For a selected item” step (Step 3 above).

Changing the Message field to “Make the field optional” seems pretty straight-forward. It will even work for some flows. But by itself this change will not work for all scenarios. The flow will fail when the Message field is left blank because later in the flow a step attempts to use the text IN the field. When that text is blank, the flow fails. 

If you run/test the flow with no Message text, you can open the failed instance of the flow to see where the error occurred. The scope will show an indication that something is wrong. When you expand the scope you can see the error on the “Start an approval” step. This flow fail is also followed up by a “Something went wrong…”  email from Microsoft Flow.

Broken Flow

Looking at the error message, there’s something wrong with ‘text’, which looks like it’s pointing back to the Message field we changed.

If we test run the flow again, and put text in the message, the flow works fine.

Looking at steps 4-6 above, you can see where the additional change needs to be made – adding a question mark to the expression. This syntax (the “?”) allows the expression to evaluate the Message field – [‘text’] – as Null when it is empty. Before we added the question mark the expression didn’t know what to do with a blank Message field and failed the flow. 

Note: I haven’t been able to find official documentation anywhere on what the “?” syntax is within expressions, how to use it, when to use it, etc. Maybe my search skills are falling off. If you find something from Microsoft, let me know and I’ll update this post and references for it. Until then I’ll include what I did find below.

References

Paul Stork has the most information I’ve seen on the use of the question mark (?)  thus far as seen in this community post:
Solved: Use of Question Mark(?) in expressions – Power Platform Community (microsoft.com) 

Another blog that references the question mark with regards to a Power Automate expression
Power Automate – how do we check if a property exists in the object? | It Ain’t Boring (itaintboring.com)

Modern SharePoint + Power Automate Approval with Select User Auto-Approve

Background

I’m still a Power Automate newb. Lots of you have been working with these for years now. I’m legit curious what silly things I’m doing, what other approach I should be taking, what best practices to integrate, etc.

So let me have it… in the comments here, in Twitter, whatever. I’m waiting.

Overview

Power Automate backed approval flows for a SharePoint intranet site on the Site Pages library. Pretty slick and polished as a replacement for SharePoint workflows and nice template to look at for Automate newbs. Kudos Microsoft folks.

Now, however, I’d like to have approvals on my site, but also have them bypassed for certain users – folks that own the site, do the majority of content generation, etc. I still want the process in place because I have other content contributors that I still want to manage.

So, we have an out of the box page approval flow that I want to tweak.

It sounds potentially easy, but we all know better. There will be some pain. And there is. But we can work with it.

What I did

The out of box page approval flow looks as follows:
OOB Approval Flow

With the exception of a few new variables I added, all changes were limited to the “Scope 2” step. When I was finished, the top level view looked like this:
(Yes, I should be consistent with my naming – I’ll clean that up)

Final Approval Flow

I’ll walk through the variables shown above as we go.

Who Gets Auto-Approved

This is a topic that could be debated in terms of the best way to manage the list or folks that get auto-approved. Should you use a list, an Azure Group, a SharePoint Group, or something else? For simplicity’s sake, I’m going with a list. It’s the easiest to maintain visibility and control at the team level without needing to work with a security team every time we need to change a group. I’m also pretty confident that Automate can get to the data I need in a list. I’m not yet sure about the other approaches. Obviously a decision like this may vary org to org. For this example, we’re using a list.

I created a SharePoint list and called it “AutoApproveNews”. Yep, I’m old school and don’t like spaces in my list names. I’m not messing with the Title field right now, so just left it alone and required. It’ll need to be filled with rubbish for now. The only column I added to the list was AutoApproved as a Person or Group field displaying the Name. Yes, the name might not be unique… so something to look at later to seal the process up a bit more. Using the email address is probably a nicer, more unique, approach but I haven’t tried that variation yet.

I didn’t do anything special with permissions for the list while validating this concept, but there are a few ground rules you’d likely want to follow.

  • Make sure whatever connection you’re using to SharePoint from Automate has access to the list.
  • Lock down the list to the folks that manage content so random users can’t just add themselves.
  • I also found it useful to remove the list from navigation. Don’t like cluttering things up unnecessarily.

First note on variables

The approach I’m using is that I’m going to get the name of the person submitting the news article, iterate through my list of folks that get auto-approved, and trip a flag that indicates I found a match.

So with that, AutoApproveFlag is created and set to false.

NewsSubmitter is set to the name of the person that added the new news article or page from the “For a selected item” step.
Submit Variable

The other variables are used in the text of the email, so you can fill in whatever fits for you. Here’s what I did for now:
Variable Initialize

You’ll see where they fit in down below.

New Steps

Using as much of the default flow as possible.

  1. After the “Set content approval status – Pending” add a step to “get items from” a SharePoint list and point it to the AutoApprovedNews list we just created.
  2. Add a “Apply to each” step to iterate through the SharePoint list data.
    Add a condition where if a match (comparing the AutoApproved field to the NewsSubmitter variable) is found, set the AutoApproveFlag variable to true.
  3. Add a Condition step. Test for the value of variable AutoApproveFlag – if not true (no matches found), then start the flow approval. I added the condition step and then dragged the existing “Start an approval” step into the No condition.

    Note: Because there will be some conditions where the Approval doesn’t exist, I need to change some of the email steps later in the flow because they use properties of the Approval in their email content – hence the other two variables that were created: strApprover and strComment. (I should be consistent with variable naming, started thinking about that after I was underway – cleanup for later)

  4. The default flow has a Condition step that waits for the approval response. I didn’t see a way to change the condition the way I wanted, so I created a new Condition step to check for an approval response OR check if the AutoApprovedFlag variable was set to true.

    Note: DO NOT delete the original Condition step until after #5 below and you’ve moved/copied the steps from the original to the replacement.

    Original:
    Original approval

    Replacement:
    New Approval

  5. Move or copy the steps in the yes and no conditions to the new condition step (#4 above). I was able to drag and drop some, but also needed to use the preview functionality of copying steps to the clipboard and then used the “Add an action” feature to add from clipboard.
  6. Now you can delete the original Condition you just copied the steps from.
  7. In order to clean up the emails sent later, I needed to replace Approval properties in the email content with variables that would work both when approvals were automatic and manual. In order to make these work, I needed to add a Condition step and Set Variable steps.

    With new Condition rules, moved steps, and an additional Condition step, the flow looks like this:
    After Moves

    Setting the variables as follows:
    Set Approval Vars

  8. Finally, we need to update the email content to replace the Approval properties with variables. Only the “Send email notification” steps (both “Yes” and “No” outcomes) under the scheduling condition (“Check if content has been schedule or not”) need to be updated.

    These are the ones you’re changing:
    Which email steps

    From this:
    TextBefore

    To this:
    TextVariables

  9. After we’re all done, Scope 2 looks like this:
    Scope2

I think that’s everything. I’ll update if I find additional details.

Recap, Summary

It works. What am I missing? What should I have done different?

Bring it!!

References and stuff

Thanks to Mark Rackley for a few pointers…

Modern SharePoint – Workflow and Approval Visibility with Power Automate

I’ve been out of the pages and workflow game for a bit while focusing elsewhere – so playing a bit of catchup in certain areas. Plenty of folks have been digging in here for a long time, so what I’m learning and re-learning isn’t news, but it is new to me and I’m sure there are others in the same boat. There are plenty of folks on older versions of SharePoint that are, or will eventually, make a similar move to SharePoint Online as I’m working on/helping with right now.

In legacy versions of SharePoint, we’ve had workflows and approvals around for some time. These have been managed through SharePoint Designer (SPD) (…may it RIP) and surfaced directly in the list settings where workflows were implemented. Looking something like this:

LegacyWorkflow

In “Modern” SharePoint – SharePoint Online – the preferred and recommended workflow solution is Power Automate, which creates “flows” instead of workflows.

The SharePoint and Power Platform (umbrella group within MS that owns Power Automate and sibling technologies) folks have done a nice job integrating Power Platform tools within the SharePoint interface – including Power Automate and specifically approval workflows. Baked directly in the SharePoint list top nav/action bar:

Automate Integration

Out of the box, configuring a page approval flow is super easy and pretty darn elegant as an approval solution. Kudos Microsoft.

NOW. One of my first questions after turning one of these on was “Where can I see all my open approvals and manage them as needed?”. Because hey, I want visibility and the ability to override stuff. A few key points:

  • Configuring a page approval flow adds an “Approval Status” field to your library – so you can see the status in your Site Pages view
  • Users can review approvals on the submitted page itself and get there via the “three dot” menu on individual items in the Site Pages view “More –> Review Approvals”
  • Open approvals do NOT show up in the Workflow Settings page (shown above)
    • Initially, this might be kind of irritating, but it does make sense. The Workflow Settings page is SharePoint workflows, not Power Automate approvals
    • It would be kind of nice to have some note or even a link to where users can find the new approvals, but I suspect this page has a limited life left and probably isn’t worth updating

So, if you haven’t guessed it yet – where we find that list of open approvals, as well as history and lots of other goodies, is in the Power Automate interface.

There are several ways to get there, but the easiest for folks working in SharePoint already is to use the menu shown above and select the “See your flows” option. This will open a tab to Power Automate. In the left menu you’ll see Action Items –> Approvals.

Approval Flows

Ultimately, this is super easy and makes sense. But if you’re coming from legacy SharePoint and you haven’t made the mental shift to working with Power Automate yet you might just need a little nudge. Hopefully this helped.

SharePoint Modern Challenges – Weather Web Part

Overview

Working on a “relatively simple” intranet, using as much out of the box capabilities as possible… and running into what seem to be silly issues.

Want to display the weather. Cool (Haha, see what I did there?), seems legit. Should be easy. There’s a Weather web part.

Edit the page. Add the web part. It asks for the location. Nice and simple lookup that finds my desired location easily. The company has multiple locations, so we add multiple locations to the web part. Still looks good.

I can set the Celsius vs. Fahrenheit setting for the web part, but users can also override. Looks good.

I can override the displayed city name for each location.

That’s it for configuration/customization.

Issues

  • When does this web part update? How often? Can an update be forced?
    All unknowns. It would be nice to have this information as it does NOT seem to update on a timely basis and well… it’s weather – that changes regularly, if not quickly.
  • I have multiple locations in my web part. When I click the “MSN Weather” link for more information – like the next few days forecast – ALL locations configured within the web part seem to land on the same location’s info.
    • Not sure where it’s getting this default location. Might be the tenant’s default, the MSN site may be using web location settings, might be something else.
      I haven’t narrowed that down yet.
    • The link for each location appears to be individually configured as I can see the location of the web part in the link string.
      Does the MSN Weather site no longer recognize the URL string and/or parameters being passed in?
      Is something else overriding the desired location? Either way – it doesn’t work as users would expect.

Resolutions

None yet. I’ll update this if/when I find answers or work-arounds.

References

Show the weather on your page – Office Support (microsoft.com)

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.

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