cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luciano_A
Helper I
Helper I

Help with building a gallery/table that filters/lookup trough 2 different sharepoint lists

Scenario: I have two SP lists and I want to represent items from List "B" based on a "match" made on list "A".

 

List "A":

 

Users (Person / Group)Approver (Person / Group)
User AApprover A
User BApprover B

 

List "B":

 

User (Person / Group)Date ( Date )Approved (Yes/No)
User_A13/05/2020Yes
User_B13/05/2020No

 

Note: I collect current logged user on OnVisible in a var named CurrentUser

 

Question:

How can I filter/show items from List "B" based on the combination found in list "A" ( Approver B should be able to see only User B items from list "B")?

 

Any help appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions

hmm, seems you might want to switch places of your lists in formula and add text() to get proper text email from lookup function:

Filter(
'List B',
Text(User.Email) =  Text(LookUp('List A',Approver.Email=CurrentUser.Email).Users.Email)

),

 

or simply add Text() to this real formula:

Filter(
'Biogen Hot Desk: Bookings',
Text(Utente.Email) = Text(LookUp('Biogen Hot Desk: Approvers',Approver.Email=CurrentUser.Email,User.Email) )

can't check it on my side, let me know if it worked 🙂

View solution in original post

7 REPLIES 7
FlyitComPl
Frequent Visitor

Hi @Luciano_A 

should be fairly easy to achieve that with Filter function - see reference: https://docs.microsoft.com/pl-pl/powerapps/maker/canvas-apps/functions/function-filter-lookup

In your scenario:

Filter(ListB, User.DisplayName = CurrentUser.DisplayName)

or sth very similar. Hope it helps. I assumed that CurrentUser = the right Approver in List A, so List A is not necessary to show only the records of List B where his approvals are stored. 

Good luck

Jakub

Filter(ListB, User.DisplayName = CurrentUser.DisplayName)

 

This would only show items entered by the approver itself, I want the "approver" to see items entered by the users that he is matched with by List A.

 

I think I am very near to the solution, this is the latest I've come up with:

 

Filter(
'List A',
Text(User.Email) = LookUp('List B',Approver.Email=CurrentUser.Email)

)

 

Filter(
'List A',
Text(Utente.Email) = LookUp('List B',Approver.Email=CurrentUser.Email,User.Email)

hmm, seems you might want to switch places of your lists in formula and add text() to get proper text email from lookup function:

Filter(
'List B',
Text(User.Email) =  Text(LookUp('List A',Approver.Email=CurrentUser.Email).Users.Email)

),

 

or simply add Text() to this real formula:

Filter(
'Biogen Hot Desk: Bookings',
Text(Utente.Email) = Text(LookUp('Biogen Hot Desk: Approvers',Approver.Email=CurrentUser.Email,User.Email) )

can't check it on my side, let me know if it worked 🙂

View solution in original post

It worked, but not 100% - I can pull data for only 1 user that way ( I think that's the way Lookup is working, need to find an alternative to that? )

I tried to use Search instead, but can't get to where I want 😞

 

Galler View of my users ( Approver View ).png

 

Filter(
'List: Bookings',
Text(Utente.Email) = Search('List: Approvers',Text(CurrentUser.Email),"Approver")

)

glad to hear that sth worked 🙂

yes, that's the way LookUp does the thing. You then might want to :

1. Search List A to find all users that are in responsibility oflet's say Approver A and

2. make collection UsersForAproverA - that stores all of users out of this search.

3. Filter List B like you did so far, but for each user from your collection and add results to new collection UsersFromListBforApproverA.

 

Hope that makes sense. Good luck!

Jakub

That's great! What you said is what has come to my mind somehow (As you see in my screenshot, I started to build & visualize the collection "UsersForApproverA" - This means to me that I most-likely I am in the good direction in understanding how the PowerApps world works 🙂

 

I am working now in the Step 3 you mentioned, not sure if I understood it 100% but I think I am on the right way, THANK YOU!

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,221)