Answered

Row limit problems in a ForAll Collect

(0) Share

Report

Posted on by ChrisBrydges75

I am getting a delegation problem in a Collect that is looping using For All

I have a list of Business Units in one SharePoint List. Each Business has a District Code, Each District code is managed by one user.

I then have another list that contains Risk Assessment header records, this contains the status of the assessment.

I need to populate a gallery with the Risk Assessment status for all Business Units (BUN)

I am using the follwing to get the details.

The first part gets the list of Business Units (BUN) thatthe user is responsible for, their district number is derived from the label:

ClearCollect(
DistrictBUNsCollect,
Filter(
Sites,
'Hierarchy 5 Code' = DistrictNumberLabel.Text
)
);

I then put this into a gallery.

The next part loops through each Business Unit (BUN) in the Gallery:

ForAll(
DistrictBUNGallery.AllItems,
Collect(
AllRiskAssessmentsThisDistrict,
Filter(
CovidRiskAssessAudit,
BUN = DIstrictGalleryBUNLabel.Text
)
)
)

It will only ever loop through the first 500 rows though, there may be 10s of thousands in the future so I need to find another method for do

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@ChrisBrydges75

Your issue is not delegation, your issue is record limit. You can only pull back x number of records. x is defined in your app settings. The default is 500.

Change your formula for the first Gallery Items property to:

Filter(Sites, 'Hierarchy 5 Code' = DistrictNumberLabel.Text )

Change your formula in the second Gallery Items property to:

Filter( CovidRiskAssessAudit, BUN = yourFirstGallery.Selected.DistrictGalleryBUNLabel.Text )

Although record limit will still apply (and the max you can have is 2000), the Gallery is a little special in that it performs its own type of record gathering and ignores the record limit property.

I hope this is helpful for you.

Was this reply helpful? Yes No
ChrisBrydges75 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thats nearly what I need, the issue is that I cant reference the selected item as I need to display the status of all of the items in the gallery.

I am nearly there, I can do the first bit, I can get the ID with this filter to bring back the most recent record

Max(
Filter(
CovidRiskAssessAudit,
BUN = ThisItem.BusinessId
),
ID
)

The image below shows what I am trying to achieve.
The green circle is the BUN that comes from the initial collect/filter (this one is not an issue as there will always be less than 2000 records), the red circle is the most recent assessment ID that has been derived from the MAX formula above, I then need to look up/filter the rest of the fields highligetd in blue based on the assessment ID from the CovidRiskAssessAudit list , the problem is that it is all in one gallery so I need another formula similar to the above to get the rest of the fields back.

Is there a way of filtering a data table on a list of values in a collection or gallery? Would that be a way around it? Something like this (it doesnt work but something similar)

Filter(CovidRiskAssessAudit, ID in DistrictBUNGallery.AllItems.BUN)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@ChrisBrydges75

Are you stating that there are multiple assessment records for the formula:

Filter( CovidRiskAssessAudit, BUN = yourFirstGallery.Selected.DistrictGalleryBUNLabel.Text )

Or, in other words...your data needs to be grouped by a particular column?

When I start seeing Max in a formula in a Gallery...this implies some level of grouping to me.

Was this reply helpful? Yes No
ChrisBrydges75 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thats correct, there may be multiple records for each Business Unit, I only want to display the most recent one, that is why I am deriving the max ID for each Business Unit first.

This is then the formula for the Completed Date:

"Completed: " & LookUp(AllRiskAssessmentsThisDistrict,Text(ID) = LatestReviewIDLabel.Text,EndDateTime)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@ChrisBrydges75

The problem with that approach is that you're still going to have all the other records in the gallery.

What you need to use is the GroupBy function. What is the grouping of your data? Is there some AssessmentID column that identifies a group?

If so, then your formula for the Gallery should really be:

GroupBy( Filter( CovidRiskAssessAudit, BUN = yourFirstGallery.Selected.DistrictGalleryBUNLabel.Text ), "AssessmentID", "_assessments" )

Then, you can derive the most recent information from the _assessments records either in the Gallery labels:

  First(Sort(ThisItem._assessments, EndDateTime, Descending)).EndDateTime

Or, if you'd have to repeat the above in multiple labels/controls, then you can expose the latest record in the Items property instead:

AddColumns( GroupBy( Filter( CovidRiskAssessAudit, BUN = yourFirstGallery.Selected.DistrictGalleryBUNLabel.Text ), "AssessmentID", "_assessments" ), "_latestAssessment", First(Sort(_assessments, EndDateTime, Descending)) )

Then your label in the Gallery would be:

ThisItem._latestAssessment.EndDateTime

and all other data columns you want would follow that same process ex.  ThisItem._latestAssessment.Status

What is AllRiskAssessmentsThisDistrict??

Was this reply helpful? Yes No
Verified answer

ChrisBrydges75 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for all of your help.

I played around with it and got this to work... I dont know why it works, but it does! It works in the context of each row of the gallery.

If(
IsEmpty(
Filter(
CovidRiskAssessAudit,
ID = LatestReviewIDLabel.Text
)
),
LookUp(
CovidRiskAssessAudit,
ID = LatestReviewIDLabel.Text,
StartDateTime
),
LookUp(
CovidRiskAssessAudit,
ID = LatestReviewIDLabel.Text,
StartDateTime
)
)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisBrydges75

That will work, but your performance will be horrible as your list grows.

You really need to group the data and derive from that.

But, if you've got what you need, drop back when performance becomes the issue.

Was this reply helpful? Yes No
ChrisBrydges75 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, yes I will add in the grouping now that I understand how to get it to work

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@ChrisBrydges75

Yes, that will provide much better performance as your current formula is doing a full table filter and then additional lookup *again* to the data for each row in your gallery.

Utilizing the GroupBy will do all of this in one step for all rows.

It should give you better mileage in the long run.

Was this reply helpful? Yes No