Filter Gallery, show record if some text is in column

[susannarae]

Hi all, I'm working on a gallery filter that displays kids in our preschool based on which programs they are enrolled in. The problem is that some kids are enrolled in more than one program, for example summer school and GSRP. The programs are captured in a text box with a comma between each program (ie: GSRP, Summer Program).

I would like my gallery to filter the children displayed based on a combobox selection, and show all students for the selected program. Currently, however, it only shows students who only have that specific program selected (such as just GSRP or just Summer Program, but not both). I'm not sure what I'm doing wrong. This is the formula I currently have: 

Filter('21-22 S2-4 CLC Enrollment', If(!IsBlank(Locate.SelectedItems.Result), CLCLocation in Locate.SelectedItems, true) && If(!IsBlank(Programs.SelectedItems.ProgName),All_x002d_Program_x0020_Type in Programs.SelectedItems.ProgName, true) && If(!IsBlank(Rooms.SelectedItems.RoomNumbFinal), All_x002d_S4_x003a__x0020_Room_x in Rooms.SelectedItems.RoomNumbFinal,true),IsBlank(All_x002d_S4_x003a__x0020_Drop_x),Archived<>true)

As you can see, there are a LOT of filters on this gallery, and most of them already work well. But when a child has more than one program in their Program item, they just don't show. Any advice?

[WarrenBelz]

Hi @susannarae ,

Firstly, please try this structure

Filter(
   '21-22 S2-4 CLC Enrollment', 
   (
      IsBlank(Locate.Selected.Result) || 
      CLCLocation in Locate.SelectedItems
   ) && 
   (
      IsBlank(Programs.Selected.ProgName) ||
      All_x002d_Program_x0020_Type in Programs.Selected.ProgName
   ) && 
  (
     IsBlank(Rooms.Selected.RoomNumbFinal) || 
     All_x002d_S4_x003a__x0020_Room_x in Rooms.Selected.RoomNumbFinal
   ) &&
   IsBlank(All_x002d_S4_x003a__x0020_Drop_x) &&
   !Archived
)

Also are any of these multi-choice columns (do you need the in filter?)

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.

[susannarae]

I will give this a try today and see if it works!

All of the columns in the SP List that I'm filtering by here have text values, although one I'll be working on in the future DOES have a choice column.

[susannarae]

Unfortunately this didn't work. It is only showing kids with one program type entered, not those with more than one.

[Drrickryp]

@susannarae 

Your database design is flawed.  You have a many to many relationship in a single table.  Since one child can be enrolled in many programs and each program can have many children, best practices would be to have 3 lists with the junction list (enrollments) where details of the child's programs can be described. For more information regarding how to design the tables and how to implement them in PowerApps, see my blog post here

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Principles-and-Pow...  and here:

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Design-fundamental... 

[susannarae]

I hear what you're saying but this is just an enrollment app. It's only supposed to track their progress through the enrollment process, which is basically the same for all of our programs. It's just for this one gallery that I need the filter by program type. Do you have any workaround suggestions?

[susannarae · Accepted solution]

After tooling around with this app for a while, I finally did find some semblance of a solution. There are restrictions, but they're restrictions I can work around. Here's the formula:

Filter('21-22 S2-4 CLC Enrollment', 
'S1 CLC Location'=Locate.Selected.Result &&
!Archived &&
IsBlank('All-S4: Drop Date (if dropped)')&&
If(!IsBlank(Programs.SelectedItems.ProgName), 'All-Program Type' in ProgramLabel.Text Or StartsWith('All-Program Type',ProgramLabel.Text) Or EndsWith('All-Program Type',ProgramLabel.Text),true)&&
If(!IsBlank(RoomLabel.Text), 'All-S4: Room #' in RoomLabel.Text,true))

A few things to keep in mind:

1. You can only choose one program from the ComboBox, as children can have more than one program assigned and it will be invalid otherwise.

3. Each child can only have a max of 2 programs selected, otherwise the 'StartsWith' and 'EndsWith' won't pick up.

2. You can choose more than one room number for the filter, as each child will only have 1 room number assigned.

I also made two labels, 1 called "ProgramLabel" and one called "RoomLabel." I had each label populate from the corresponding ComboBox. Did I need this step? Maybe not, but the dang app was giving me fits until I tossed that in there and it seems to be working smoothly now.

For the programs, since a child can have up to 2 selected, the 'in' function checks for children with just one program, and if the program matches plugs them in; the 'StartsWith' checks all the first choice for children with 2 programs, and if their first choice matches plugs them in; and finally 'EndsWith' checks all the second choices for children with 2 programs, and if that second choice matches plugs them in.

It's extremely roundabout, but ultimately it meets my needs and we can work easily within its limitation of only having 2 programs selected per child. Hopefully this is helpful for someone else!

[WarrenBelz]

@susannarae ,

I am glad you got it sorted, but I don't think either @Drrickryp or myself could have worked out that logic from what you posted. It it worth remembering that the more detailed the logic you post, the easier it is to respond accurately to.

Taking your code, please consider the below for consistency of and/or and bracketing logic - you will also find it easier to read the logic. Also you do not need StartsWith/EndsWith with if you are using the in filter (if it starts or ends with, it is in the value)

Filter(
   '21-22 S2-4 CLC Enrollment', 
   'S1 CLC Location' = Locate.Selected.Result &&
   !Archived &&
   IsBlank('All-S4: Drop Date (if dropped)') &&
   (
      IsBlank(Programs.Selected.ProgName) || 
      ProgramLabel.Text in 'All-Program Type'
   )
   ) &&
   (
      IsBlank(RoomLabel.Text) ||
      RoomLabel.Text in 'All-S4: Room #'
   )
)

Also note SelectedItems is deprecated - use Selected instead.

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.