Filtering Gallery Based On Variable Created From User Logged-In Email and Access List - User Has Multiple Access Rows In SharePoint

[mkusler]

The OnStart for my app performs the following:

Set(

    vUserMail,

    User().Email

);

Set(

    vUserGroup,

    LookUp(

        Test_Competency_Access,

        Email = vUserMail,

        Function

    );

)

I then have a gallery with the following code in Items:

Filter(Test_Competency, Function = vUserGroup && Competency = CB3.Selected.Result)

My SharePoint Access List looks like this:

The OnStart and Gallery filter work just fine to provide a basic level security based on the user logged in for a single function. However, what if a person has access to multiple functions, therefore has multiple rows in my SharePoint access list.

In this scenario, is there a way the gallery can show both “Non-Tech” and “Tech” options for person "A"?

[Feiteira]

Hi there @mkusler ,

If you can have multiple records for one person then you sould use Filter() instead of LookUp()

[mkusler]

@Feiteira 

I guess I'm not sure how that would work with the gallery and only show data from the other SharePoint list that meets multiple "Function" criteria.

[Feiteira · Accepted solution]

@mkusler 

Try to use this:

Set(
    vUserGroup,
	Concat(
		Filter(
	        Test_Competency_Access,
	        Email = vUserMail
	    ).Function,
		ThisRecord.Filter & "; "
	)
)

It should return "Non-Tech; Tech;"

Having that, in the Gallery Filter you can check if the "Function" value is present in the string above.

Filter(
	Test_Competency,
	Function in vUserGroup
	&&
	Competency = CB3.Selected.Result
)

 Note that I use "in" instead of "=" (equals) and so it checks if the "Function" value exists in any part of the string ( example: "Tech" in "Non-Tech; Tech;" = True)

Can this help you out this way?

[mkusler]

@Feiteira 

That worked! One more question though. On the Gallery I am getting delegation warning. Is there anyway I can get rid of that as the SharePoint List that is being filtered contains over 2000 records?

Thanks!

[Feiteira]

@mkusler 
Great, happy it worked 😀

PowerApps will allways show that warning as it has no native way of showing more than 2000 records (keep in mind that the PowerApp configuration by default is set to get only 500 records but you can raise this to 2000)

The only way to make it work is by Filtering the data in the list so it return less than 2000 records, which you already are doing, so, unless you expect to return 2000+ records with this filter, don't worry about delegation for this case.

[mkusler]

@Feiteira 

Awesome! Thanks for all your help. 10 Gold Stars.

[Wolfmeyn]

is your function column a "Choices" column or what kind of column is it, im working on a similar task