PowerApps fetching data from a SharePoint list

14 juni 2018

Last year I started using PowerApps and I really like it. It makes it a breeze to create apps which consume and create data from and to a lot of external applications. This blog describes how to fetch data from a SharePoint list. We are also filtering this data using input from a screen in the PowerApp.

With this how to we will create a PowerApp where the user will fetch and select data from a SharePoint List, add some manual data and the PowerApp will then show the approver for the quote.

SharePoint list

The first step is to create a SharePoint list which contains your data. Please do this according to your own needs. In this example the columns 'Tier level' and 'Cost center' are just plain text. The column 'Manager' is a Person Group column. Please enter some items in the list. The name of the list is 'Tier'.

Okay, the SharePoint part is done right now. Next up is the PowerApp.

Create a PowerApp

In this PowerApp we create an input field, set the format to 'Number'. I've named this field 'InputAmount'. I've also inserted three labels. The label 'valueTierLevel' is the most important from these three labels. We need this name further down the road. The other two are just, labels!

Like I said, the label 'valueTierLevel' is important. The tier level will be calculated from the value of the quote. So the field 'InputAmount' determines which tier level it is and the tier level relates to an approver.

Select the label 'valueTierLevel' and open the advanced properties of the label field.

To determine the tier level, we're going to use multiple If Then Else functions. We have got 2 tier levels (level 1 and 2), USD 5.000 and USD 10.000. And, if everything else fails, we will always show the second tier level. You're free to add multiple levels if needed. If you only need two levels the If-function would be a little less complex.

In the Text property of the label enter the following If-function.
If(Value(InputAmount.Text) < 5000, "1", Value(InputAmount.Text) < 10000, "2", "2")

If this generates an error please make sure you've set the format for the input field (InputAmount) to 'Number'.

Please test your If-function to see the result. USD 5.000 and up should show '2'. If you change the last "2" to something else (3 for example) USD 10.000 and above should show tier level 3. This last option is the 'else' in IF-THEN-ELSE. The example above is an IF-THEN-IF-THEN-ELSE ;)

All right, that's the dynamic tier level depending on the amount entered on the form. Next up is to link this tier level to cost centers and the corresponding manager. Remember the SharePoint list we've created? We need it now.

Use your SharePoint list in the PowerApp

Let's summon the list in our PowerApp

1: Open the view tab in PowerApps
2: Click 'Data sources' and add a data source

3: Select the SharePoint connector
4: Enter the site where you've created the list, all lists in that site will be shown and select the list(s) you would like to use in your PowerApp and click 'Connect'. In this case I select the list 'Tier'

5: Close the drawer

We now have the possibility to use the SharePoint list items in the PowerApp. But first we're going to create a data collection from it.

Data collection

The advantage of using collections is that the data is fetched only once and reused throughout all your screens and forms. Because we are going to use the OnVisible action in this how to, the data is (re)fetched every time the main screen is shown.

It depends on the kind of data you're using if a data collection is the best solution for your app. If your data is highly dynamic then it might be wise to query your data real time and not using a collection. But using a collection will almost certain give the user a better experience because data is available instant after it's loaded in the app.

1: Select the screen and open the advanced properties of the screen
2: Enter the following syntax in the 'OnVisible' parameter. This will create a collection named 'CollectionTier' with all data from the data source 'Tier' (see step 4 above)

ClearCollect(Collectionforblog, Tier)

Trigger the data collection

3: Add a new screen to the app and switch to the new screen and back to the main screen to trigger the OnVisible action and fetch the data
4: Go to the 'View' tab and click on 'Collections' to see the collections in your PowerApp. Only one collection should be shown in this case

In the screenshot above you'll see some grey boxes. In this case these boxes present Person/Group objects. If you click the box you will see the contents of the object.

Filter the data from the collection

In this PowerApp we also need to select a cost center. This selection is then, together with the tier lever, used to determine the approver.

1: Insert a drop down control in your app on the main screen (name it 'ddlCostCenter')
2: Open the advanced properties of the control an remove the default value
3: Add the following syntax to the 'Items' field

Distinct(Collectionforblog, Cost_x0020_center)

This will fetch all distinct values from the column 'cost center' in the SharePoint list.

So we have a tier level depending on the quote, the selected cost center and a data collection from our SharePoint list. Let's combine these three to show the name of the manager.

1: Create two new labels, the first one is just a label. The second one will contain the name of the manager

2: Select the second label and enter the following syntax in the text field

LookUp(Filter(Collectionforblog, Tier_x0020_level = valueTierLevel.Text), Cost_x0020_center = ddlCostCenter.Selected.Value, person_x0020_group.DisplayName)

This syntax first filters all values according to the selected tier level. And from that collection of data does a LookUp on the Cost center column. When a value is found, it shows the display name from the person group field.

Because it's a person group field it's possible to use all available data in that object. For example; use the profile picture of the user :)

A nice excercise for you would be to create a new list item in another SharePoint list with the data you've fetched here :)

Good luck!

Submit a comment