PowerApps – Filter SharePoint List Items

by | Sep 4, 2017 | Collaboration | 0 comments

If your organization is like most, your employees’ primary computing device is their phone. The rise of mobile device popularity has forced technology companies and their customers to rethink how systems are accessed and engaged by users. However, not all companies can afford a development staff to create and support mobile interaction. Microsoft created a solution for this.

Microsoft’s PowerApps provides a great way for non-technical users to create and publish custom forms for SharePoint lists. Even novices can quickly spin up mobile and tablet versions of forms for submitting, viewing, and editing SharePoint list items by simply letting PowerApps create base forms.

PowerApp Forms include a “gallery” form that automatically shows items in a SharePoint list. This is great when you use the built-in search controls to find an item in the list or to use your application for submitting new items to your SharePoint list.

But what if you want your gallery to only show specific list items? PowerApps has a Filter function, so that should be easy, right? Well, maybe…

The Business Problem

To help illustrate the issue and the solution, I’ll walk you through an example from our experience. We created a “Time off Request” application in PowerApps so our IT consultants and managers, who work remotely and at client sites, can make and approve time off requests from their smartphones. We have an existing SharePoint list we use to track time off requests, so it only took a few minutes to create the base application in PowerApps.

For our managers, I added a button in the application that takes them to a gallery form where they can see open requests that are assigned to them. Our SharePoint list is already set up to control access to individual time off requests based on permissions. Therefore, I only needed to use a filter to display the open requests. In our SharePoint list, we use a choice field to identify the state of individual requests.   As managers approve or deny time off requests, the workflow updates the label in the choice field, and our “Open Request” view manages the display.

PowerApps supports a wide variety of data sources, including SQL databases, SharePoint lists, and third-party data sources. For SharePoint lists, however, we found that the PowerApps Filter function does not recognize Choice columns in the Filter function.

How do we get around this?

In our solution, we created a number column called “PowerAppFilter” as a hidden column in our SharePoint “Time Off Request” list.  We then created a SharePoint workflow that updates the value in the “PowerAppFilter” column each time an item is created or edited. The workflow reads the value in the “Status” field of the list and adds or updates the number value to the “PowerAppFilter” column. For requests that have not yet been processed, the list Status field equals “Open” and the “PowerAppsFilter” field equals “1”.

In the PowerApps Filter function, I set a filter on the Items property to only display items where “PowerAppFilter” = “1”.

The function looks like this:

Sort(Filter(‘Time Off Request’ ,PowerAppFilter = “1”), Created, Descending)

Now my managers only see their open requests, eliminating the need to search through all requests, thus speeding up the approval process.

Do you have more questions about PowerApps or SharePoint workflows? Need help to evaluate your work processes and determine what collaboration solutions can work for you? Peters & Associates has been assisting organizations in solving business problems since 1981. Give us a call or drop us a line – 630.832.0075 or info@peters.com.

Check out these related articles to get the most our of your PowerApps services: