I'm just thinking the best way of doing the following and I haven't had any idea at the moment. Any help will be great.
I have data in two SharePoint Lists. I would like to present in a gallery a filtered combination of both lists.
The ITEMS property in the gallery is a filtering of a SharePoint List (SP_1) based on the current user. This gallery has 3 elements:
- ThisItem.MyID - ThisItem.Name - Lookup(SP_2,ID=MyIDV).Status
something like this:
1 John Completed 2 Mark Completed 3 Lisa Pending 4 Michael Completed
What I would like to do is this gallery displays only the items where status = Pending.
As this is based on SharePoint I don;t want to have any delegation problems and I really dont know how I can do that filter or if that is possible.
The simplest way to get around the delegation issue might be to create a LookUp column in SharePoint instead of performing a table join using the LookUp function in PowerApps.
Go to your list settings in SharePoint, then Create A Column. Select the LookUp column type. Get Information from your 2nd table and add the Status Column you need to show in your gallery.
Now the code to FILTER the gallery becomes much simpler
Filter( your_datasource_name, Name = User().FullName YourStatusColumn.Value = "Pending" )
Note: I added an example Username filter but you should replace it with whatever is currently working.
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
You might try a collection with AddColumns, then display the collection in the gallery
Thanks for the reply @mdevaney ,
Yes, this is what I'm looking for. However, I've tried it and I get the delegation warning and the gallery it is not even displaying results when I apply the formula. The formula is ("highlighted in red what the systems identify as delegation warning)
MySPList, Name = User().FullName && 'SP_ID:Status'.Value = "Pending"
It is weird as I'm able to display the result in the gallery but not in the items formula. If I add a new label to the gallery with Text= 'SP_ID:Status'.Value, it shows "Pending" or "Completed" correctly.
Any ideas what I'm doing wrong?
@WarrenBelz , thanks for your replay. Unfortunately can't create the collection in that way as I will get the delegation warning.
OK @Sam4 - I have always been able to AddColumns off a "master" collection (I have about 200 users) so have learnt something new (result of AddColumns are not delegable from SharePoint directly).
So - if by chance you have less than 2,000 records, in SP_2, you might try collecting this list and then apply the AddColumns based on this collection.
Try throwing your User().FullName into a variable and using that in your filter eg
Filter( MySPList, Name = varUserFullName ...)
I tried just the first part of your filter on it's own using both a Person column and Text column in SP and for both instances using User().FullName gave me a delegation warning but when I used a variable it went away.
The Sharepoint delegation docs do mention only Email & DisplayName are delegatable so I'm thinking this may be your issue? See Note 1 here: https://docs.microsoft.com/en-us/connectors/sharepointonline/#powerapps-delegable-functions-and-oper...
Hi @Sam4 ,
Yes - sorry I went off on another tangent then realised that whatever you did (including collecting all the pendings) you still had a delegation issue.
I should have seen your issue and suggested this as the first thing I do on every app with user authentication is exactly that (set vUser to User().FullName).
A small side issue on this is the practice of using an always unique user value with email address as two John Smiths can cause issues.
Check out the News & Announcements to learn more.
Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.