🪄🐈‍⬛ TnT #4 🧨

Roughly a year ago, I had a project that I used SharePoint Lists to map the hierarchical organization structure of approximately 6000 employees/positions. I wanted to use combo boxes within Power Apps to quickly filter down to specific branches within the organization. Unfortunately, the multi-select choice fields in the SharePoint List were ultimately what lead to that portion of the project stalling indefinitely.

Not one of my proudest moments. 😕

However, earlier this week, I found a help request post over on the Power Apps Community | Build Power Apps Forum that needed a similar solution. Current me, now has a year’s-worth more experience finding creative ways to problem-solve in Power Apps with SharePoint. Between the new knowledge and the time away from the problem, I developed the solution resulting in the functionality displayed below. (Marked as the accepted Solution by the post Author😍)

Request Detail

• Data source is a SharePoint List
  • Two columns of note:
    • BusinessProcess: Choice field with multi-select turned on
    • BusinessRole: Choice field with multi-select turned on
• Power App contains the following controls with relevant settings:
  • BoxProcess (Combobox)
    • Items = Choices([@ExampleListName].BusinessProcess)
    • SelectMultiple = true
  • BoxRole (Combobox)
    • Items = Choices([@ExampleListName].BusinessRole)
    • SelectMultiple = true
  • Gallery1 (Blank Vertical Gallery)
    • Items = {see code box below}
    • Multiple Label controls to display information
• Filter Functionality
  • All items selected for a combo box, must also be selected for the list item, at a minimum.
  • If no items are selected for one or both combo boxes, then the list items should not be filtered by that field.

Solution

Filter( 'ExampleListName'
  , (
	CountIf( BoxProcess.SelectedItems As i
	    , i.Value in BusinessProcess.Value
	)
	= CountRows( BoxProcess.SelectedItems )
    ) 
    * 
    (
	CountIf( BoxRole.SelectedItems As i
	    , i.Value in BusinessRole.Value
	)
	= CountRows( BoxRole.SelectedItems )
    )
    = 1
)

I actually arrived at this solution, because of some work that I’ve been doing in Excel for a Top Secret project. (🤫 shhh… more on that soon)

In Excel I’ve been able to filter rows of data by multiplying or adding the output of several functions that result in a Boolean value.

• Multiply the values to apply AND logic to the array
• Add the values to apply OR logic to the array

The above solution is:

1. Counting how many of the selected items in the combo box are in the associated field for each list item.
2. Counting the total selected items in the combo box.
3. Determining if both of those values are equal.
4. Multiplying together each Boolean output for each field.

If the final value equals 1, then that list item is returned, otherwise it gets filtered out. Simple as that! 😁

That is all for now. Let me know if you enjoyed this bonus post for the week, and enjoy your weekend.