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

[Anonymous]

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

[Anonymous]

Anyone? 😞

[WarrenBelz]

Hi @Anonymous ,

I will tag one of my code unravelling colleagues @Anonymous to see if he can spot it.

[v-siky-msft · Accepted solution]

Hi @Anonymous ,

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.

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

Sik

[Anonymous]

Interesting, so is there anyway to sort by date without the Sort function and also keep it delegable?
If not, then ill remove every sort condition from the completed part, so its available but not sorted.
Open tickets will never reach 500 so im fine with that.

Quick question though, does having a small part of the gallery's code not delegable will make every part of it not delegable?
for example, if Filter = completed then show the CompletedTickets list but without the sort functions // Should be delegable
Else, do the same to OpenTickets but with the sort functions // Wont be delegable.

Thanks alot for the help!
Ill report back with results

[Anonymous]

@Anonymous notwithstanding the good work that @WarrenBelz and @v-siky-msft have already put into this problem I think the issue is with all the If()'s inside the Filter()'s. This delegation doc link seems to say that If() cannot be used inside Filter or LookUp and this would seem to be the case when I test it.

Using @v-siky-msft process of reducing the limit to 5 records this formula returned all relevant records:

Sort( Filter(
             mySPList1,  
             SiteName = "Site A"
            ),
      Created,
      Descending
)

whereas this formula only returned 5 records

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

There were no blue lines just the above outcomes. It appears you will need to look at other ways to do what you need if you need to return a lot of records?

One suggestion I could make would be to collate all the data into one large SP List adding extra columns that match your filtering needs and filter on those columns instead eg DeptName = BATAOpenTickets OR LogisticsOpenTickets

[Anonymous]

First of all thanks for replying!
If you check my latest reply with code attached in it you will see that i rollbacked the applicartion to a point in time where there was no two databases and there were no ifs inside the filter function, so I automatically ruled that one out.

Is it possible that both the If operator and the Sort function prevent delegation?

EDIT:I removed the sort function from a certain part and all the ifs from inside the filters yet it still did not work
The part in the code:

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",

                If(
                    NamesFilter = "All",
                    Filter(
BataCompletedTickets,
                        TaskStatus.Value = Filter
                    ),
                    NamesFilter = "Not Assigned",
                    Filter(
BataCompletedTickets,
                        AssignedPerson = "-"
                    ),
                    Filter(
BataCompletedTickets,
                        AssignedPerson = NamesFilter && TaskStatus.Value = Filter
                    )
                ),

This is only part of the code, but it serves as an example
As you can see, I took the the If statement and the Sort statement yet it still shows only 5 records (changed delegation to 5 as you told, makes it way easier, thanks!)

I wonder if one part is not delegable, EVERYTHING is not delegable and then I wont be able to use the same gallery for both delegable functions and non delegable functions..

[Anonymous · Accepted solution]

@Anonymous 

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?

[Anonymous]

Incredible, it works!
I was so happy I shouted a bit over here in the office 😅
The problem was the Sort function, and the solution Eelman proposed fixed it.

Thank you all for helping, highly appreciated!
@Anonymous 
@v-siky-msft 
@WarrenBelz 
Have a lovely week.

EDIT: fyi, the If ruins delegation too, so Im looking for ways to fix that in a different way, maybe use a variable that called selectedDataBase and every time the database gets refreshed, reset it.
I will have to test how slow it makes the app, but for now I found the issues.

[Anonymous]

@Anonymous 

Happy it worked out for you and nice to hear you gave a cheer, haha. I had a little fist pump followed by a "Yeeessssss" here - that's what it's all about, right? lol

Have a great week mate.