Filtering gallery using separate SharePoint list

[cattre]

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!

[cattre]

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 · Accepted solution]

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!