Dynamic gallery contents based on SP list

(0) Share

Report

Posted on by Crispybits7777

Hi all

So I have a SP list that looks like this:

Each of the Yes/No columns is whether that person should have access to the data for that site.

On a number of other pages I have galleries, and I want to filter the galleries to only the sites where the user has the "Yes" permission from that list.

I know I could brute force it by creating a big switch command with every combination of Yes/No concatenated together and then the appropriate filter applied to the Items property of the gallery, but I'm trying to make a different approach work. I don't know if I can't make it work because it's not possible, or if I'm just fudging up the code specifics...

Here is the Items code for one of the galleries without this site filter applied:

Distinct(Sort(HREmployees,Surname,SortOrder.Ascending),Title)

I'm trying to write something like:

Filter(Distinct(Sort(HREmployees,Surname,SortOrder.Ascending),Title),
If(LookUp(HRAdministrators,Title=User().Email).HQ="Yes",NormalWorkSite="HQ",true) &&
If(LookUp(HRAdministrators,Title=User().Email).NCB="Yes",NormalWorkSite="NCB",true) &&
If(LookUp(HRAdministrators,Title=User().Email).NCD="Yes",NormalWorkSite="NCD",true) && ....)

I've left out some of the lines there because I already know it doesn't work like this. I get a red underline under "NormalWorkSite" (the field in the "HREmployees" list that stores each employees place of work) with the "Name isn't valid" tooltip.

Anyone got any idea how I might make this approach work to save me having some very longwinded and messy code? I'm not averse to restructuring the lists even...

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Crispybits7777 99 on at

Like
a
(0)

Report
Copy link

Link copied!
Small update, this code works if I only want to filter for a single site:

If(LookUp(HRAdministrators,Title=ActiveUser).HQ="Yes",Distinct(Sort(Filter(HREmployees,NormalWorkSite="HQ"),Surname,SortOrder.Ascending),Title))

However I don't see a way to add the next site - if I just use the & or && connector and the same code again with the next site abbreviations I get an error of invalid argument anywhere in the text and expected value on the & or &&

Was this reply helpful? Yes No
Crispybits7777 99 on at

Like
a
(0)

Report
Copy link

Link copied!
OK next update, I tried a different approach and put the following code into the button that navigates the user to this screen (it's the only way to get to the screen):

ClearCollect(EmpList,HREmployees); Clear(EmpList); If(LookUp(HRAdministrators,Title=ActiveUser).HQ="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="HQ"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).NCB="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="NCB"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).NCD="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="NCD"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).NCP="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="NCP"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).WFA="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="WFA"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).AGP="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="AGP"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).RSP="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="RSP"),Surname,SortOrder.Ascending),Title))); If(LookUp(HRAdministrators,Title=ActiveUser).THP="Yes",Collect(EmpList,Distinct(Sort(Filter(HREmployees,NormalWorkSite="THP"),Surname,SortOrder.Ascending),Title)))

The problem I'm having now is in the gallery even if I try and sort the list by surname like this:

Sort(EmpList,Surname,SortOrder.Ascending)

It's still only sorting by surname within each site, I get all of HQ first in alphabetical order by surname, then all of NCB in alphabetical order by surname, etc. Why is it doing that?

(By the way even if you're just lurking and have no idea how I'm making such a mess of this or how to fix it thank you all for being my rubber ducky today - just trying to explain this at each step is helping me make progress)

Was this reply helpful? Yes No
Verified answer

Crispybits7777 99 on at

Like
a
(0)

Report
Copy link

Link copied!
OK final update - it works now! 😄

I removed the distinct and sort from the code that was creating the collection, and added an additional line to the bottom of that:

ClearCollect(EmpList,HREmployees); Clear(EmpList); If(LookUp(HRAdministrators,Title=ActiveUser).HQ="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="HQ"))); If(LookUp(HRAdministrators,Title=ActiveUser).NCB="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="NCB"))); If(LookUp(HRAdministrators,Title=ActiveUser).NCD="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="NCD"))); If(LookUp(HRAdministrators,Title=ActiveUser).NCP="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="NCP"))); If(LookUp(HRAdministrators,Title=ActiveUser).WFA="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="WFA"))); If(LookUp(HRAdministrators,Title=ActiveUser).AGP="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="AGP"))); If(LookUp(HRAdministrators,Title=ActiveUser).RSP="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="RSP"))); If(LookUp(HRAdministrators,Title=ActiveUser).THP="Yes",Collect(EmpList,Filter(HREmployees,NormalWorkSite="THP"))); ClearCollect(EmpList,Sort(EmpList,Surname,SortOrder.Ascending))

I then added the distinct in the gallery items:

Distinct(EmpList,Title)

Doing this means I can't just use ThisItem.FieldName in the gallery labels, so they all look like this:

LookUp(HREmployees,Title=ThisItem.Value).KnownAs

Probably a janky way of getting there but it all works so I ain't messing with it 🤣

Was this reply helpful? Yes No