cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GDSI
Helper I
Helper I

Parsing collections with ForAll() with Rate Limit Exceeded

Scenario: Need to display (in a gallery) a collection of Search Results based on Observations that are made by employees. Some employees are just employees, but others are Managers. Further, some are Location Admins. So, if Bob is a Location Admin of Area B AND a manager of Sally and Rick, then Bob can see a collection of his, Sally & Rick's, and everyone in Area B's Observations. (If Bob was just a manager, or just a location admin, then this would be easy. Because his employees may already be in the Location of which he's an admin, this is where it gets tricky. Any duplicates must be prevented or eliminated before displayed.)

 

First, use a routine that checks if the current user is a Location Admin. If yes, then populate a collection of Observation ID's based on that Location.

Something like:

ClearCollect(colObsID, ShowColumns(Filter('Observations',ObsLocation=varUserLocation), "ID")) 

 

Next1, add all of the user's observations. (Just the ID for now.)

Something like:

Collect(colObsID, ShowColumns(Filter('Observations',Author.email=varUserEmail, "ID"))

 

Next2, determine if the user is a manager, then determine who are all the underlings.

Something like:

Set(gtblMyEmps, Filter('Employees',ManagerEmpID = gvarWorker.EmpID));

Set(gfWorkerIsManager, CountRows(gtblMyEmps) > 0);

 

Next3, Get the ID's of all of the underlings Observations.

Something like:

ForAll(gtblMyEmps, Collect(colObsIDSearchResults,

ShowColumns(

Filter('Observations', Author.Email = WorkEmail),

"ID")))

(I assume that this means: "Find matching records where gtblMyEmps.WorkEmail = Observations.Author.Email"  (PLEASE LET ME KNOW IF IT DOESN'T!)

 

Next 4: Now that ObsID from multiple criteria have been added to this collection, it's time to strip it down and get the Unique Collection of the Last 2000 IDs.

Something like:

ClearCollect(colObsIDSearchResultsUnq,
FirstN(Sort(Distinct(colObsIDSearchResults,ID),
Result,Descending),2000));

 

FINALLY, use the collection of unique ID's to obtain a Distinct List of Observations for good ol' Bob. Something like:

ForAll(colObsIDSearchResultsUnq,
Collect(colObsSearchResults, Filter('Observations',ID = Result)));

 

Naturally, this is the point where PowerApps destroys my soul and produces the "Rate Limit Exceeded" error, and fails to properly populate the colObsSearchResults collection.

 

If I could properly Loop, or could properly Trap for an error, or could properly do many other basic proper programming practices, I would have this solved. But, in the half-baked pie world HBPW of PowerApps, what is the "proper" way to achieve the collection population of the Search Results, that Bob needs to see, such that he can do his job.... properly?

 

Please and Thanks.

3 REPLIES 3
Adrian_Celis
Super User
Super User

Hi @GDSI 

1) Up to how many records do you think colObsSearchResults will reach?

2) On your Step4, if you trim down the number of records in colObsIDSearchResultsUnq, up to what number would it run fine. Like for example if you trim it to 500, would it work?

ClearCollect(colObsIDSearchResultsUnq,
FirstN(Sort(Distinct(colObsIDSearchResults,ID),
Result,Descending),500));

I can't trim it, as it's a growing list. Because this error is based on the number of hits per minute, I believe, even if I tried to do 4 sets of 500, I would expect the error to still happen. But, feel free to further clarify where you're headed, or if you have a completely different paradigm.

Hi @GDSI 

Just an idea so that you can avoid the ForAll loop.

In Step 4, once you have your unique ID's in your collection named colObsIDSearchResultsUnq, you can create another table to include your Observations and then add another column using AddColumns function which will serve as a lookup to your colObsIDSearchResultsUnq.

Example:

Collect(NewCollection, AddColumns('Observations',"LookUpColumn",!IsBlank(LookUp(colObsIDSearchResultsUnq, Result = ID)))
)

 

Now you have an Observations table named 'NewCollection' with a lookup column named 'LookUpColumn' that says true if it is in colObsIDSearchResultUnq and false if it is not.

 

You can use it to filter, etc.

 

Not sure how the performance will be for data of very big sizes though.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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