Category Archives: PowerApps

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:

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

Mark Rackley talking about Parent/Child List Relationships
http://www.markrackley.net/2016/08/26/automatic-parentchild-list-relationships-for-large-lists-in-sharepoint/
Database Design – Parent Tables and Child Tables
https://www.youtube.com/watch?v=ZtK89kVLPzM
Announcement on Modern web part connections roadmap
https://techcommunity.microsoft.com/t5/Microsoft-SharePoint-Blog/SharePoint-powers-teamwork-in-Office-365-Microsoft-Ignite-2018/ba-p/255465
As of this writing SPFx 1.7 released Dynamic Data which allows for developer access to ‘connected’ controls. What we knew as ‘Connected web parts’ in previous versions of SharePoint has not yet been released in SharePoint Online / O365.
https://techcommunity.microsoft.com/t5/Microsoft-SharePoint-Blog/SharePoint-powers-teamwork-in-Office-365-Microsoft-Ignite-2018/ba-p/255465

SPC19 – New Approaches to the Traditional SharePoint List View: PowerApps and more…

In 2009 I attended my first SharePoint Conference (SPC). In 2012 I spoke at my first SPC. Between then and now there have been quite a few changes – both in the products and events. SharePoint has continued to evolve over the years with TONS of new features, shifts from on-prem to hybrid and the cloud, new and complementary products within the Microsoft ecosystem, and so much more.

In keeping with a consistent focus over the years, I’ll be talking again about how to extend SharePoint list views beyond their out-of-box capabilities. Back in 2012 we introduced a new and somewhat obscure method called Client-Side Rendering (CSR) and JSLink. Well, CSR wasn’t obscure to developers, though it was new to power users. JSLink was Microsoft’s way of making CSR somewhat ‘legit’ in the interface. Now, with Modern SharePoint and integration with other products in the stack, we’ve got Microsoft PowerApps extending SharePoint forms, views, access, and business solutions in new and interesting ways.

I’ll be talking about where PowerApps plays in the SharePoint space – not with regards to the marketing-focused element of forms, but with how users can extend access to SharePoint data both within SharePoint and beyond SharePoint.

Session abstract

It’s no coincidence that PowerApps are surfaced in SharePoint via the View dropdown control. In this session, see how PowerApps can be used to display rich and functional ‘views’ of your data – embedded directly into SharePoint or Teams, how PowerApps ‘views’ compare to traditional SharePoint views, and how PowerApps solutions compare to the new column and view formatting capabilities within SharePoint.

Why you should attend this session

This will be an ‘Overview’ session in the ‘Business Apps’ track – meaning we’ll cover capabilities and use cases but won’t dig into a lot of the details on how things are done. I’ll certainly get into some of the formulas, etc. but not go too deep. It’s not intended to be a workshop / step-by-step session, more of a “I can do what?!?” session where you’ll hopefully walk away thinking of new possibilities within your organization. I’ll also follow up with blog posts, etc. where more details are requested.

Ultimately, this session is about understanding and building on the Microsoft technology skillset for you or your team. PowerApps is a power-user friendly tool within the Microsoft stack – part of the ‘Power Platform’ coming out of the Azure and Dynamics teams. Much of the marketing around PowerApps is focused on building forms – which addresses the gap in SharePoint created by the deprecation of SharePoint Designer and InfoPath. But there’s much more to PowerApps and it’s integration with SharePoint. For SharePoint users that want to see and do more with their data, want to make their SharePoint data more accessible, want to go beyond what they’re able to do with out-of-the-box list views, and create foundations for business solutions to build more – they want to see this session. We’ll cover where users can start with quick and simple solutions and where users can choose from a few paths where more effort can yield even more powerful solutions.

Why you should attend SPC19

Have you attended a SharePoint Saturday event? These are wonderful, locally run events with a smattering of local and national speakers. SPC is the big time. It’s a large, Microsoft-backed, multi-day extravaganza of Office365, SharePoint, and OneDrive content that includes new announcements directly from the product teams themselves, overviews to help decision makers steer their organizations, and deep dives into features and functionality for architects, developers, administrators, and users. Where a large SPS might have 30+ sessions (most SPS events are smaller), SPC will have over 200 sessions – covering a depth and breadth unavailable elsewhere. What about the Ignite Conference you say? It’s a much larger conference, covering the entire Microsoft product offering. It’s size, however, tends to dilute Office365 and SharePoint content. SPC is the place to be if you need to, or want to, focus on O365, SharePoint, and OneDrive and has the community leaders and product team folks to back it up.

SharePoint Conference will be held May 21-23, 2019 at the MGM Grand in Las Vegas, NV.
For information about the event: https://sharepointna.com
If you’re going to register, use the link below with an embedded discount code.

Follow event announcements, speakers, sessions, and more on Twitter with @SPConf and #SPC19

Final Notes

In addition to this session, I’ll be presenting with Sarah Haase a session called “Building a Diverse Tech Community” – To learn more, check out our preview video.

Want to register for the event using my discount code?
https://www.sharepointna.com/#!/register?utm_term=PRESTON

More to come as we get closer to the event!

SharePoint Fest Chicago – December 2018

Are you attending #SPFestChi SharePoint Fest – Chicago next week? I’ll be presenting a workshop on Monday and sessions Wednesday through Friday. Mostly talking about the intersection of SharePoint and PowerApps but also talking some legacy client-side rendering for SharePoint ‘classic’ as well.

WRK302 – Introduction to Building PowerApps for SharePoint Users
PWR104 – PowerApps: New Approaches to the Traditional SharePoint List View
PWR202 – From SharePoint to PowerApps: Evolution of the ‘Intake Form’ Solution
PWR303 – Client-Side Rendering (CSR) for SharePoint: from Intro to Practical Application

Let me know if you have any questions about my sessions and hope to see you there!

PowerApps–Links to Apps and Screens

Last week at SharePoint Fest DC (#SPFestDC) I had a question in one of my sessions that I didn’t know the answer to at the time and wanted to follow up on:
“Can you link to a specific screen on a PowerApp?”

Doing a little search I quickly found the answer HERE in the General Discussion Community Boards.

Background: Link Basics

There are a number of ways to get access to a PowerApp via a URL link. They all by default will land the user on the default start screen for the app.

From SharePoint, PowerApps apps created from and linked to a list are displayed in the Views dropdown.

These links take you to an app ‘launch screen’ for PowerApps. Where you need to click again to get to the app.

Clicking on ‘Open’ launches the PowerApp in the browser window on a new tab.

Note: ONLY apps created from the SharePoint interface will show up in the views drop down. If you create other apps starting from the PowerApps interface – either from blank, or from data – they will NOT show up in the views drop down for the list even if they use the list as the data source for the app.

From PowerApps, when clicking on the app ‘info’ icon you access the app detail page and can get access to a URL directly to your app.

Using the 3rd icon… the ’i’.

So, the link looks something like this:
https://web.powerapps.com/apps/[GUID]

This link opens the PowerApps app directly in the browser – so something you could add to a SharePoint page if you wanted users to have more direct access to the app from anywhere.
Note: If you provide a direct link consider who has access to the app (Sharing) and the data (SharePoint list, or other data source). Providing a link to someone that doesn’t have the access they need could be irritating for users.

Link to Screen

The concept seems simple.

Add a parameter to the app URL
Add logic in the app to grab the parameter value and determine which screen to navigate to

Using the web.powerapps.com link shown above I added a new menu item to my SharePoint menu:

This links directly to the app and the default screen.

Then added the parameter to the string for a second link:

This links to the specific screen (after the formula below is also added)

In the PowerApp, set the OnStart formula to something like the following:

Set(startScreen,Param(“screenid”));If(startScreen=”2″, Navigate(BrowseRankingScreen,ScreenTransition.None),Navigate(BrowseScreen1,ScreenTransition.None))

This is essentially two lines of ‘code’:

Set(startScreen,Param(“screenid”));
Param grabs the parameter passed in by the URL string and sets it to the ‘startScreen’ variable within the PowerApp
If(startScreen=”2″, Navigate(BrowseRankingScreen,ScreenTransition.None),Navigate(BrowseScreen1,ScreenTransition.None))
This references the ‘startScreen’ parameter to determine which screen should be displayed. This particular example is ‘one or the other’ – defaulting to the normal start screen if a parameter is not passed in. You could use a variation of the formula to essentially do a ‘switch’ or multiple ‘ifs’ if you wanted options for a bunch of different start screens.

Once the pieces are all in place, you have the option of linking directly to a specific page in a PowerApp app from SharePoint or anywhere else an HTML/URL link is allowed.

Notes:

Be sure that any screen you are choosing to start from will work when directly navigating to it. Depending on how the app was built the screen may normally require data or settings from other screens to work correctly – as when a record selection has been made before coming to a detail screen.
There were references to the parameter only working with and/or forcing the parameter name to lower case. I have not verified if this is still the case.
There were also reports of caching issues so you may need to try a few times to get the links working properly. Kind of irritating, but it eventually worked.
Be aware of the parameter type/value being used. In the example above we used: ?screenid=2 which actually passed in a *string* value of “2”. This required the formula to use the string version as well: startScreen=”2” (correct) instead of startScreen=2 (incorrect). Easy mistake to make and the difference between your formula working or not. If you choose to pass in a non-numeric string it might make readability better down the road: ?screen=thisscreen.

References

PowerApps and SharePoint–Top X Wish List

Someone told me that it’s a good idea to always have a ‘top X’ things you’d fix, add, or change in a product. I first posted my PowerApps and SharePoint wish list about a year ago.
Top X Things Needed to Make PowerApps Awesome for SharePoint

Since last time

Embedded forms have been introduced.
SharePoint Online now supports customizing SharePoint list forms by using PowerApps. It’s a pretty awesome improvement. Still a ‘step one’, but a pretty huge step.
The other two things I mentioned are still on the list and reframed a bit below.

The New List

Full fidelity for complex field types – The more I use PowerApps, the more I want to be able to use the SharePoint column types that we’re using in many of our lists. Namely Option columns and Lookup columns. At the moment there are stumbling blocks in PowerApps when using these fields and things like GroupBy. PowerApps just doesn’t recognize the data in the way we need it to… yet.
True, there are work-arounds for some use-cases, but the fewer of these we need to deal with going forward, the better. Because this actually hampers some development I’ve moved it to the top of my list.
PowerApps Web Part – In order for users to have a good experience when in SharePoint and using PowerApps, it’s got to be smooth. Right now it’s still very ‘jerky’ when in SharePoint and opening an app… “hold on while we load up this other thing…”.
As I stated in the original post, this is critical for SharePoint ‘view’ replacements.
Yes, we have iframes as a work around. That’s ‘OK’ but just now how smoothly integrated it should be in my opinion.
When creating an app from the SharePoint interface give us layout options rather than defaulting to the phone layout. Definitely realize this was a ‘first step’ and are grateful for it, but time to take the next step. This will also be a big deal once a PowerApps web part (see #1) is ready.
Workaround: Yes, you can do this when starting from scratch in PowerApps, but you lose out on the gains of using the wizard.
From SharePoint: Create an app from a SharePoint view.
From PowerApps: Create an app from a SharePoint view. Seems like these would be the same thing, but because it’s actually the integration of two products there’s work that needs to be done on both sides for this to work. When choosing a data source it currently gets you down to the list level – ‘just’ need to extend one more level down to selecting a view. (I say ‘just’ but fully realize there’s a LOT of work behind the scenes to make this happen – keep it up engineers!!).

Other items

More things in no particular order:

Keep improving PowerApps forms for SharePoint. Wizards, better UX, or whatever it takes to make this both easier for power users (non-dev). Great start – really, I don’t want to sound ungrateful – it’s awesome. Just keep improving on it.
1. Improve forms UX for (non-dev) power users.
2. Make forms available for anonymous and external users. This works for SharePoint, but not the PowerApps forms quite yet (I assume this is at least in part because PowerApps itself is not available to non-tenant users).
I don’t have a good name for this one, but essentially a ‘code view’ and search.
Ex. When creating an app from SharePoint it creates a nice 3 screen app with a search baked in. If you delete the Search box, a number of other controls break because they have references to the Search control. It would be nice to have visibility within the PowerApps Studio to where formulas reference a control so makers can update references.
PowerApps forms available in the SharePoint mobile app
Responsive (Canvas) PowerApps. Sounds like the model-driven apps will be responsive. When will we get the same responsive options in SharePoint forms and canvas apps?

The list will continue to evolve…

PowerApps + SharePoint Drop Down Thoughts

This probably falls somewhere under app design, governance, and tips and tricks in the cross-over space between SharePoint and PowerApps.

When creating a list in SharePoint and needing a field with options in it, we need to decide between the relatively static ‘choice’ field with the options baked in to the control, and creating a Lookup control that pulls its options from another list. They both have their place. Choice fields tend to be a bit simpler while Lookups come with a little more overhead, but are good from a data perspective with consistency.

With the introduction of PowerApps and creating new design surfaces for your list data the Lookup option extends that consistency a bit further and may make you choose Lookups even more. When it comes to CRUD forms, both the Choice field and Lookup fields work great (way to go PowerApps and SharePoint teams!). Building other interfaces, however, may encourage SharePoint users to lean more on Lookups.

The Scenario

A default app created by the ‘Create an app’ option from SharePoint gives you a 3-page app that starts with a gallery view filtered by a Search control. I’d like to change the filtering of the page to be a drop down of a Status field. Two options we can use to accomplish this are to create a drop down with static options (like a the SharePoint Choice field) or to create a drop down connected to the same SharePoint list as the Lookup field.

Option #1 – Static options

The initial reason I considered this was because I didn’t actually want to give (PowerApps) users *all* of the options that are available in a (SharePoint list) choice field. Using a static options control allowed me to just display the options I wanted them to see. This is easily implemented using the method covered in my last post – Simple Drop Down Options – and using values that exactly match your list field values.

One possible upside to this approach is that you could use different text or drop down options that might make sense to users but *don’t* match your list values and then use formulas to match your drop down control values to your list values. For example in the above image I have a number attached to each option in my dropdown which some folks might prefer to leave off: “Paid” instead of “5 – Paid”.

The downside of this approach is I now have one more place where I’m required to maintain the list of values. If I update the Choice field in my SharePoint list I might also need to update the drop down control in PowerApps.

Option #2 – Lookup field options

This seems like how the drop down control was intended to be used. Rather than having to manually add values you can simply connect the control to an existing SharePoint list and have the exact same values as the SharePoint list column.

1. Add an additional data source to your PowerApp

2. Add the Drop Down control and set the Items value. By default it’s already set to a sample data set.
3. Change the Items property to the new data source and field to display and boom. Done.

4. Switch to the Advanced properties for the drop down control and set a default value for it.

5. Finally, change the gallery control to use the new drop down as a filter instead of the Search box (key change is highlighted).

From: SortByColumns(Filter(‘Sponsors 2018 April’, StartsWith(Company, TextSearchBox1_1.Text)), “Company”, If(SortDescending1, Descending, Ascending))

To: SortByColumns(Filter(‘Sponsors 2018 April’, (Dropdown1.Selected.Value = Status.Value)), “Company”, If(SortDescending1, Descending, Ascending))

NOTE: When using this approach It is important to understand how Delegation works with PowerApps and SharePoint in order to ensure complete and correct data is being surfaced in PowerApps. See references below for more information.

The most notable upside to this approach is that both the PowerApps drop down control and the SharePoint list column are pulling their options from the same data source and changes to that one list will update both the SharePoint list options AND the PowerApp.

Finally, as was mentioned in Option 1 – if you don’t want to make all the options available in the drop down control you can still accomplish this here by filtering the list like this:
Items = Filter(StatusOpts, StartsWith(Title,”2″) || StartsWith(Title,”3″) || StartsWith(Title,”4″) || StartsWith(Title,”5″))

Conclusion

The added benefits of using a separate list in SharePoint as a lookup source (Option #2 above) for SharePoint list data *and* PowerApps controls seems the better option for longer-term management.

References

Drop down controls in PowerApps
Simple Drop Down Options
Understand delegation
Delegable data sources (including SharePoint)

PowerApps – Simple Drop Down Options

I’ll just file this under ‘recommended documentation updates’.
(Which I submitted on the corresponding docs.microsoft.com page)

Playing around with some UX in PowerApps I wanted to add a drop down control with simple, static values in it and couldn’t find an example of how to do it on the Drop down control in PowerApps page where I’d expect to find it. What we’re talking about is the Items property and formula. After poking around a bit I found the example I needed in the Examples section of the List Box control page:

Yields the following:

Simple.

References

Spring and More, Conferences Galore!

As SharePoint and Office 365 continue to evolve, the SharePoint community seems to be as strong as ever. There are a bunch of conferences coming up over the next few months. Here are a few notable ones:

March 2-3 – North American Collaboration Summit – Branson, MO
Better hurry up if you want to make this one. There are a ton of great speakers in town for this one as it’s just before the MVP Summit in Redmond. So catch these folks while you can. It is a great value for your time and a nice little escape from Winter for a few days.
FYI – It *is* drivable from the Twin Cities – we’ve done it a few times. Smile
Discount Code: COLLABSUMMIT50

March 26-30 – SharePoint Fest – Washington DC
A quality SharePoint, Office 365, Azure, and Microsoft Teams event offered several times a year between DC, Seattle (August), and Chicago (December). These folks do a great job. They’ve been doing this ‘series’ for a few years and they just seem to get better. They’ve recently switched to a longer 5-day format that includes 2 days of workshops and 3 days of sessions. Be sure to check them out. I’ll be doing a workshop and 3 sessions at the DC event.
Discount Code: PrestonDC100

April 7 – SharePoint Saturday Omaha – Omaha, NE

April 14th – SharePoint Saturday Twin Cities – Minneapolis, MN
Back for our Spring event with registration just opening up today.
FREE

May 5 – SharePoint Saturday Chicago Suburbs – Chicago, IL

May 21-23 – SharePoint Conference North America – Las Vegas, NV
Note: This is NOT a Microsoft-run event like previous ‘SPC’ events, but it IS Microsoft sponsored, backed, and endorsed. There will be a LOT of product group folks attending and speaking in addition to plenty of community-recognized experts, MVPs, etc.
Discount Code: https://t.co/K9n43i1UOr

And of course there continue to be SharePoint Saturday (SPS) events throughout the US and the world. Please remember, not all SPS events are listed on the SPSEvents site – Including OURS – for a variety of reasons. So keep up to date with your local and regional groups so you don’t miss out on a great event!

So much information, so little time. Hope to cross paths with you at some of these!

SharePoint [Me] Filter in PowerApps

SharePoint has a couple of built-in filters for column values: [Today] and [Me]. These allow users to filter views by dynamic data. For example you can create a view called AssignedToMe and set a filter like so:

Using this approach site admins don’t need to create views specific to each user, but can use the [Me] filter on a single view that applies to each individual user. As a user visits the view, they see only the records that are specific to them.

How does one do this in PowerApps when filtering data? Good question. This came up during our Minnesota SharePoint User Group (MNSPUG) meeting today and good old Brian Caauwe knew the answer: rather than using [Me] we use the User function in PowerApps.

On a gallery or data table object, our Items property might have a formula something like this:

Items = Filter(‘IT Request’, AssignedTo.DisplayName=User().FullName)

Note: When bringing fields over from SharePoint, PowerApps translates the CreatedBy and ModifiedBy fields to something a little different. That might be a little confusing when getting started.

CreatedBy –> Author
ModifiedBy –> Editor

Good questions from the attendees today and good catch by Brian with the User() answer. Smile

References

PowerApps: Rules for Conditional Formatting

Well done PowerApps team!! Just days before Ignite they rolled out Rules for Conditional Formatting in PowerApps. You could technically get the same functionality with building out formulas, but the new (experimental) rules bypass this and are MUCH more end-user friendly.

Check out the blog post.

AND take a look at the supporting documentation that’s already released:
Create a rule in PowerApps

For those of us coming from a SharePoint background this is creeping closer to functionality that was available back in the days of SharePoint Designer. As soon as a true web part is available to embed a PowerApp in a SharePoint page (hopefully the new “modern” pages) users will be able to replace SharePoint views with robust apps that include conditional formatting and other goodies not currently available in SharePoint alone.

Exciting stuff and more to come!!