cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cattre
Level: Powered On

Filtering gallery using separate SharePoint list

Hi all,

 

I've got another headache working through this one and need a bit of help - sometimes just writing out the problem helps me see things differently! Essentially I've got a gallery which is pulling from a SharePoint list. I am trying to filter the items based on a few variables and am using a second reference list in SharePoint to establish what the current user should be able to see.

 

The first list "Items" has (amongst many other columns):

  • Item ref (unique)
  • Trainee email (text field)
  • Choices (choice field, multi-select)

 

The second list "Managers" contains the following

  • Manager (person field, NOT unique)
  • Trainees (person field, multi-select)
  • Choice (choice field, single-select)

 

So, the current user should only see items for trainees listed against them where Choice is contained within Choices (in Items) for that item.

 

I have managed to get the filter working for a simple Manager entry:

 

 

Filter
(
    'Items',
    (
            'Trainee email' in manager.'Trainees'.Email),
            manager.Choice.Value in Choices.Value
    )
)

 

 

The "manager" variable is created by:

 

 

Set(manager,LookUp(Users,Office365Users.MyProfile().Mail = 'Manager'.Email));

 


My problem is that the managers aren't unique in the second list - they have different trainees and variable combinations so need to have multiple entries. I tried creating a filtered Manager table (to replace the manager variable), then thought about using a second LookUp function within the Filter statement to match rows based on Trainee. However, the syntax wasn't working and I also can't guarantee that a manager-trainee combination will be unique.

 

It could be that the Manager and Trainee share additional Choices and therefore need multiple rows. Of course I could change the Choice field in the Manager list to be multi-select, but have had issues comparing two multi-select fields before.

 

I may be over-complicating the solution (wouldn't be the first time!) so happy to be told if this can't be done. And no, writing this out at length hasn't helped to lift the fog!

1 REPLY 1
cattre
Level: Powered On

Re: Filtering gallery using separate SharePoint list

As an update, I seem to have made a bit of progress but it's hard to tell whether or not it's in the right direction! I came across ForAll and managed to get a data table showing each row in Items where my criteria in Managers was met:

ClearCollect(
    collection,
    ForAll(
        Items,
        LookUp(
            Managers,
            Office365Users.MyProfile().Mail = Manager.Email && 'Trainee email' in 'Trainees'.Email,
            'Item ref'
        )
    )
)

 

The data table returned the row ID (Item ref) for each row that matched, and "false" for all others. So then I thought that I could use this to filter the gallery, but unfortunately that's where I'm getting stuck. The following code doesn't throw any errors but doesn't work either:

 

Filter(
    Items,
    IsNumeric(
        ForAll(
            Items,
            LookUp(
                Managers,
                Office365Users.MyProfile().Mail = Manager.Email && 'Trainee email' in Trainees.Email,
                'Item ref'
            )
        )
    )
)

 

So now it feels like there could be a solution but I'm just struggling (a lot) to find it! Any ideas out there?

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,612)