Welcome to the second blog of this series, in the coming weeks we will get closer to an app that makes it possible to navigate, add, change, delete items in a SharePoint list and make the content in this list more appealing.
In this blog we will talk about filter () and sort () along with the if () function. Personally, I use the filtering and sorting functions a lot to display the data in a clear and orderly way. Even more often I use the if function, probably not unknown to Excel users here. In Power Apps I use this feature to test a condition and I can’t even begin to describe in how many ways and variations I use it. If you’re following this series step by step it will result in this functionality at the end of this blog:
We’ll begin by opening the app we created in the previous blog. Below you can see some records I have added to the SharePoint database to make use of the filter function. The screen can’t display all of the records due to the large number of records. It’s time to apply a filter.
For each controller you can find the properties in under ‘Advanced’ in the right banner of the studio, or in the dropdown in the upper left. Select the gallery control “gal_People” and go to the Items property.
We want to filter records with the city “Bloomington” for this use case. As a result, the formula is as follows:
Filter: Filter functions require a collection as a first parameter. The following parameters allow you to add conditions to the filter. This example filters items with Bloomington as a city.
You have to adapt the code for different filters, so this doesn’t seem very user-friendly. So let’s see if we can apply the filter using the a tekst input controller.
Add a Text Input Controller and name it “txtinput_FilterCity”. The filter function must now use the text from “txtinput_FilterCity” as a condition. We can do that by replacing “Bloomington” with “txtinput_FilterCity.Text” in the items property of the gallery control “gal_People”. Then you can do the following:
In this concept it is very error sensitive, if only there was a way to use a dropdown as the filter. Wait, there is, lets see how we can apply that.
We need to create a dropdown controller called “drpdwn_FilterCity” and fill it with all the unique values in the city column. We will use the Distinct() function in the Items property of the dropdown controller “drpdwn_FilterCity” to fill this dropdown.
Distinct(): The distinct function results in a list of all the unique values in a column. You can see this in the next illustration if you apply this formula.
Now that we have configured the “drpdwn_FilterCity”, we should apply it to the “gal_People” gallery filter and replace the textinput controller with the dropdown. As you might notice, whenever the selection is empty, the gallery also stays empty. We can solve this by applying a condition to check to see if the “drpdwn_FilterCity” has no item selected.
Blank(): returns a Boolean value (true/false) if the parameter is blank or not.
If(): The first parameter is the condition you want to test, in this case we want to see whether the dropdown is blank. Second and third parameters describe the action that follows a true or false. Because this If function is in a filter we just to return the ‘true’ value when the dropdown has no selection. If the dropdown has a selection, we want it applied to the filter. A little to abstract? Below I will show you how this filter turns out when we apply it in the “gal_People” gallery:
NOTE: you will get a delegation warning on this filter (the blue lines and the exclamation mark in the yellow triangle). We will deal with that in another blog in this series.
Now we’ve filtered the list in a constructive way, we can also sort the list. We can do that by simply applying the sort function.
Sort(): the first parameter needs to be the database you want to sort. In this case that’s the filtered data from this blog. In the next parameter we select the column we want to sort, lets pick the given name for this example. In the last column we define what the way of sorting is, ascending or descending.
At last, if you want to unselect an item in the dropdown, you first need to find the “AllowEmptySelection” property and set it to “true”.
Now we have a way of to filter and sort our list on a column. I hope that you got through this blog and that it was all clear to you. In the next blog we’re going to navigate to a screen in which we see more details of the item. If you have any questions or suggestions, feel free to leave them in the comments. I will answer them as I can. I am looking forward to your feedback and suggestions.