Solved: Re: Filtering a Gallery by Person from two separat...

ShawnSchinkel · ‎02-15-2023

Good morning!

I am trying to do some filtering and I could use some help.

Scenario: I have a gallery that contains People from a sharepoint Person column 'user_details_list'.PersonName. I need to have the data only show up in the gallery if that Person is also present in the second list 'Shift Rosters'.EmployeeName.

The part I am unable to figure out is how to reference and compare a Person record from 'user_details_list'.PersonName.DisplayName to 'Shift Rosters'.EmployeeName.DisplayName. The items both being records is what's giving me grief.

I would share my code but none of what I am doing is even remotely close to working so it won't be helpful. What I'm really after is a basic example that would point me in the right direction.

Any ideas?

Thanks in advance,

Shawn

WarrenBelz · ‎02-18-2023

Hi @ShawnSchinkel ,

I can save you a bit of code here, but there is no reason this should create blank records - I use the structure regularly and have posted it dozens of times on accepted solutions, however the reason may be elsewhere. I have experienced instances where a Gallery simply will not properly resolve a highly complex multi-criteria formula properly. To test this, make a collection with the code and then display that as the Items of the Gallery.

With(
   {
      wDate:
      Date(
         Year(varSelectedDay),
         Month(varSelectedDay),
         Day(varSelectedDay)
      ),
   },
   With(
      {
         FilterPlatoon: 
         LookUp(
            BLoop,
            ShiftDate = wDate
         ).Platoon,
         wDate1: wDate + 1
      },
      Ungroup(
         ForAll(
            Filter(
               user_details_list,
               Platoon.Value = FilterPlatoon
            ) As aComp,
            Filter(
               'Shift Rosters 2.0',
               OnDuty > wDate &&
               OnDuty < wDate1 &&
               EmployeeName.DisplayName in aComp.PersonName.DisplayName
            )
         ),
         "Value"
      )
   )
)

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

WarrenBelz · ‎02-15-2023

HI @ShawnSchinkel ,

You have a many-to-many relationship there - I cannot test this presently, but try

Ungroup(
   ForAll(
      user_details_list As aComp,
      Filter(
         'Shift Rosters',
         aComp.PersonName.DisplayName in EmployeeName.DisplayName
      )
   ),
   "Value"
)

NOTE: This is not Delegable

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

ShawnSchinkel · ‎02-15-2023

Thanks Warren!

I'll give that a try and let you know how it goes!

Shawn

ShawnSchinkel · ‎02-16-2023

Good morning @WarrenBelz,

I tried out the code last night and it's popping up a few errors.

The underlined errors are saying Ungroup and ForAll have some invalid arguments and that aComp isn't recognized. "Value" at the bottom is stating that the specified column doesn't exist.

I'm not sure about the As portion as I've never used that before but could the Ungroup need a Group to go with it somewhere? Or am I misunderstanding how that function works?

Shawn

WarrenBelz · ‎02-16-2023

@ShawnSchinkel ,

Small correction done As

ShawnSchinkel · ‎02-16-2023

@WarrenBelz

That fixed the formula!

Next question if you have a minute. The results are appearing as in the screenshot below.

with

Ungroup(
ForAll(
user_details_list As aComp,
Filter(
'Shift Rosters 2.0',
aComp.PersonName.DisplayName in EmployeeName.DisplayName
)
),
"Value"
)

Is there a way to remove the empty spaces in between the items?

Shawn

WarrenBelz · ‎02-16-2023

Hi @ShawnSchinkel ,

What field are you displaying - you should be able to do this

Ungroup(
   ForAll(
      user_details_list As aComp,
      Filter(
         'Shift Rosters 2.0',
         aComp.PersonName.DisplayName in EmployeeName.DisplayName &&
         !IsBlank(YourBlankFieldName)
      )
   ),
   "Value"
)

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

ShawnSchinkel · ‎02-17-2023

Hey @WarrenBelz,

This is going to sound like a dumb question but I'm not sure what you mean by YourBlankFieldName. Is that a reference to the field in the gallery like Label1_11 or a record reference?

WarrenBelz · ‎02-17-2023

Hi @ShawnSchinkel ,

You asked how to get rid of the blanks - there this would be the field name you are displaying in your image that is blank - you need to filter it out when it is blank.

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

ShawnSchinkel · ‎02-18-2023

Hi @WarrenBelz

I was trying to figure out what you meant as none of the available field names would solve the problem when entered and then I realized that I didn't explain myself very clearly. Each of those blanks that are showing up in the gallery are actually in my 'user_details_list'. It appears that the code is correctly running the filter and only showing the names that are also in the 'Shift Rosters 2.0' list. However, instead of shortening the gallery items to say 15 filtered items with no spaces after the filtering is completed, the gallery is showing the 116 items from the 'user_details_list', but the only ones that are visible are the filtered items and the rest are blank.

Any thoughts?

Thanks!

Shawn