cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam4
Resolver I
Resolver I

Filtering a gallery that is already filtered in "Items"

Hi, 

 

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.

 

Any suggestions?

 

Thanks

9 REPLIES 9
mdevaney
Super User
Super User

@Sam4 

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.

 

 

aaa.PNG

 

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."

WarrenBelz
Super User
Super User

You might try a collection with AddColumns, then display the collection in the gallery

 

ClearCollect(
colStaff,
AddColumns(
SP_1,
"MyStatus",
LookUp(
SP_2,
ID=MyIDV,
Status
)
)
);


ClearCollect(
colStaff,
Filter(
colStaff,
MyStatus="Pending")
)

 

 

@WarrenBelz
It is possible to edit a previous post

OK - found it and edited

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)

 

Filter(
   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?

 

Thanks

 

@WarrenBelz , thanks for your replay. Unfortunately can't create the collection in that way as I will get the delegation warning. 

 

Kind Regards

 

 

 

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.

@Sam4 

Try throwing your User().FullName into a variable and using that in your filter eg

 

Set(varUserFullName, User().FullName);

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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