cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cattre
Resolver I
Resolver I

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 ACCEPTED SOLUTION

Accepted Solutions
cattre
Resolver I
Resolver I

I've finally cracked it and thought I'd update this post in case anyone else has a similar issue in future.

 

I started thinking that, if I couldn't easily reference the second table, maybe I could join the two together. This led me towards considering a combined collection which was fairly simple to create, adding the following to the OnVisible property of the first screen:

 

ClearCollect
(
    collection,
    AddColumns
    (
        Items,
        "Manager",
        First
        (
            Filter
            (
                'Managers',
                'Trainee email' in 'Trainee'.Email
            )
        ).Manager.Email
    )
)

 

I actually appended four columns from Managers to Items in this way which allowed me to simplify the filters on the gallery list by filtering the collection rather than the data source.

 

Not 100% sure if using a collection for the whole data source is a good idea in the long run but at least this problem is resolved finally!

View solution in original post

2 REPLIES 2
cattre
Resolver I
Resolver I

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?

cattre
Resolver I
Resolver I

I've finally cracked it and thought I'd update this post in case anyone else has a similar issue in future.

 

I started thinking that, if I couldn't easily reference the second table, maybe I could join the two together. This led me towards considering a combined collection which was fairly simple to create, adding the following to the OnVisible property of the first screen:

 

ClearCollect
(
    collection,
    AddColumns
    (
        Items,
        "Manager",
        First
        (
            Filter
            (
                'Managers',
                'Trainee email' in 'Trainee'.Email
            )
        ).Manager.Email
    )
)

 

I actually appended four columns from Managers to Items in this way which allowed me to simplify the filters on the gallery list by filtering the collection rather than the data source.

 

Not 100% sure if using a collection for the whole data source is a good idea in the long run but at least this problem is resolved finally!

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (75,128)