cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bendd221
Level: Powered On

Delegation issue without a warning, can anyone spot it through this messy code?

I know its a mess, but that code basically filters a gallery based on status, person and even free text.
There are no delegation warnings whatsoever yet there is a delegation problem here somewhere, and I cant seem to find it.
This app serves as a Ticketing system for two departments, and only after rollbacking 40 versions back delegation worked, and the app changed alot!
There are no noticable performance issues but the code is long and I doubt anyone but me can easily decipher it (which is a problem in itself, but powerapps has so many limitations I just couldnt make it any clearer on my skill level) so Im only asking you guys if you can spot any easily noticeable delegation problems here, thanks!

 

If(
    isAdmin,
    If(
        isPressed = false,
        If(
            Filter = "NOT COMPLETED",
            Sort(
                If(
                    NamesFilter = "All",
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = "NOT STARTED" || TaskStatus.Value = "IN PROGRESS"
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = "NOT STARTED" && AssignedPerson = "-" || TaskStatus.Value = "IN PROGRESS" && AssignedPerson = "-"
                    ),
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = "NOT STARTED" && AssignedPerson = NamesFilter || TaskStatus.Value = "IN PROGRESS" && AssignedPerson = NamesFilter
                    )
                ),
                Created,
                Descending
            ),
            Filter = "COMPLETED" || Filter = "NOT RELEVANT - DELETED",
            Sort(
                If(
                    NamesFilter = "All",
                    Filter(
                        If(
                            isComputing,
                            BataCompletedTickets,
                            LogisticsCompletedTickets
                        ),
                        TaskStatus.Value = Filter
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
                        If(
                            isComputing,
                            BataCompletedTickets,
                            LogisticsCompletedTickets
                        ),
                        AssignedPerson = "-"
                    ),
                    Filter(
                        If(
                            isComputing,
                            BataCompletedTickets,
                            LogisticsCompletedTickets
                        ),
                        AssignedPerson = NamesFilter && TaskStatus.Value = Filter
                    )
                ),
                Created,
                Descending
            ),
            Sort(
                If(
                    NamesFilter = "All",
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = Filter
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = Filter && AssignedPerson = "-" || TaskStatus.Value = Filter && AssignedPerson = "-"
                    ),
                    Filter(
                        If(
                            isComputing,
                            BATAOpenTickets,
                            LogisticsOpenTickets
                        ),
                        TaskStatus.Value = Filter && AssignedPerson = NamesFilter
                    )
                ),
                Created,
                Descending
            )
        ),
        If(
            Filter = "COMPLETED" || Filter = "NOT RELEVANT",
            Sort(
                Filter(
                    If(
                        isComputing,
                        BataCompletedTickets,
                        LogisticsCompletedTickets
                    ),
                    StartsWith(
                        Title,
                        TextInput1.Text
                    )
                ),
                Created,
                Descending
            ),
            Sort(
                Filter(
                    If(
                        isComputing,
                        BATAOpenTickets,
                        LogisticsOpenTickets
                    ),
                    StartsWith(
                        Title,
                        TextInput1.Text
                    )
                ),
                Created,
                Descending
            )
        )
    ),
    Sort(
        Filter(
            If(
                isComputing,
                BATAOpenTickets,
                LogisticsOpenTickets
            ),
            Author.Email = MyProfile.Mail
        ),
        Created,
        Descending
    )
)

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-siky-msft
Microsoft

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Hi @bendd221 ,


You are right, there is a limitation that Gallery can only load the first 100 items by default, so this is a dead end. Let's refocus on the formula and logic of codes.

After I reduce the codes and test, I find the delegation issue is from Sort function. 

The following is the reduced codes, please modify all condition to be true for the test. You will find this code is still undeletable.

 

 

If(
    isAdmin, // modify here, hardcode condition to be true
    If(isPressed = false, // modify here, hardcode condition to be true
        If(
            Filter = "NOT COMPLETED", // modify here, hardcode condition to be true
            Sort(
                If(
                    NamesFilter = "All", // modify here, hardcode condition to be true
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = "NOT STARTED" || TaskStatus.Value = "IN PROGRESS"
                    )
                ),
                Created,
                Descending
             )
         )
    )
)

 

 

If you remove the Sort function, and the code is like this, then you will find it becomes delegable. 

 

 

If(
    true, // modify here, hardcode condition to be true
    If(true, // modify here, hardcode condition to be true
        If(
            true, // modify here, hardcode condition to be true
                If(
                    true, // modify here, hardcode condition to be true
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = "NOT STARTED" || TaskStatus.Value = "IN PROGRESS"
                    )
                )
         )
    )
)

 

 

Note: try to set the delegation threshold to 5, it would be easier to check if the code is delegable.

Snipaste_2020-02-25_16-31-15.png

 

Overall, I suggest you remove all Sort functions. Please have a test to check if it works for you.

Sik

 

View solution in original post

Eelman
Level 10

Re: Delegation issue without a warning, can anyone spot it through this messy code?

@bendd221 

Firstly, yourself and @v-siky-msft are correct, the Sort() is causing the issue. However, I managed to bypass my delegation issues by putting the Sort() around the SP List not around the Filter(), eg

 

This returned ALL records

If(isComputing,Filter(Sort(mySPList1,Created,Descending), UserEmail=vMail),Filter(mySPList1, SiteName = "Site A"))

whereas this returned 5 records

Sort(If(isComputing,Filter(mySPList1, UserEmail=vMail),Filter(mySPList1, SiteName = "Site A")),Created,Descending)

 

Maybe try this structure on your formula?

 

 

View solution in original post

18 REPLIES 18
WarrenBelz
Level 8

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Hi @bendd221 ,

Not messy at all - much better parsed that a lot of blocks of code I have seen.

I was just typing out some assumptions when I spotted  this

Filter = "NOT COMPLETED"

Filter is a "Reserved" word and should not be used for a control, variable or field name.

I assume this is a Variable.

Try renaming this and see if it helps.

 

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.

bendd221
Level: Powered On

Re: Delegation issue without a warning, can anyone spot it through this messy code?

First of all thanks for replying!
It didnt work, but I permanently changed it regardless of the problem as it is probably better practice.
Any other ideas though? Its not a crucial problem as im working with two SP lists, one for the open and in progress tickets and one for the completed ones, and I can always just direct people to the actual list when looking for completed tickets (and open ones never reach the limit) but Ill be happy to get atleast some guidance on what to look for.

WarrenBelz
Level 8

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Hi @bendd221 ,

I will have a good look at this now - due to time zone issues, I will get back to you later.

WarrenBelz
Level 8

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Hi @bendd221 ,

The best thing I can do here is approach this as a "fresh set of eyes", making no assumptions, so can you please confirm the following

  • BATAOpenTickets  and BataCompletedTickets are the data sources
  • LogisticsOpenTickets is the field in this you are filtering on
  • isPressed is a Boolean Variable
  • Your renamed Filter is a String Variable
  • isComputing is a Boolean Variable
  • AssignedPerson is a String Variable
  • NamesFilter is a String Variable
  • TaskStatus is a Choices-based DropDown/ComboBox

Please let me know

Microsoft v-siky-msft
Microsoft

Re: Delegation issue without a warning, can anyone spot it through this messy code?

@bendd221 

 

What is the kind of data source? Could you please reduce the codes to find out where the issue is?

From my experience with this post, I suspect that the issue is that the IF function is inside the Filter function. Since your code is very long, I am not going to modify your logic. My solution is to change the data source in your code into a delegable data source.

The workaround:

1. Add two galleries in a new screen, named GalleryA and GalleryB

2. set GalleryA Items property: BATAOpenTickets, set GalleryB Items property: LogisticsOpenTickets

3. Replace BATAOpenTickets in the codes by 'GalleryA.AllItems', replace LogisticsOpenTickets by 'GalleryB.AllItems'.

EX:

 

Filter(
If(
isComputing,
GalleryA.AllItems, // modify here
GalleryB.AllItems  //modify here
),
TaskStatus.Value = "NOT STARTED" || TaskStatus.Value = "IN PROGRESS"
),
NamesFilter = "Not Assigned"

 

Please have a try, if this doesn't make sense, please let me know.

Sik

bendd221
Level: Powered On

Re: Delegation issue without a warning, can anyone spot it through this messy code?

@v-siky-msft 
@WarrenBelz 
Thank you both for trying to assist me with this issue!
First of all WarrenBelz, everything you stated is correct except LogisticsOpenTicket
Both BATAopenTickets and LogisticsOpenTickets are my datasources, and Im filtering those based on which button the user pressed on the home screen (Two ticketing systems for two different departments)
Overall, 4 SP Lists are being used, two for each department, one for open tickets and one for closed tickets.

Siky-msft, I'll try implementing your solution and see how it goes.

I have rollbacked my application all the way to the beginning to see if the problem persists, and it does!
Here is a shortened version of the code that still does not work(delegation is not working):

If(
    isAdmin,
    If(isPressed = false,
        If(
            Filter = "NOT COMPLETED",
            Sort(
                If(
                    NamesFilter = "All",
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = "NOT STARTED" || TaskStatus.Value = "IN PROGRESS"
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = "NOT STARTED" && AssignedPerson = "-" || TaskStatus.Value = "IN PROGRESS" && AssignedPerson = "-"
                    ),
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = "NOT STARTED" && AssignedPerson = NamesFilter || TaskStatus.Value = "IN PROGRESS" && AssignedPerson = NamesFilter
                    )
                ),
                Created,
                Descending
            ), Filter = "COMPLETED" || Filter = "NOT RELEVANT - DELETED",
            Sort(If(NamesFilter = "All", Filter(BataCompletedTickets, TaskStatus.Value = Filter), NamesFilter = "Not Assigned", Filter(BataCompletedTickets,                           AssignedPerson ="-"), Filter(BataCompletedTickets, AssignedPerson = NamesFilter && TaskStatus.Value = Filter)), Created, Descending),
            Sort(
                If(
                    NamesFilter = "All",
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = Filter
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = Filter && AssignedPerson = "-" || TaskStatus.Value = Filter && AssignedPerson = "-"
                    ),
                    Filter(
                        BATAOpenTickets,
                        TaskStatus.Value = Filter && AssignedPerson = NamesFilter
                    )
                ),
                Created,
                Descending
            )
        ),
        If(Filter = "COMPLETED" || Filter = "NOT RELEVANT", 
        Sort(Filter(BataCompletedTickets,StartsWith(Title,TextInput1.Text)), Created, Descending),
        Sort(Filter(BATAOpenTickets, StartsWith(Title,TextInput1.Text)), Created, Descending))
    ),
    Sort(
        Filter(
            BATAOpenTickets,
            Author.Email = MyProfile.Mail
        ),
        Created,
        Descending
    )
)


I will try implementing both of your solutions and report back, thank you!

WarrenBelz
Level 8

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Hi @bendd221 ,

Yes - a misread on my part - I was more looking for the variables to make sure they are not controls or field names.

Let me know if you want me to do anything further.

bendd221
Level: Powered On

Re: Delegation issue without a warning, can anyone spot it through this messy code?

Thanks, the delegation warning while using the 'in' operator returned after rolling back, but even removing it did not solve the case yet, ill look into @v-siky-msft  solution and post back results.

The only thing the comes to my mind is that filtering two different sharepoint lists on the same gallery might effecting delegation, but that is only an assumption, ill do my testing and report back.

bendd221
Level: Powered On

Re: Delegation issue without a warning, can anyone spot it through this messy code?

@v-siky-msft 
Okay, so I tried what you said and it works  EDIT:Delegation works, buts its not a viable solution! read below
The problem is, while I wont mind loading the gallery onto an Invisible screen to serve as my datasource, It only took the first 100 items and it didnt load the rest until I manually scrolled all the way down to load everything.

 

I thought about maybe stacking two galleries ontop of each other, moving the code elsewhere and only tweak the .visible property but I wont be able to filter everything, which kinda rules it out.

Any idea?


Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (3,661)