Filtering a Gallery by Person from two separate Sharepoint Lists

[ShawnSchinkel]

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]

HI @ShawnSchinkel ,
Sorry I am a bit lost here and cannot see your data - this code

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

will do a many-to many filter where any PersonName.DisplayName in user_details_list matches any DisplayName in EmployeeName. You can then display all the relevant fields in your Gallery. If you have any fields in user_details_list that are blank and you are displaying these fields in the Gallery, then naturally you will have a blank value in that Label. If you want to exclude these with the Filter, then you need to filter for that field not being blank. But I have to ask here, what part does the 'Shift Rosters 2.0' play here as it is not mentioned in the code.

[ShawnSchinkel]

Hi @WarrenBelz ,

I'll do my best to explain my scenario. user_details_list is a list of all the employees in our organization. We have 116 people and they are on 4 different platoons. 'Shift Rosters 2.0' is a list that I am using to record and schedule shifts and each record has one employee with the relevant shift information such as station location, date of the shift, role for the shift etc. In user_details_list the number of records is fixed and each record represents an employee with all the associated information.

The layout I currently have is the gallery on the left side draws information from 'Shift Rosters 2.0', filtered by shift date, and is added to/modified by the form to the right of it. The gallery directly above the form, called Waiting for Assignment, draws from the user_details_list of all the employees.

The ultimate goal is that the Assignment gallery will display everyone who is a member of the platoon that is currently working on the day being scheduled, and will only show people whose names are not in the 'Shift Roster 2.0' gallery, so the person doing the scheduling can confirm that everyone has been assigned. 

Right now we're working on the positive of that which is to show people whose names are included in 'Shift Rosters 2.0' to get the troubleshooting done and then I'll work on the negative.

Here is all my code which includes the filters for Date and Platoon.

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

The result of the above code is below.

It appears to me that the output of the code is creating a table with blank records in it, and when compared to my 'user_details_list', the blanks line up perfectly with the people who do not meet the criteria of the filters. 

To elaborate on that, in this section of code,

Filter(
user_details_list,
Platoon.Value = FilterPlatoon
) As aComp,

the platoon is filtered down to only the people who's platoon is scheduled to be working that day. When I do the same filter in Sharepoint, the records line up perfectly but the table that is generated in the second section of the code still includes people who are not present in the 'Shift Rosters 2.0' gallery.

I'm lost haha

Thanks for taking the time to help me troubleshoot this!

Shawn

[WarrenBelz · Accepted solution]

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

[ShawnSchinkel]

Good morning @WarrenBelz !

First off, I really appreciate the help as well as the learning opportunity from your coding experience. The nested With statements was something I never thought of but really simplifies the coding. There are definitely places in a couple other apps I have created where that will come in handy!

I entered your code and was still getting the blank records (very weird!). With a bit of tinkering I was able to successfully filter out the blank records from the Ungroup code by adding a filter and !IsBlank() around it so that part seems to be solved and working. 

With(
    {
        wDate: Date(
            Year(varSelectedDay),
            Month(varSelectedDay),
            Day(varSelectedDay)
        )
    },
    With(
        {
            FilterPlatoon: LookUp(
                BLoop,
                ShiftDate = wDate
            ).Platoon,
            wDate1: wDate + 1
        },
        Filter(
            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"
            ),
            !IsBlank(EmployeeName.DisplayName)
        )
    )
)

Next, and hopefully the last question for a bit, are we able to reverse that code so that the Waiting for Assignment gallery only shows items from 'user_details_list', filtered by shift date and platoon, that have not been assigned into the Shift Rosters gallery on the left? 

Thanks!

Shawn

[WarrenBelz]

@ShawnSchinkel ,

So essentially, EmployeeName was the blank field I referred to - you could also include that in the inner filter as I suggested, but it is probably better around the outside as you have it as Not() ! is not Delegable. To answer your question, you can, but produces another problem of duplicates as each iteration will show all records not in the one being queried.

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"
      )
   )
)

You can head down the Distinct() path, but this is also a bit more complex and will only return one field - whatever you are testing Distinct on.

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]

Good morning @WarrenBelz !

I added your code in, and with Distinct included it did indeed filter down to show only the 7 of 29 people on the platoon who have been assigned into the shift. 

When I add the ! to attempt the negative of that, instead of showing the 22 of 29 people NOT assigned to the shift, it only shows all 29 on the shift. It has the identical result of me // out the line. It's almost as if the line 

!(PersonName.DisplayName in aComp.EmployeeName.DisplayName)

 is being ignored? Or maybe there's more at play here that I'm not understanding with the Distinct added in.

This is the current version of the negative that isn't working.

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

[WarrenBelz]

@ShawnSchinkel ,

Actually that will not work as it will put back in all the records - sorry I do not have an answer to that one. Has your original question been resolved ?

[ShawnSchinkel]

@WarrenBelz thanks for all your help! I am going to keep tinkering, I think I have a clunky idea that might work. If I come up with something I'll post it on here!