Parsing collections with ForAll() with Rate Limit Exceeded

[GDSI]

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)));

This is the point where PowerApps produces the "Rate Limit Exceeded" error and fails to properly populate the colObsSearchResults collection.

What is the "proper" way to achieve the collection population of the Search Results, that Bob needs to see?

Please and Thanks.

[Adrian_Celis]

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));

[GDSI]

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.

[Adrian_Celis]

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.