This isn't so much a problem that others won't figure out on their own, just that I think it isn't obvious how to do it in a reasonably consistent manner that you can use almost everywhere. So I've included a walkthrough below to make it easier to get started.
Example using a Data Table
To jump right to it, this is my generic code I put in the Items property for approaching these problems:
I've broken it down into where the orange section relates to the sorting, the blue section is the fuzzy text search, and the green section is the drop-down filter(s). For newbies, understand that the "inner most" code executes first (green), then the next layer (blue), then the last layer (orange). Formatted code generally will include indentation that makes this slightly easier to read (as I've done above).
The above code gives you a framework for doing both manual hard filtering through a drop-down (or similar) as well as also allowing simultaneously for open text searching. It also includes the logic for sorting based upon some variables that you'll set when column headings are clicked.
There are still a few scenarios where I alter my code outside of this, but most of the time, this is what I use by default and it works for most scenarios.
How it Works
- An open text searchable field
- A dropdown where we are going to use as a hard filter (filter1DDown)
- A second dropdown where we are going to use as another hard filter (filter2DDown)
- A data table listing all of our items
- Clickable header controls above each column to allow for (de-)selecting/changing sort
- An icon on top of each header control that displays the sort direction
Something like this:
The end-user can type in whatever text they want and just do a search. The moment they start typing, the information they type in that field begins filtering down results. Also, they can choose to select from one or more filters via the dropdowns. These are hard exact-match filters that you might use for things like a specific Location, or Status, etc. All of these methods can be combined and all work simultaneously (i.e. select a location, a status, and start typing the name of the person).
For the sorting, the user can choose which column they want to sort by and quickly change direction or remove the sort intuitively by clicking the appropriate header. The behavior of clicking a column header changes based upon prior values of the column name and direction.
NOTE: While this doesn't handle multi-sort (and I generally find users confused by that), you can adapt this by using a Collection of columns you want sorted and some additional logic steps within the Sort.
The code snippet at the beginning reflects how this would be handled by your Data Table (or Gallery) control.
Hard Filtering
NOTE: You would change the bold text to reflect your own control names, column names, and Collection or data source name.
The name of our actual collection (or data source) that we're working with in this example is cachedItems. The Filter() works by deciding if the end-user has changed the default value for the drop-down from the first value ("All" in my own lists). If they haven't, then the value simply is "true".
How Filter() segments work is they are evaluated to see if they are true/false. If a specific row in your Collection evaluates to true then it is included, if false then it is not. So by simply saying if the drop-down = "All" we return a true value, that means we really are just skipping this logic. All rows are returned because all of them are evaluated as being true.
If we change the value to anything other than "All", then we use the logic included to evaluate that specific column inside our Collection.
NOTE: You could copy/paste as many drop-downs here as you have (e.g. filter3DDown).
Soft Filtering
When cut out of the code above, this is a little simpler to understand. We take the results of the Hard Filter above, then we do a search to see if the text typed into our search field (named searchInput in my code) exists within those columns. You could add as many columns as you'd like and just include the || (or Or) characters/command between them.
NOTE: Notice the parenthesis that wrap each distinct item. Logical precedence is a thing that can trip you up, so wrapping discrete items you want evaluated inside parenthesis makes certain things will work as expected.
Since we take the first Hard Filter as our input, this allows us to combine search methods on the fly. If all the drop-downs have "All" selected, then we're searching based upon all items in the Collection. If they choose an option in any/all drop-downs, then we are only searching within items that match those choices.
Sorting
This is a section of code that will change depending upon the columns you're sorting by (combining numbers and text sorting can get PowerApps confused). The value of sortColumnName is going to change if/when we click the column header (see below). For now, you can copy/paste this in and change the values that are inside the double quotes and right below it to be the following:
- columnHeaderText -> The actual text displayed to the end-user in the column header control
- columnHeaderName -> The actual name of the column header control
The code above allows our sorting to be controlled by two variables:
- sortColumnName (string)
- sortAscending (Boolean)
By default, all Booleans equal false and all strings are null.
NOTE: As people click on various controls, we're going to change the value of these two variables and use them to control the user experience. When sortColumnName matches the column we're displaying then we will make it appear differently and show the proper sort icon. When sortAscending is true we'll display our icon as an up arrow and a down arrow otherwise.
Column Headers
When we click our "header control" we will place above each column, we will run specific code when it is clicked.
For a basic version, the following is workable:
This will simply change the sortColumnName to the same text as what the control displays and flip the sort from up/down.
A slightly more end-user-friendly version (but less easy for you to read/understand) is:
This one is context-sensitive based upon prior values. It switches to an Ascending sort by the column clicked if it isn't currently selected, then descending if clicked again, then de-selects the sort if clicked again. Changing between columns switches to the default of an Ascending sort for the specific column.
For the fill on the column headers, the Fill is set so that the column header has a slightly different color if we have selected it. In my case, I'm using RGBA(164, 38,44,1) as the base color. Just replace both of these w/ whatever color you're using in the code below:
This gives you a slightly shaded version. You can change the -20% to whatever makes sense for you or even change the color completely if the specific column is selected for sorting.
Sort Icons
Finally, for the icons, this is where some of the annoying complexity shows up.
NOTE: Follow this section closely as it will be heavily modified based upon the names of your controls and which control you place next to your icon.
For each column we want to sort by, we'll place an icon next to text of the column header associated w/ the specific column we're working with. Then we,
- Set their OnSelect set to select the header they are grouped with (e.g. OnSelect -> Select(Label1))
- Set the Visible Property to be if the value of sortColumnName ='s the value of the corresponding column header
- Set the Icon value is set to Up/Down based upon the value of the sortAscending value.
So now place one Icon next to each of the names of the columns you're wanting to sort on within your screen.
Now change the OnSelect to tie it to the corresponding column header (so you don't have to copy/paste the code you already put into the header control). Something similar to:
Select(columnHeaderName1) //change columnHeaderName1 to the name of your control
Set the Visible property to be if the value of sortColumnName ='s the Text that is displayed to the end-user in the corresponding column. Something similar to:
sortColumnName = columnHeaderName1.Text //same change as above
Now finally, set the Icon value for the control to be the following
That's it. The icon will change from the Up Arrow if the sort is Ascending, down if Descending, and will disappear if this isn't the column we're sorting by currently.
Performance
The first thing for me is that if you're going to have a very responsive control in this scenario where end-users are going to be flipping a lot of levers and switches to alter appearance, it is important to cache your data or work with a very small dataset.
If you are constantly querying your datasource and needing to account for delegation, then you (and your users) are going to get frustrated. Bringing that data into the application so you can easily do this in memory is key. Certainly you can do this w/ live links to lists, but you should do everything in your power to get things to a point where you can work w/ them in your app.
NOTE: If you are not caching your data and are going direct to your list, make sure that you Index any/all columns you're filtering/searching on.
Final Thoughts
This is a little more complex than you'd want, but it is a pattern that plays out quite often. Giving users some complex ways to filter/search/sort that ultimately are simple for them to understand is key.
Do keep in mind that if your Sort() code isn't working as expected, check the column types. If you're mixing Numbers/Text, then it is possible that the Sort code as written will get confused. You might need to break out a numerical sort vs. a text sort into a little more complex code vs. the simpler version above.
If you wind up in this situation, the your best solution is to use nested Sorts:
No comments:
Post a Comment
Because some d-bag is throwing 'bot posts at my blog I've turned on full Moderation. If your comment doesn't show up immediately then that's why.
DIAF Visualpath team