PowerApps: Use a SharePoint list for PowerApps Styling

When developing with PowerApps​​ we often want to store information in a SharePoint list and access the information in the PowerApp. The three main reasons are to store the styling code, settings or just for data storage. In this post I will explain how to connect a SharePoint list to PowerApp and use it for the styling of the PowerApp. You will have a central location to maintain the styling of one or multiple PowerApps.

Connect​​​​ to PowerApp

  1. Create a SharePoint list called PowerAppStyling ​with the following columns.
    • ​​Title (single line of text)
    • Setting (single line of text)
  2. Activate versioning on the SharePoint list.
  3. ​Create a PowerApp.
    • ​In my example I created a PowerApp from a SharePoint list.
  4. ​Click on the tab View followed by Data sources.
  5. Click on Add data source.
  6. Click on SharePoint (for me the last option).
  7. Select the correct site or provide the URL and Click on Go.
  8. Select the the list and click on Connect.
  9. The list is now connected and can be used in the PowerApp.

Use for SharePoint list for s​tyling

We need the styling as soon as the PowerApp starts, so we need to store the styling in a collection when the PowerApp launches. If you can avoid creating collection on startup do so. You want the PowerApp to laod as quickly as possible. ​In the SharePoint list add the required styling code. You can use the following example.

  1. Open the PowerApp.
  2. On the first screen, find the OnStart action.
  3. Create a collection called Collection_Styling to store the PowerAppStyling​ datasource.
  4. When accessing the styling code, always get it from the collection. This is faster than accessing the SharePoint list.
  5. Create a label called Label_Style_Example.
  6. Set the Text to This is my style.
  7. Set the font color of the label.
  8. Set the fill (background) color of the label.
  9. Set the border color of the label.
  10. Set the border color of the label.
  11. The label will now look all black because the Collection_Styling is still empty, the PowerApp has not been started yet.
  12. Run the PowerApp and see the result, sometimes you need to restart the PowerApp multiple times.

Office 365: PowerApps Examples of often used formula

Microsoft PowerApps is part of Office 365 and provides users the ability to create and use mobile apps that are connect to data within and even outside of Office 365. PowerApps is a very powerful tool to help users on a day by day basis to do more, faster and easier. Every user can create basic PowerApps that can be used within in minutes, but the best Apps need a bit of customization.

PowerApps uses formulas, similar to Excel formulas, to create the desired behavior, design and interactions. In this blogpost I will share a couple of very useful customization, which we will be using quick often.

Search in a gallery

The default solution for search is that the value entered in the search box will search in the title field of the data in the gallery. This will already work when you created the PowerApp based on a SharePoint list

  1. Insert a Text input element
  2. Set the hint text to Search for items
  3. Set the default value to blank
  4. Set the Items property of the gallery to
    Filter([Name of datasource], StartsWith(Title, TextSearchBox1.Text)
    
  5. The complete default code with sort funtion on the title field is as follows. You can use this if you have a button called SortDescending1.
    SortByColumns(Filter([Name of datasource], StartsWith(Title, TextSearchBox2.Text)), “Title”, If(SortDescending1, Descending, Ascending))

Search on multiple fields

For searching on multiple fields it is easier to use the search function than the filter function. It is possible to do it with the filter function but it will become a very long and complicated formula.

  1. Insert a Text input element
  2. Set the hint text to Search for items
  3. Set the default value to blank
  4. The syntax of searching on multiple field is as follows
    Search( Table, SearchString, Column1 , Column2, … )
  5. Set the Items property of the gallery to
     Search(Tabel1_1, TextSearchBox1.Text, Title, Description)

Filter a gallery

Many PowerApp will use filters to provide users with a fast way to find the required data.

  1. Insert a Drop down control
  2. Name the drop down ddStatusFilters
  3. Connect the drop down to the status options, I will connect it to a collection called StatusFilters
  4. Create a collection called StatusFilters, that will be created on the Onvisible property of the screen
    ClearCollect(StatusFilters,{Status:”New”},{Status:”Approved”},{Status:”Rejected”})
  5. Set the Items property of the drop down to
    StatusFilters
  6. Set the Items property of the gallery to
    Filter([Name of datasource], ddStatusFilters.Selected.Value = Status)

Filter and search combined

  1. Create a search control and a filter control, see examples Search in a gallery and Filter a gallery
  2. Change the Items property of the gallery to
    Filter(SpecialistRequests, ddStatusFilters.Selected.Value in Status || TextSearchBox1.Text in Title)
  3. This will filter the status field on the selected filter in the drop down and filter the title field on the text in the search box.

Hide fields based on a condition

Field can be hidden based on a condition, this will help you make the form of a app more dynamic and easier to use. In this example we will hide the comment field if the status is new.

  1. Open the EditForm
  2. Change the Visible property of the Comment field to
    StatusDataCardValue.Text<>”New”
  3. StatusDataCardValue is the name my DataCardValue assosiated with the Status field.