Dropdown Filter Not Updating Gallery

[lottostagira]

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]

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]

@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]

@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]

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]

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]

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]

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]

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]

HI @lottostagira ,

Two separate but related issues regarding empty strings -

1. 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.
2. 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.