Fundamental restrictions due to delegation?

[RMDNA]

Hi all,

As I get better at PowerApps and am able to write the formulas that (should) give me the results I need, I increasingly find myself running into issues with delegation. From reading the documentation, I understand the reason and resulting limitations, but it seems like it's severely restricting what I'm able to build. From looking around the board, people have built some very comprehensive, complex solutions, and I'm curious how people are "bypassing" these limits.

How are people doing otherwise simple operations like filtering on text strings and/or more than two conditions, or across multiple tables/collections? Do you all have hyper-specific data sources or something? Is no one filtering on text strings or on fields with text values?

Specific question: I have a gallery with a bunch of time entry data for people on their projects. I want the user of the app to have the gallery only show them the projects they're an approver on, so I created a collection that returns that list. Gallery code:

Spoiler

SortByColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                GroupBy(
                    AddColumns(
                        Filter('Time Entries','Time Entries (Views)'.'Time Entries for Approval',
                        Date>VarSelectedStartDate&&Date<VarSelectedEndDate),
                    "DateString",Text(Date,ShortDate),
                    "ResourceName",'Bookable Resource'.Name,
                    "ProjectName",Project.'Project Name'),
                "DateString","ResourceName","ProjectName","Prj Data"),
            "Minutes",Sum('Prj Data',Duration),"GUIDs",Concat('Prj Data','Time Entry'&", ")),
        "ResourceName","ProjectName","Other"),
    "TotalTime",Sum(Other,Minutes),"AllGUIDs",Concat(Other,GUIDs&", ")
    ),
"ResourceName",Ascending)

Collection code is in the spoiler. Even creating the collection, I had to add a column to turn a "yes/no" into a Boolean so it would filter correctly, since filtering on "yes" wasn't working.

Spoiler

ClearCollect(
    ColUserApprovals,
    ShowColumns(
        Filter(
            AddColumns(
                Filter('Project Team Members','Bookable Resource'.Name = VarCurrentUser.FullName),
                "AppBoolean",If(msdyn_projectapprover in "Yes",1,0)),AppBoolean = 1),
        "msdyn_bookableresourceid","msdyn_project))

Then I tried to add a condition in the gallery's Filter() like: "&& Project.'Project Name' in ColUserApprovals.msdyn_project.'Project Name' but got delegation errors.

Part of my confusion with PowerApps as a whole so far is just how complex a formula can be for what I would consider to be an otherwise simple operation. WRT the gallery, aggregation of data up a level or two and filtered by dates from a date picker are three nested AddColumns and two GroupBys. I'm sure there may be a significant amount of inefficient coding, but something seems off here.

[WarrenBelz · Accepted solution]

Hi @RMDNA ,

I have a blog on Delegation that hopefully explains it in more detail for you. Happy to elaborate further with any subsequent questions, however for a start you have

• An in Filter
• Date Filters
• A Sum Statement
• GroupBy
• AddColumns

None of which are Delegable (although the least two will not give you warnings).

You are correct in that quite complex solutions can be built with some planning on your data structure.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[RMDNA]

Hi @WarrenBelz,

Thanks for the succinct reply to a rambling post. I figured it was a data issue, but my concern is that we're working with data from Dynamics 365, which is just a giant pile of lookups. A good third of fields in the tables I need to work with are just lookups to other tables.

That would certainly help explain why building this app has been so difficult. I did what I could to use collections, but even the filters I would need to apply to create them run up against those limits.

Not sure where to take it from here, given that this is the data we have to work with, but I'll keep working at it.