Filter a gallery of one SharePoint list based on an ID count of a second list

[CorbnDallas]

Scenario: I am trying to filter a gallery called Shifts (a list stored in SharePoint), based on how many times a 'Shift ID' is present in Requests (a second SharePoint list). My Requests list has a lookup column that stores corresponding Shift IDs.

End result: I'm trying to only show my users all Shifts that have fewer than 10 Requests.

I know I can use CountIf to determine if a particular Shift ID is present in the Results list more than X times. My initial attempt was to filter the gallery of Shifts with the following:

Filter(Shifts, (CountIf(Requests, 'Shift ID'.Id = ID) < 10))

Unfortunately, that scenario failed, as have attempts around building a collection and trying to store those unique IDs there.

Here's an example of how my lists are setup and which IDs I would want filtered out of the Shifts gallery. In this example, I am only wanting to show Shifts that have not been requested more than 2 times, so Shift ID 2 should be filtered out from the Shifts gallery.

Thanks for any advice on this!

Requests

ID	Employee ID (lookup)	Shift ID (lookup)	Status
1	1	2	Open
2	4	1	Open
3	2	2	Open
4	6	3	Open

Shifts

ID	Date	Time	Location
1	4/22/2020	3a-4p	Central
2	4/22/2020	12a-6p	Main
3	4/23/2020	6a-2p	South

[WarrenBelz]

Hi @CorbnDallas ,

For the solution below, I have to suggest you change something. You cannot Sort or Group by a Lookup field and Filtering is also problematic. If you can change this to a text field, or have a "shadow" text field, then I am suggesting a gallery using the GroupBy function to get the user requests in one line, then drilling into the table that results from the rest of the data to count the number of Requests present, then filtering this for results less than ten. So the gallery Items would be something like this

Filter(
    GroupBy(
        Shifts,
        "ShiftID",            //New Column suggested
        "RemainData"          //You can call this whatever you want
    ),
    CountRows(RemainData.Requests) < 10
)

If you cannot do this, I think your desired result will be very challenging if not unachievable.

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.

[CorbnDallas]

Thanks @WarrenBelz,

I will try to implement your suggestion today. Can you confirm for me, that the new "shadow field" would reside in the Shifts list? So adding a new Shift ID (text value) column to match the auto-generated ID field in the shifts table. Correct?

I had considered adding a new column to my Shifts list called NumRequests.

That field could increment by one (via Flow) each time a new request is made for a particular shift. Then it would just be a matter of filtering the shifts gallery based on the number in that column.

My only hesitation with that idea was that I wanted to avoid scenarios where my users might be trying to update the same SharePoint list item at the same time.

[WarrenBelz · Accepted solution]

Hi @CorbnDallas ,

Firstly I forgot to mention how to display the field in the gallery - a label with the Text

CountRows(ThisItem.RemainData.Requests)

Now to your questions - I will deal with the SharePoint user question first. If you are going to let your users loose in a Data Sheet Quick Edit view, then Choice /Lookup columns are probably needed, but anything else can be done in Power Apps, either integrated or stand alone. I do not use SharePoint Lookup columns at all and very few Choice columns. If you keep your base data to Text and Numeric fields (you will need some Date as well, but they are not Delegable), almost all of your Power Apps data management issues go away. You also do not generally need a Flow to do make data updates if is only updated in Power Apps.

You can easily have an OnChange event on your Lookup Field and reset the shadow field (it will need to be on the form but can be hidden) with the Default

YourLookupControlName.Selected.Value

You could also write a number field to the incremental value by looking up the existing number in a Variable, adding 1 to it and resetting the control with a Default of the Variable.

GroupBy is still a good and simple tool for summarizing data.

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.

[WarrenBelz]

Hi @CorbnDallas ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

[CorbnDallas]

Hello @WarrenBelz ,

Yes I did get the result I was looking for. Thanks for your help!