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

Alternative to using "In" within a filter

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

11 REPLIES 11
KvB1
Solution Specialist
Solution Specialist

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? 

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
Solution Specialist
Solution Specialist

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

rachel_bisland
Frequent Visitor

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
Solution Specialist
Solution Specialist

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
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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.

RandyHayes
Super User
Super User

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

 

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!

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.

Users online (1,861)