cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
peter_Ch
Frequent Visitor

Construct a filter that does not give a warning

I have what I think is a fairly simple problem. This is a formula I have in the items section of my Browse Gallery.

 

 

If(CboArea.Selected.Value = "All",SortByColumns(Filter([@Volunteers],ThisRecord.Status = cboStatus.Selected.Value),"Sname", Ascending),SortByColumns(Filter([@Volunteers],ThisRecord.Status = cboStatus.Selected.Value And CboArea.Selected.Value in ThisRecord.Area), "Sname", Ascending))

 

 

I changed it from CboArea.Selected.Value = ThisRecord.Area to "in" so that I could have a person in 2 areas and still be selected. This way (=) works fine but when I set it to (in) there is a warning that it could have trouble with large databases. It still works okay because I only have 50 records at most, but is there another way I can do it that won't bring about the warning?

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

@peter_Ch 

If your [@Volunteers] list will never be more than 500 or 2k at most, then you can create a collection in the OnVisible property of your screen  ClearCollect(colVolunteers,[@Volunteers}) and replace [@Volunteers] in your formula with colVolunteers and you won't get a warning. 

View solution in original post

7 REPLIES 7
Bilakanti
Responsive Resident
Responsive Resident

@peter_Ch  You can use ShowColumns function to get rid of the Delegation warning.

KvB1
Solution Specialist
Solution Specialist

It is, you can use a prefilter using the With( syntax to filter your data source with deligatable filters, and then filter the prefilter using non-deligatable filters (such as 'in')

 

However, if your deligatable filter has more than 2000 records returned from it, powerapps will only retrieve the first 2000 records.

Drrickryp
Super User
Super User

@peter_Ch 

If your [@Volunteers] list will never be more than 500 or 2k at most, then you can create a collection in the OnVisible property of your screen  ClearCollect(colVolunteers,[@Volunteers}) and replace [@Volunteers] in your formula with colVolunteers and you won't get a warning. 

View solution in original post

I tried what you suggested and had this as my items function

If(CboArea.Selected.Value = "All",SortByColumns(Filter(colVolunteers,ThisRecord.Status = cboStatus.Selected.Value),"Sname", Ascending),SortByColumns(Filter(colVolunteers,ThisRecord.Status = cboStatus.Selected.Value And CboArea.Selected.Value in ThisRecord.Area), "Sname", Ascending))

The problem is that no items are displayed even though I can see that colVolunteers is populated when I view the first 5 items in the collection. What am I missing?

I'll investigate ShowColumns. Thanks for your help

peter_Ch
Frequent Visitor

I'll need to do some more learning on non-deligatible filters before I can use this solution. Thanks for your help

Actually, I just came back to the App and it now works. It just took some time getting the data. THANKS !

You have really mad me focus more on learning about Collections and their usage!

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 (4,399)