Category Archives: PowerApps

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.

image image

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
image

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.
image

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

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:

image

Yields the following:

image

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:

image

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.

image 

Check out the blog post.

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

image

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!!

SharePoint Fest – Chicago

Heading to SPFest Chicago in December? I’ll be presenting a few sessions on PowerApps.

PWR104 – PowerApps for SharePoint Users
PWR202 – From SharePoint to PowerApps: Evolution of the ‘Intake Form’ Solution
(Check out the sessions here)

There’s going to be a LOT of great speakers, sponsors, and content. Fresh out of Ignite there will be a lot to talk about.

Interested in attending? Here’s a discount code (click for registration):
SPFestChi17

Hope to see you there!!

Microsoft Ignite 2017 !!

UPDATE: They’ve managed to fix our session overlap – new location and time shown below!
UPDATE: Added links to the Tech Community Discussions for each session. Chime in! 

In a relatively late development, I’ll be attending and speaking at Ignite in Orlando this year! I wasn’t planning on either, so this is exciting. Working on both SharePoint and PowerApps, I’ve got one theater session and will be co-presenting with Cathy Dew on a full breakout session. Links and details below.

Microsoft Ignite in Orlando. Sept. 25 – 29th.

Evolution of the Intake Request Solution from SharePoint to PowerApps
OCCC South – Expo Theater #4
Wednesday @ 1:05 – 1:25
Tech Community Discussion <- Talk to us! 

w/Cathy Dew:
Create Custom forms and digital experiences in SharePoint using Microsoft PowerApps
OCCC W307
Thursday @12:30 – 1:45
Tech Community Discussion <- Talk to us! 

Yes, if you’re looking at the times the two sessions actually overlap right now (fixed!). We’re working on that. Smile with tongue out Worst case I leave Cathy’s session a little before we’re done and RUN to the other building. Let’s hope it doesn’t come to that. Either way we’ll make it work.

SUPER excited to be able to attend and contribute. Hope to see you there!

JS Link – No More HTML in Calculated Field Change

Didn’t see that coming.

More than a few times I’ve had folks bring up solutions using calculated fields to do some of the same things we do with Client-side rendering (CSR) and JS Link in SharePoint. That was fine at the time. Just another way to get to the solution using creative out of the box capabilities – until now.

Microsoft implemented a change yesterday (June 13, 2017) that HTML or script elements in calculated fields would be turned off. Apparently this was an ‘undocumented use’ of the feature. The system is now escaping special characters and replacing the column with a blank in the list view.

Now What?

The short answers – in no particular order:

  1. Client-side rendering via JS Link property of web parts. This only works for Classic mode. If you are using Classic mode, this is likely the ‘best’ short term answer to broken interfaces until a longer-term solution is implemented
  2. Third party products
  3. SPFx Extensions or customization
  4. PowerApps

CSR and JS Link: There are multiple ways to implement JS Link, some via the browser (how I use JS Link) and via deployed coded solutions (Dev only). As mentioned in other responses JS Link does NOT work in the modern experience. It will only work in the Classic mode (as long as it is around). There have been NO dates announced for getting rid of Classic mode at this time. If you choose this direction it’s worth noting that if you’re comfortable writing script in the calculated field you’ll likely be comfortable writing the code needed to get CSR to work. I’ve got plenty of examples in the link included here.

Third party products. I’ve seen folks mention something called Skybow but I’ve never seen or used it. Only mentioning because it was mentioned by someone in the community I trust.

SPFx Extensions and customizations: This is the ‘approved’ development path for deployed solutions. If you are a developer and/or are creating solutions that are used across a broader scope this is likely the path you should go down.

PowerApps: This is likely the long-term replacement for solutions you used HTML or other scripting in your calculated field for (hand slap from English teacher). Embedded integration with SharePoint isn’t there yet (well, it kind of is…), but has been announced. You can start building the views/solutions you need, but they won’t be ‘really’ embedded in SharePoint yet – web parts and/or other tools are coming soon (they’ve been announced).

Note: Much of the attention to PowerApps has been in the area of forms, but views will be a part of the capabilities as well. They’ve already made improvements with introduction of controls like the Data Table control. Check out my posts on PowerApps for SharePoint users, specifically things like ‘List View’ Layouts.

Update

Migration Note: Do not forget to take this into consideration now as something to look at when migrating from an environment where special characters (HTML and scripting) work into an environment (O365, etc.) where they do not. You’ll need a plan to replace or update your field customizations.

Summary

Unfortunately this seems like a pretty sudden change. I certainly had no idea it was coming. For folks that were using this approach there’s a lot of scrambling going on right now dealing with blank columns that weren’t blank a few days ago. Depending on how complex the scripting was, some fixes might be as easy as stripping the HTML and script out and displaying a simple value. More complex solutions are going to take some effort to redo, either with a new approach, new code, or 3rd party software to get the business functionality back.

For simple HTML replacement, JS Link could be an easy switch. If you’ve never used it before check out the Hello World post and then the KPI post. Those will cover a lot and introduce you to the JS Link concept if you need to dig deeper for more complex solutions.

Good luck!

References

PowerApps – No Items to Show

In a previous JS Link / CSR post we covered how to handle what is displayed in a web part when there are no items to show. SharePoint’s default message is an ambiguous:
“There are no items to show in this view of the [listname] list”

Not an awesome user experience. Something a little more contextually relevant might be more helpful to users – either in a simple list view, or in a more complicated solution.

A PowerApps Gallery control isn’t any better – merely showing a blank record. The Data Table control, on the other hand, has a handy ‘NoDataText’ property that can be configured to display a message when the control is empty. 

So, what do we do when using Gallery controls – until a ‘NoDataText’ property is added and/or the Data Table control allows for more configuration options? As is the answer with many things in PowerApps: We use a formula.

Using an appropriate text field control within a Gallery control, we can replace the Text property with a formula like the following:

If(CountRows([_List Name_])=0,”[_Put your message here_]”, ThisItem.Title)

For a SharePoint list named: DefaultList you get the following:

If(CountRows(DefaultList)=0,”Nothing to see here”, ThisItem.Title)

The ‘CountRows’ function as you might guess returns the number of rows in the list.

The ‘If’ function tells the control to set the displayed value of the control to “Nothing to see here” if the condition (there are 0 rows) evaluates to ‘true’. If not, the value of the control is ThisItem.Title – the normal field value we would expect in the control. The text is only displayed when there are no items in the list. 

That’s it. Smile 

References

PowerApps – Data Table Control

PowerApps takes another step in the direction of SharePoint lists. This isn’t intended to be a knock on anything, just an effort to put it into perspective for SharePoint user. With a data table (grid) control PowerApps is able to look a lot like a SharePoint list view.  Smile  PowerApps users now have a control to more rapidly replicate SharePoint view-looking solutions. True, it’s not just SharePoint… but that’s where many users are coming from… and the table/grid layout will be a nice comfort zone for ‘SharePointers’.

If users want to display items from a data source, they have a variety of options in PowerApps – initially centered on the gallery control. The gallery has a LOT of flexibility, allowing users to select gallery templates or build layouts from scratch. Layouts might look like a grid layout, or like a ‘card’ with fields arranged more like the old announcement web part in SharePoint. With this flexibility, however, comes more effort. Not a bad option, just a trade-off. 

The Data Table control has fewer configuration options, but is a big time-saver in that it drops a complete control on the PowerApps screen and allows for a quick configuration to a data source. Fewer options (for now), but quick and easy creation.

Once on a screen, the Data table can be found in the Insert tab: image

Once inserted on a screen, the control needs to connect to data. Clicking on ‘Connect to data’ will allow the user to use existing connections or add a new one. image

In the properties column before connecting to data:
image

Connecting to the SharePoint site, you need to enter the URL of your *site*, not the *list* directly. Once connected to the site you’ll be able to select the list you want.

Once a list is selected, the fields available are displayed. Fields are selected in the order they are to be displayed from left to right. They can be reordered by dragging the columns up and down in the property pane shown here:
image

The screen view and property page with fields selected:
image

As the fields are selected, they show up on the control itself. Boom. That’s it for starters.

The control has a standard collection of basic properties available for configuration, though I expect the options to expand over time. 

Notes

  • CSR – In SharePoint there was no option to configure the text displayed when a web part was empty without getting into some Client-Side Rendering or JS Link customizations. With the Data Grid control there is a “NoDataText” property that takes care of this capability out of the box. Yea!
  • Filtering and Sorting – These aren’t the only other properties of a SharePoint view, but they are the next pieces after choosing the fields to display. See how to use formulas in PowerApps to control the ‘view’ sort and filter options.  
  • Connected controls – The Data table can be connected to another control, like a form where record data can be displayed and edited. Definitely check this one out in the data table control documentation (listed in references below).

Top Wants

This is a great start so I don’t want to appear too negative. I’m a fan of Microsoft’s newish approach to getting new features out and then moving forward with steady improvements over time. With that, here are the top things that came to mind when initially trying the new control:

  • Column widths, row heights, and word wrap – The ability to control the cells a bit more. You can see in the images above how the field widths aren’t acting like you’d hope or assume. The Title field should be bigger and the Priority field should be smaller. It’ll get there.
  • Column formatting, Conditional formatting – Right now the font controls are limited to the headers and the rest of the cells. It will be nice to have column/field level formatting and conditional formatting – one of the most requested and customized features in SharePoint views. 
  • A supported / best practices method for embedding these PowerApps into the SharePoint interface. THAT is how we’ll be able to replace and extend the current SharePoint list view with a PowerApp.

References