Solved: Dropdown Filter Not Updating Gallery

lottostagira · ‎02-23-2023

Still pretty new to this. I am trying to create a simple inventory application for my IT department. I created a Sharepoint List with details about the many devices in our inventory, then used this datasource to create a canvas app.

I have an "Inventory" screen with a gallery that displays all of the devices from my SP List along with other details like serial number, model, status, etc. On this inventory screen I have added a search bar that functions as expected.

However, I want to add three dropdown filters to this screen as well to filter Equipment Type, Status, and Assigned To. So far, I have this code:

SortByColumns(
    Filter(
        [@Inventory],
        StartsWith('Asset Tag', TextSearchBox1.Text) ||
        StartsWith(Status, TextSearchBox1.Text) ||
        StartsWith('Assigned To', TextSearchBox1.Text) ||
        StartsWith(Contact, TextSearchBox1.Text) ||
        StartsWith(Title, TextSearchBox1.Text) ||
        StartsWith('Serial Number', TextSearchBox1.Text) &&
        (Dropdown1.Selected.Result = "" || Dropdown1.Selected.Result = 'Equipment Type') 
    ),
    "Title", If(SortDescending1, Descending, Ascending)
)

I don't receive any errors, however when I select an option from one of the dropdown menus, nothing happens and my gallery remains the same. Any ideas as to why my dropdown aren't filtering the data from my gallery?

Please let me know if additional details are needed, any assistance is very much appreciated.

WarrenBelz · ‎03-10-2023

@lottostagira ,

As I noted, making the Default of the Search Box "" should do that (it does on every StartsWith filter I have ever used), however I will again repeat my suspicion here that the gallery is simply not resolving the query due to its complexity. You can confirm this (or not) by making a collection with the same filter when the controls contain the desired values. You could also go further down the "coding/bracketing rabbit hole" as below, but that is about the last of my ideas here

SortByColumns(
   Filter(
      'Inventory List (New)',
      (
         Len(TextSearchBox1.Text) = 0 ||
         (
            StartsWith(
               'W/Charger',
               TextSearchBox1.Text
            ) ||
            StartsWith(
               'Equipment Type',
               TextSearchBox1.Text
            ) ||
            StartsWith(
               'Updated By',
               TextSearchBox1.Text
            ) ||
            StartsWith(
               Model,
               TextSearchBox1.Text
            ) ||
            StartsWith(
               'Asset Tag',
               TextSearchBox1.Text
            ) ||
            StartsWith(
               Title,
               TextSearchBox1.Text
            ) ||
            StartsWith(
               'Serial Number',
               TextSearchBox1.Text
            ) ||
            StartsWith(
               Contact,
               TextSearchBox1.Text
            )
         )		   
      ) || 
      (
         Len(EquipmentTypeDropdown.Selected.Result) = 0 || 
         'Equipment Type' = EquipmentTypeDropdown.Selected.Result
      ) &&
      (
         Len(StatusDropdown.Selected.Result) = 0 || 
         'Status' = StatusDropdown.Selected.Result
      ) &&
      (
         Len(UpdatedByDropdown.Selected.Result) = 0 || 
         'Updated By' = UpdatedByDropdown.Selected.Result
      )
   ),
   "Title",
   If(
      SortDescending1,
      SortOrder.Descending,
      SortOrder.Ascending
   )
)

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.

MVP (Business Applications) Visit my blog Practical Power Apps

View solution in original post

lottostagira · ‎03-12-2023

Warren, I truly do appreciate your time and assistance with this.

After toying with the code you provided, I found that changing the OR symbol to the AND symbol at the beginning of the dropdown filtering code-block solved my issue, as shown in the snippet below.

StartsWith(
               Contact,
               TextSearchBox1.Text
            )
         )		   
      ) && //I changed OR (||) to AND (&&)
      (
         Len(EquipmentTypeDropdown.Selected.Result) = 0 || 
         'Equipment Type' = EquipmentTypeDropdown.Selected.Result
      )

Again, thank you so much for helping me figure this out.

View solution in original post

WarrenBelz · ‎02-23-2023

Hi @lottostagira ,

Two issues - firstly try this while also setting the Default of TextSearchBox1 to "" (empty string)

SortByColumns(
   Filter(
      [@Inventory],
      StartsWith('Asset Tag', TextSearchBox1.Text) ||
      StartsWith(Status, TextSearchBox1.Text) ||
      StartsWith('Assigned To', TextSearchBox1.Text) ||
      StartsWith(Contact, TextSearchBox1.Text) ||
      StartsWith(Title, TextSearchBox1.Text) ||
      StartsWith('Serial Number', TextSearchBox1.Text) &&
      (
         Len(Dropdown1.Selected.Result) = 0 || 
         Dropdown1.Selected.Result = 'Equipment Type'
      ) 
   ),
   "Title", 
   If(
      SortDescending1, 
      Descending, 
      Ascending
   )
)

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Drrickryp · ‎02-23-2023

@lottostagira

With({_items:Filter([@Inventory], Dropdown1.Selected.Result = Blank() || 'Equipment Type' = Dropdown1.Selected.Result)},

SortByColumns(
Filter(_items,
StartsWith('Asset Tag', TextSearchBox1.Text) ||
StartsWith(Status, TextSearchBox1.Text) ||
StartsWith('Assigned To', TextSearchBox1.Text) ||
StartsWith(Contact, TextSearchBox1.Text) ||
StartsWith(Title, TextSearchBox1.Text) ||
'Serial Number'= Value(TextSearchBox1.Text)

),
"Title", If(SortDescending1, Descending, Ascending)

)

lottostagira · ‎02-23-2023

@WarrenBelz My apologies, I forgot to include the code for the other two dropdown menus. I took your suggested and changed my code to the following:

SortByColumns(
   Filter(
      [@Inventory],
      StartsWith('Asset Tag', TextSearchBox1.Text) ||
      StartsWith(Status, TextSearchBox1.Text) ||
      StartsWith('Assigned To', TextSearchBox1.Text) ||
      StartsWith(Contact, TextSearchBox1.Text) ||
      StartsWith(Title, TextSearchBox1.Text) ||
      StartsWith('Serial Number', TextSearchBox1.Text) &&
      (
         Len(Dropdown1.Selected.Result) = 0 || 
         Dropdown1.Selected.Result = 'Equipment Type'
      ) &&
      (
         Len(Dropdown2.Selected.Result) = 0 || 
         Dropdown2.Selected.Result = Status
      ) &&
      (
         Len(Dropdown3.Selected.Result) = 0 || 
         Dropdown3.Selected.Result = 'Assigned To'
      )
   ),
   "Title", 
   If(
      SortDescending1, 
      Descending, 
      Ascending
   )
)

No visible error messages, however the gallery still isn't being filtered when i make a selection from the dropdown

lottostagira · ‎02-23-2023

My apologies, I forgot to include the code for the other two dropdown menus:

SortByColumns(
    Filter(
        [@Inventory],
        StartsWith('Asset Tag', TextSearchBox1.Text) ||
        StartsWith(Status, TextSearchBox1.Text) ||
        StartsWith('Assigned To', TextSearchBox1.Text) ||
        StartsWith(Contact, TextSearchBox1.Text) ||
        StartsWith(Title, TextSearchBox1.Text) ||
        StartsWith('Serial Number', TextSearchBox1.Text) &&
        (Dropdown1.Selected.Value = "" || Dropdown1.Selected.Value = 'Equipment Type') &&
        (Dropdown2.Selected.Value = "" || Dropdown2.Selected.Value = Status) &&
        (Dropdown3.Selected.Value = "" || Dropdown3.Selected.Value = 'Assigned To')
    ),
    "Title", If(SortDescending1, Descending, Ascending)
)

I tried your suggestion and received several "Invalid use of '.'" and "Name invalid. 'ThisItem' isn't recognized

WarrenBelz · ‎02-24-2023

Hi @lottostagira ,

Assuming you have set the Default of TextSearchBox1 to "" (empty string) as I suggested and all those Combo Boxes are single selection, this code should be correct. Note that "" does not cover Blank whereas Len() addresses Blank(), Empty() and "".

SortByColumns(
   Filter(
      [@Inventory],
      (
         StartsWith(
            'Asset Tag', 
             TextSearchBox1.Text
         ) ||
         StartsWith(
            Status, 
            TextSearchBox1.Text
         ) ||
         StartsWith(
            'Assigned To', 
            TextSearchBox1.Text
         ) ||
         StartsWith(
            Contact, 
            TextSearchBox1.Text
         ) ||
         StartsWith(
            Title, 
            TextSearchBox1.Text
         ) ||
         StartsWith(
           'Serial Number', 
           TextSearchBox1.Text
         )
      )
   ) &&
   (
      (
         Len(Dropdown1.Selected.Value) = 0 || 
         'Equipment Type' = Dropdown1.Selected.Value
      ) &&
      (
         Len(Dropdown2.Selected.Value) = 0 || 
         Status = Dropdown2.Selected.Value
      ) &&
      (
         Len(Dropdown3.Selected.Value) = 0 || 
         'Assigned To' = Dropdown3.Selected.Value
      )
   ),
   "Title", 
   If(
      SortDescending1, 
      Descending, 
      Ascending
   )
)

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.

MVP (Business Applications) Visit my blog Practical Power Apps

lottostagira · ‎03-07-2023

Thanks for your assistance on this. I tried your suggestions with no luck. I scrapped the project and started over from scratch, same concept. The Dropdown menus are now named 'Equipment Type', 'Status', and 'Updated By'. I was able to come up with this code for the search bar and dropdown menus.

SortByColumns(
    Filter(
        'Inventory List (New)',
StartsWith(
    'W/Charger',
    TextSearchBox1.Text
) 
||
StartsWith(
    'Equipment Type',
    TextSearchBox1.Text
)
  ||
StartsWith(
    'Updated By',
    TextSearchBox1.Text
)
  ||
StartsWith(
    Model,
    TextSearchBox1.Text
) ||
StartsWith(
    'Asset Tag',
    TextSearchBox1.Text
) ||
StartsWith(
    Title,
    TextSearchBox1.Text
) ||
StartsWith(
    'Serial Number',
    TextSearchBox1.Text
) ||
StartsWith(
    Contact,
    TextSearchBox1.Text
) || 
(
    EquipmentTypeDropdown.Selected.Result = "" || 
    StartsWith(
        'Equipment Type',
        EquipmentTypeDropdown.Selected.Result
    )
) &&
(
    StatusDropdown.Selected.Result = "" || 
    StartsWith(
        'Status',
        StatusDropdown.Selected.Result
    )
) &&
(
    UpdatedByDropdown.Selected.Result = "" || 
    StartsWith(
        'Updated By',
        UpdatedByDropdown.Selected.Result
    )
)

        
    ),
    "Title",
    If(
        SortDescending1,
        SortOrder.Descending,
        SortOrder.Ascending
    )
)

Still not getting any errors. But same result - when I select an option from the dropdowns, no updates occur to the gallery. It remains unchanged, unfiltered.

You mentioned something about single selection combo boxes. The options for the Equipment Type drop down (so far) are Dell Laptop and MacBook. The choices for Status dropdown should be In and Out. The choices for Updated should be a list of techs from our department.

I captured the logs from the activity monitor when performing the dropdown filtering and receive the result "The number passed in is a blank value."

Please let me know if you need any more information for context. I appreciate your assistance.

WarrenBelz · ‎03-07-2023

Hi @lottostagira ,

You are using "" again and this is not a valid test for Blank() as I mentioned, conversely you need to set the Default of your Text Search Box to "" as StartsWith then will work when the box is empty. Also why are you using StartsWith for the drop-downs when the result will be a match ?

SortByColumns(
   Filter(
      'Inventory List (New)',
      StartsWith(
         'W/Charger',
         TextSearchBox1.Text
      ) ||
      StartsWith(
         'Equipment Type',
         TextSearchBox1.Text
      ) ||
      StartsWith(
         'Updated By',
         TextSearchBox1.Text
      ) ||
      StartsWith(
         Model,
         TextSearchBox1.Text
      ) ||
      StartsWith(
         'Asset Tag',
         TextSearchBox1.Text
      ) ||
      StartsWith(
         Title,
         TextSearchBox1.Text
      ) ||
      StartsWith(
         'Serial Number',
         TextSearchBox1.Text
      ) ||
      StartsWith(
         Contact,
         TextSearchBox1.Text
      ) || 
      (
         Len(EquipmentTypeDropdown.Selected.Result) = 0 || 
         'Equipment Type' = EquipmentTypeDropdown.Selected.Result
      ) &&
      (
         Len(StatusDropdown.Selected.Result) = 0 || 
         'Status' = StatusDropdown.Selected.Result
      ) &&
      (
         Len(UpdatedByDropdown.Selected.Result) = 0 || 
         'Updated By' = UpdatedByDropdown.Selected.Result
      )
   ),
   "Title",
   If(
      SortDescending1,
      SortOrder.Descending,
      SortOrder.Ascending
   )
)

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.

MVP (Business Applications) Visit my blog Practical Power Apps

lottostagira · ‎03-08-2023

I appreciate your feedback. Still learning, but now I understand where I was going wrong with "" and StartsWith. I also updated the code for the dropdowns as you suggested. The Default field for the Text Search Box has been, and is still "" as you mentioned. However, as shown in this image, Default is set to "" at the top in the formula bar, but not on the far right in Properties. Is this correct?

After applying your suggestions, I notice that the dropdown filtering functions properly after typing "" into the Text Search Box as shown below. Is this normal?

.

My apologies if I'm missing something that you have already mentioned. Again, I appreciate your assistance thus far.

WarrenBelz · ‎03-08-2023

HI @lottostagira ,

Two separate but related issues regarding empty strings -

Making the Default of your Text Box "" will save you typing it in and your StartsWith will then work when there is not input in the box. What you have is correct.
Conversely when you are testing your drop-downs for no input you cannot use "" as this (empty string) is a different value to Blank() or Empty() as I posted Len(ddName.Selected.Result) = 0 covers Blank(), Empty() and "".

I am not clear on what you are saying now (what is not working), but I have another suspicion here - when the Items of a Gallery get too complex, there are times it simply does not resolve properly. Try making a collection with the code and see if you get the expected result.