web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Alternative to using "...
Power Apps
Answered

Alternative to using "In" within a filter

(0) ShareShare
ReportReport
Posted on by rachel_bisland 35

Hello,

I am filtering a gallery which datasource is "Pupil Records" - A SharePoint list. 

Within the filter, I have a formula which says "Code in Pupils.Code".  "Pupils" is another SharePoint list. 

Code appears in both lists and is a unique identifier.

 

I am wanting to perform this action to only return 'pupil records' where the 'pupil' is in the master list.  

However, "In" is not delegable.  

 

Is there another way I can filter the data which would be delegable?

Categories:
Building Power Apps
  • KvB1 Profile Picture
    KvB1 1,596 on at

    Not really, but you can do some other stuff inside the app using LookUp.

     

    In a gallery you could have the master list in the items property, then have LookUp(Pupil Records;Code=ThisItem.Code) for various labels

     

    Or you create a new collection using a ForAll(Pupils,Collect(CombinedCollection,{Name: LookUp(Pupil Records,Code=ThisRecord.Code).Name}))

     

    Edit: since you are being aware of delegation, i'm assuming that your master list contains over 2000 records. Is this true? 

  • rachel_bisland Profile Picture
    rachel_bisland 35 on at

    Yes the master list has the potential to have more than 2000.  

     

    I think a collection might be the way to go.  Should I create the collection within the onStart property of the screen?

  • KvB1 Profile Picture
    KvB1 1,596 on at

    Yes, but you would need to retrieve the master list to PowerApps first. If you need to operate with collections larger than 2000 items, you can't leave the data on sharepoint.

     

    Even when you are using delegatable functions, you will run into problems. For example, if you were to create a collection using the master list on sharepoint in the Items property, it will only be 2000 rows.

     

    You would need to collect the master list from sharepoint in batches, creating a local list that you will use in the app. Here is a nice explanation: Overcome 2000 items limit using Power Apps Collect function • Tomasz Poszytek, Business Applications MVP

  • rachel_bisland Profile Picture
    rachel_bisland 35 on at

    Here is my filter formula.  I previously had many IF functions within it, which I realised were not delegable.  So I have fixed this by carrying out some IF functions within hidden labels on my app screen and I refer to these now instead. 

     

    My "Pupil Records" datasource will have 7000+ records within it.  However, it is unlikely that my "Pupils" datasource will go above 2000 (just checked this with my boss).

    So I wonder, if having that one non-delegable function in the filter for "Code in Pupils.Code" would cause any issues?  

    rachel_bisland_0-1627564535481.png

     

     

  • KvB1 Profile Picture
    KvB1 1,596 on at

    Yes it will. You are filtering a datasource using a non delegetable function. The data source that you filter 'Pupil Reocrds' will have more than 2000 items. This means that PowerApps will simply retrieve the first 2000 records of your data source and filter them locally.

     

    However, if your master list wont go above 2000 records you can still use the suggestions I mentioned earlier.

     

    OnStart of your app:
     

     

    ForAll(
	Pupils,
	Collect(
		LocalCollection,
		{
			SomeColumn: LookUp(
					'Pupil Records,
					Code=ThisRecord.Code
					).SomeColumn
		}
	)
)

     

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @rachel_bisland 

    Change your formula to prefilter all items that have delegable criteria and then filter that with non-delegable.

    With({_preFilter:
 Filter('Pupil Records',
 House=varHouse &&
 Site = varSite &&
 StartsWith(EnrolYear, varEnrolYear) &&
 StartsWith(Escalated, ESCALATEDFILTERCALC.Text) &&
 StartsWith(RecordStatus, STATUSFILTERCALC.Text) &&
 StartsWith(CategoryMain, MAINCATFILTERCALC.Text) &&
 StartsWith(CategoryOther, OTHERCATFILTERCALC.Text) &&
 StartsWith(Complaint, COMPLAINTFILTERCALC.Text)
 )},

 SortByColumns(
 Filter(_preFilter,
 Code in Pupils.Code &&
 Date >= DateValue(DATEFILTERCALC.Text)
 ),
 "Date", Descending, "SurName", Ascending
 )
)

    This will prefilter the datasource with all delegable criteria and use it in a With scoped _preFilter variable.  Then it will filter that table with the non-delegable criteria.

     

    The point/goal is that the prefilter will be less than 2000 records and thus record limit does not become a factor.

    There is no need to pull in all of your records into the memory of your app.  This is slow and a performance and memory issue.  If you cannot avoid it then you have to in some cases, but that should be an absolute last and final resort and only after attempting to reshape the data as much as possible to avoid it.

     

    I hope this is helpful for you.

  • rachel_bisland Profile Picture
    rachel_bisland 35 on at

    Thank you Randy - this is really helpful.  

    I also search on text that is input to a search text box and wondered where I should put this into the formula you sent?

    StartsWith(FirstName, 'HO-PupilName-SearchBox'.Text) || StartsWith(Surname, 'HO-PupilName-SearchBox'.Text) || StartsWith(FormClass,'HO-PupilName-SearchBox'.Text)

     

    It returns blank gallery results whenever I add it in to either the pre filter or non delegable part of the filter.

  • Verified answer
    RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @rachel_bisland 

    Since StartsWith is delegable, you can put it in the prefilter.

    With({_preFilter:
 Filter('Pupil Records',
 House=varHouse &&
 Site = varSite &&
 StartsWith(EnrolYear, varEnrolYear) &&
 StartsWith(Escalated, ESCALATEDFILTERCALC.Text) &&
 StartsWith(RecordStatus, STATUSFILTERCALC.Text) &&
 StartsWith(CategoryMain, MAINCATFILTERCALC.Text) &&
 StartsWith(CategoryOther, OTHERCATFILTERCALC.Text) &&
 StartsWith(Complaint, COMPLAINTFILTERCALC.Text) &&
 (StartsWith(FirstName, 'HO-PupilName-SearchBox'.Text) || 
 StartsWith(Surname, 'HO-PupilName-SearchBox'.Text) || 
 StartsWith(FormClass,'HO-PupilName-SearchBox'.Text)
 )
 )},

 SortByColumns(
 Filter(_preFilter,
 Code in Pupils.Code &&
 Date >= DateValue(DATEFILTERCALC.Text)
 ),
 "Date", Descending, "SurName", Ascending
 )
)

    Also, if the prefilter (without the startswith functions) returns less than 2000 records, then you can take advantage of the non-delegable Search function to widen your search.

    i.e.

    With({_preFilter:
 Filter('Pupil Records',
 House=varHouse &&
 Site = varSite &&
 StartsWith(EnrolYear, varEnrolYear) &&
 StartsWith(Escalated, ESCALATEDFILTERCALC.Text) &&
 StartsWith(RecordStatus, STATUSFILTERCALC.Text) &&
 StartsWith(CategoryMain, MAINCATFILTERCALC.Text) &&
 StartsWith(CategoryOther, OTHERCATFILTERCALC.Text) &&
 StartsWith(Complaint, COMPLAINTFILTERCALC.Text)
 )
 )},

 SortByColumns(
 Search(
 Filter(_preFilter,
 Code in Pupils.Code &&
 Date >= DateValue(DATEFILTERCALC.Text)
 ),
 'HO-PupilName-SearchBox'.Text, "FirstName", "Surname", "FormClass"
 ),
 "Date", Descending, "SurName", Ascending
 )
)

    It's all about balancing the number of records returned from the prefilter to be less than 2000.

  • rachel_bisland Profile Picture
    rachel_bisland 35 on at

    When I try the first option of putting it within the prefilter, the gallery returns a blank.  I then thought I would try adding in an extra prefilter where AcademicYear=varAcademicYear, as this would definitely bring the records below 2000 to try your second option, but this also returns a blank gallery.  Neither action seems a big change so not sure why it is returning blank gallery.

    (when adding search box result to prefilter)

    rachel_bisland_0-1627572707047.png


    (When adding another filter within the pre-filter reduce records without the starts with functions)

    rachel_bisland_1-1627572776295.png

     

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @rachel_bisland 

    You missed some parens in the formula you have from what I provided.  The OR'ed StartsWith's should be enclosed in a paren pair.

    Correct that and let's see where you get.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 397 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 354

#3
WarrenBelz Profile Picture

WarrenBelz 232 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans