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.
ClearCollect(colObsID, ShowColumns(Filter('Observations',ObsLocation=varUserLocation), "ID"))
Next1, add all of the user's observations. (Just the ID for now.)
Collect(colObsID, ShowColumns(Filter('Observations',Author.email=varUserEmail, "ID"))
Next2, determine if the user is a manager, then determine who are all the underlings.
Set(gtblMyEmps, Filter('Employees',ManagerEmpID = gvarWorker.EmpID));
Set(gfWorkerIsManager, CountRows(gtblMyEmps) > 0);
Next3, Get the ID's of all of the underlings Observations.
Filter('Observations', Author.Email = WorkEmail),
(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.
FINALLY, use the collection of unique ID's to obtain a Distinct List of Observations for good ol' Bob. Something like:
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.
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.
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.
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.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Join us for the next call on June 15, 2022 at 8am PDT.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.