Re: Sort and filter with multiple columns

JR-BejeweledOne · ‎06-14-2021

I am trying to reduce the number of rows in a sort/filter formula from 120+ down as low as possible. I have configured it using this article by Matthew Devaney and it works well.

https://matthewdevaney.com/power-apps-gallery-sort-controls/

However this is based on all 3 of the dropdowns having selected values. I need this to treat a 'blank' dropdown as a wildcard and not look for items where that field is empty. There is also a text input that is used to 'search'. I added the filter criteria into the With function If statement, but it doesn't work as expected.

I found this solution for treating a blank dropdown as a wildcard but was unsuccessful in implementing it.

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Filter-condition-Wildcards/idi-p/117186

The first formula is the base sort/filter and it's working. The second is the filter piece that is used with the text input.

Any assistance in taking this the rest of the way would be greatly appreciated.

@mdevaney

@mrdang

With(
   {wFilterRecords:
            If(
                ProjectFilter = "All Projects", Sort(TrainingProjectsCumulative, 'Project Name', SortOrder.Ascending),
                ProjectFilter = "My Projects", Sort(Filter(TrainingProjectsCumulative, Developer in Filter(Developers, varUser = Manager).Title), 'Project Name', SortOrder.Ascending),
                ProjectFilter = "Active", Sort(Filter(TrainingProjectsCumulative, "Active" = ProjectStatus.Value), 'Project Name', SortOrder.Ascending),
                ProjectFilter = "Completed", Sort(Filter(TrainingProjectsCumulative, "Completed" = ProjectStatus.Value), 'Project Name', SortOrder.Ascending))},

                Switch(
    SortColumn,
    "'Project Type'", 
    Sort(
        Filter(wFilterRecords, 
            'Project Type' = DropdownProjectType.Selected.Value, 
            'Course Type' = DropdownCourseType.Selected.Value, 
             ProjectStatus.Value = DropdownStatus.Selected.Value
        ),
        'Project Type',
        If(ColumnOrder, Ascending, Descending)
    ),
    "'Course Type'", 
    Sort(
        Filter(wFilterRecords, 
            'Project Type' = DropdownProjectType.Selected.Value, 
            'Course Type' = DropdownCourseType.Selected.Value, 
            ProjectStatus.Value = DropdownStatus.Selected.Value
        ),
        'Course Type', 
        If(ColumnOrder, Ascending, Descending)),
    "ProjectStatus",
    Sort(
        Filter(wFilterRecords, 
            'Project Type' = DropdownProjectType.Selected.Value, 
            'Course Type' = DropdownCourseType.Selected.Value, 
            ProjectStatus.Value = DropdownStatus.Selected.Value
        ),
        ProjectStatus.Value, 
        If(ColumnOrder, Ascending, Descending)),
    "Created",
    Sort(
        Filter(wFilterRecords, 
            'Project Type' = DropdownProjectType.Selected.Value, 
            'Course Type' = DropdownCourseType.Selected.Value, 
            ProjectStatus.Value = DropdownStatus.Selected.Value
        ),
        Created,
        If(ColumnOrder, Ascending, Descending)),
    
    wFilterRecords
   )
  )

            If(
                !IsBlank(TextInputSearchBox), Sort(Filter(TrainingProjectsCumulative, TextInputSearchBox.Text in 'Project Name' || TextInputSearchBox.Text in Developer Or StartsWith('Course Type', TextInputSearchBox.Text) Or StartsWith('Project Type', TextInputSearchBox.Text)), 'Project Name', SortOrder.Ascending),
                
)

If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

WarrenBelz · ‎06-14-2021

Hi @JR-BejeweledOne ,

Free-typed so (as you would know) an idea on syntax. I cannot test it and you may have to repeat the sort in the bottom bit, but try this

With(
   {
      wFilterRecords:
      Sort(
         Filter(
            TrainingProjectsCumulative, 
            (
               ProjectFilter = "All Projects" ||
               Switch(
                  ProjectFilter,
                  "My Projects", 
                  Developer in Filter(
                     Developers, 
                     varUser = Manager
                  ).Title,
                  "Active", 
                  ProjectStatus.Value = "Active",
                 "Completed", 
                 ProjectStatus.Value = "Completed" 
               )
            )
         ), 
         'Project Name'
      )
   },
   Sort(
      Filter(
         wFilterRecords, 
         'Project Type' = DropdownProjectType.Selected.Value &&
         'Course Type' = DropdownCourseType.Selected.Value &&
         ProjectStatus.Value = DropdownStatus.Selected.Value
      ),
      Switch(
         SortColumn,
         "'Project Type'", 
         'Project Type',
         "'Course Type'", 
         'Course Type', 
         "ProjectStatus",
         ProjectStatus.Value, 
         "Created",
         Created
      ),
      If(
         ColumnOrder, 
         Ascending, 
         Descending
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

JR-BejeweledOne · ‎06-15-2021

How does this incorporate the text input piece, which should be part of the initial wFilterRecords creation. Also I don't see how it handles 'Project Type' or 'Course Type' or Status not having a selected value. This filtering action allows them to filter the results with 1, 2 or 3 items selected. It should filter only on the item selected. So if a 'Project Type' is selected and not a 'Course Type' or Status, it should only filter on the 'Project Type' and treat the others as though they don't exist and the same if 2 items are selected, it only filters on those 2.

 If(
                !IsBlank(TextInputSearchBox), Sort(Filter(TrainingProjectsCumulative, TextInputSearchBox.Text in 'Project Name' || TextInputSearchBox.Text in Developer Or StartsWith('Course Type', TextInputSearchBox.Text) Or StartsWith('Project Type', TextInputSearchBox.Text)), 'Project Name', SortOrder.Ascending),
                
)

If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

WarrenBelz · ‎06-15-2021

@JR-BejeweledOne ,

Did not actually get to the second bit - a bit brain-dead after the first section - again some syntax ideas.

With(
   {
      wRecords:
      Sort(
         Filter(
            TrainingProjectsCumulative, 
            (
               ProjectFilter = "All Projects" ||
               Switch(
                  ProjectFilter,
                  "My Projects", 
                  Developer in 
                  Filter(
                     Developers, 
                     varUser = Manager
                  ).Title,
                  "Active", 
                  ProjectStatus.Value = "Active",
                  "Completed", 
                  ProjectStatus.Value = "Completed" 
               )
            )
         ), 
         'Project Name'
      ),
      wSearch:TextInputSearchBox.Text
   },
   Sort(
      Filter(
         wRecords, 
         'Project Type' = DropdownProjectType.Selected.Value && 
         'Course Type' = DropdownCourseType.Selected.Value &&
         ProjectStatus.Value = DropdownStatus.Selected.Value &&
         (
            IsBlank(wSearch) || 
            (
               wSearch in 'Project Name' || 
               wSearch in Developer ||
               StartsWith(
                  'Course Type', 
                  wSearch
               ) || 
               StartsWith(
                  'Project Type', 
                  wSearch
               )
            )
         )
      ),
      Switch(
         SortColumn,
         "'Project Type'", 
         'Project Type',
         "'Course Type'", 
         'Course Type', 
         "ProjectStatus",
         ProjectStatus.Value, 
         "Created",
         Created
      ),
      If(
         ColumnOrder, 
         Ascending, 
         Descending
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

JR-BejeweledOne · ‎06-15-2021

Sorry for so many questions, but I still don't see where it handles 'Project Type', 'Course Type' or Status not having a value selected. As indicated previously, 1 or all 3 or any combination of the 3 could be selected.

Because the formula below is an 'And' scenario, if any of the 3 have no value selected it will look only for those items in the data source where that column is empty.

Project Type' = DropdownProjectType.Selected.Value && 
         'Course Type' = DropdownCourseType.Selected.Value &&
         ProjectStatus.Value = DropdownStatus.Selected.Value &&

What I am looking for is this (of course I have this now but my formula is over 120 lines long):

'Project Type' has value, 'Course Type' and Status are not selected: Filter only on 'Project Type'

'Project Type' & 'Course Type' have values, Status is not selected: Filter on 'Project Type' and 'Course Type'

All 3 have values: Filter on all three

'Course Type' has value, 'Project Type' and Status are not selected: Filter only on 'Course Type' and so on.

I hope this makes sense.

Also, as it is right now, it doesn't work.

If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

WarrenBelz · ‎06-15-2021

@JR-BejeweledOne ,

Something like this - I thought you might pick this up from the bit just under it

With(
   {
      wRecords:
      Sort(
         Filter(
            TrainingProjectsCumulative, 
            (
               ProjectFilter = "All Projects" ||
               Switch(
                  ProjectFilter,
                  "My Projects", 
                  Developer in 
                  Filter(
                     Developers, 
                     varUser = Manager
                  ).Title,
                  "Active", 
                  ProjectStatus.Value = "Active",
                 "Completed", 
                 ProjectStatus.Value = "Completed" 
               )
            )
         ), 
         'Project Name'
      ),
      wSearch:TextInputSearchBox.Text,
      wProject:DropdownProjectType.Selected.Value,
      wCourse:DropdownCourseType.Selected.Value,
      wStatus:DropdownStatus.Selected.Value
   },
   Sort(
      Filter(
         wRecords, 
         (IsBlank(wProject) || 'Project Type' = wProject) &&
         (IsBlank(wCourse) || 'Course Type' = wCourse) &&
         (IsBlank(wStatus) || ProjectStatus.Value = wStatus) &&
         (
            IsBlank(wSearch) || 
            (
               wSearch in 'Project Name' || 
               wSearch in Developer ||
               StartsWith(
                  'Course Type', 
                  wSearch
               ) || 
               StartsWith(
                  'Project Type', 
                  wSearch
               )
            )
         )
      ),
      Switch(
         SortColumn,
         "'Project Type'", 
	     'Project Type',
         "'Course Type'", 
         'Course Type', 
         "ProjectStatus",
         ProjectStatus.Value, 
         "Created",
         Created
      ),
      If(
         ColumnOrder, 
         Ascending, 
         Descending
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

JR-BejeweledOne · ‎06-16-2021

I appreciate your help. Unfortunately it is not working. I tried the original base syntax you provided and even just with the 'All Projects', no results are returned and I think I may have confused you as to what I am looking for, so bear with me. Also, I have learned that when learning new complex functions, I often need to see the whole thing and implement it before I can then figure out additions, manipulations etc.

Here is my starting point. As I indicated initially this is fed with an if statement that contains more than 120 lines (I stopped counting there). It works but it's slow and clunky.

#2 is the default and auto-populates all projects
#1 is a text based 'search' filter. 1 & 2 are separate and independent of each other and need to stay that way.
#3 is an additional filter choice
#4 is the sort function

In researching ways to fine tune this and cut down on the lines in the If statement, I came across a solution by Matthew Devaney using the With function along with Sort and filter. I implemented the base process, using the solution provided in conjunction with his blog article, which is the formula I shared in my original post. I am not looking to reduce the # of lines in that formula, it works well.

What I am looking to do is refine it. I am replacing #'s 3 & 4 with 3 dropdown selections for filtering and a function to sort on the columns.

None of the dropdown selections are required and if any one does not have a value it should be treated as an 'All' rather than finding items where the associated column is empty, which is the current functionality. This is where I need the assistance.

The finished product:

#'s 1 & 2 remain the same and are independent of each other.
The original Refine and Sort functions will go away and be replaced by the 3 dropdown selections, #3
#4 is the Sort function

The goal:

Refine the formula provided to allow theSort/Filter sections to treat a blank dropdown as an 'All' rather than as an Empty Column.

If you like this post, give it a Thumbs up. If it answered your question, Mark it as a Solution to enable other users find it.

WarrenBelz · ‎06-16-2021

@JR-BejeweledOne ,

I was just trying to give a fellow Super User some syntax help. I personally would never attempt something that complex in one statement (I build a series of collections and/or nested With() statements to progressively get the structure). I would certainly always have the last statement filtering a collection for both performance and Delegation reasons.

One thing in there I did notice is the in filter in the With() statement - this is not delegable and will affect the potential number of records returned for processing in the remainder of the structure.

I would have thought however that this part

&&
(
   IsBlank(wSearch) || 
   (
      wSearch in 'Project Name' || 
      wSearch in Developer ||
      StartsWith(
         'Course Type', 
         wSearch
      ) || 
      StartsWith(
         'Project Type', 
         wSearch
      )
   )
)

would achieve the desired result of allowing for a blank search box then if not the case applying the two filters.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.