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:
- 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.
- 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.
- Create a PowerApps app based on the parent list
- Add the second SharePoint list as an additional data resource to the PowerApps app
- Add your sensitive data fields to the PowerApp screens – Detail and Edit screens for now
- Update the Save button on Edit screen as needed
- 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.
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:
- Use a Lookup column in RequestList2 connected to the ID column in RequestList1
- 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.
- 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.
- Use Microsoft Flow to create a list item in RequestList2 every time a new item is added to RequestList1.
- 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:
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.
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:
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:
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.
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.
References
![SPC_SpeakerBanners_PRESTON_thumb[2]_thumb](http://www.idubbs.com/blog/wp-content/uploads/2020/02/SPC_SpeakerBanners_PRESTON_thumb2_thumb.jpg "SPC_SpeakerBanners_PRESTON_thumb[2]_thumb")
