Issue with the deligation error

[Naresh]

Hi,

I m facing a delegation error in the app.

When I was filtering the gallery it gives a delegation error

I m using the If condition inside the filter --- I hope this would be the issue.

Please assist me to get out of the issue.

SortByColumns(
        Filter(
            'Productapproval',
            (If(
                GalleryFilter = "Complex",
                (FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
                GalleryFilter in FirstApproval.Value && GalleryFilter in SecondApproval.Value
            ) && (Author.Email = Office365Users.MyProfile().Mail))
        ),
        "Modified",
        If(
            SortDescending1,
            Ascending,
            Descending
        )
    )

[Naresh]

Hi @tchin-nin @v-monli-msft @v-micsh-msft @v-xida-msft

Please assist me if there is any workaround for it thank you

[tchin-nin]

Hi @Naresh,

There are some non delegable fucntions in your formula. But the delegation warning is not an error, it is a warning, that's mean Powerapps just warn you that your formula might not work if your 'Productapproval' list contains more than 500 (up to 2000) items. If your list has less items than this, you would still have the delegation warning but your formula will work as expected.

Your delegation issues:

1 - The If function can not be delegated, so you'll need to use a bit a logic to perform the same condition in your filter without using a If function. Also in the SortByColumns you'll have to remove your If.

2 - You are using the result of a function : Office365Users.MyProfile().Mail in your filter.This is not permetted because your data source does not know what is this function (it is a powerapps function). You can only use constant values and global or context variables.

3 - The "in" operator is not delegable to SharePoint according to this article

How to warkaround :

Remove If in the SortByColumn : Use a variable with the value Descending or Ascending :

SortButton.OnSelect : if(sortModified=SortOrder.Descending,SortOrder.Ascending,SortOrder.Descending)

Gallery.Items : SortByColumns(<datasource>,"Modified",sortModified)

Now there is no If function but just a global variable, this can be delegated.

Remove the call to Office365Users connector : Use a variable with the current user informations :

Screen.OnVisible : Set(currentUser,User()) //if you just need the mail or fullname picture

OR Screen.OnVisible : Set(currentUser,Office365Users.MyProfile()) //if you need more information like jobTitle or department

Gallery.Items : Filter(<datasource>,Author.Email=currentUser.Email) //or currentUser.mail if you used Office365Users.MyProfile()

Remove If to peform conditionnal filtering : use logic.

Let say you have a data source <dc> with C1 column that have X or Y value and a variable A true or false. You want to filter like :If A is true, filter C1=X but if A is false, filter C1=Y

Filter(<dc>,(A && C1=X) || (!A && C1=Y))

So to have your exact forumla I'll need to know the value of GalleryFilter when it is not "Complex". I'll assumed that it is "Simple".

Gallery.Items : Filter(<dc>,(GalleryFilter="Complex" && Condition1 && Condition2) || (GalleryFilter="Simple" && Condition1 && Condition2))

// Impossible to use GalleryFilter <> "Complex" because only the "=" operator is delegable to SharePoint. <>,<=,>=,<,> are not.

Remove the in operator : No way to perform that in a SharePoint list with delegation. But according to what I understand of your formula, your GalleryFilter is a variable. So maybe you can make sure the value of GalleryFilter variable EQUAL your approvals value so you can use an = operator that is delegable to SharePoint.

Your global formula should look like : 

SortByColumns(
        Filter(
            'Productapproval',
            ((GalleryFilter = "Complex" && FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
            || (GalleryFilter="Simple" && FirstApproval.Value = GalleryFilter && SecondApproval.Value = GalleryFilter))
            && (Author.Email = currentUser.Email))
        ),
        "Modified",
        sortModified
    )

Once again, you'll face delegation issues if your datasource exceed 500 (up to 2000) items. So for example If you know that an user won't have authored more than 500 items, you wan perform this filter first to provide a datasource with less than the delegation limit.:

SortByColumns(
        Filter(
            Filter('Productapproval',Author.Email = currentUser.Email),
            (If(
                GalleryFilter = "Complex",
                (FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
                GalleryFilter in FirstApproval.Value && GalleryFilter in SecondApproval.Value
            ))
        ),
        "Modified",
        If(
            SortDescending1,
            Ascending,
            Descending
        )
    )

Your Delegation warning won't go away but the limit will never be reached so no problem (as long as an User have not authored more than 500 items).

Hope it help understand how Delegation works.

Théo

[Naresh]

I have the admin dashboard as well which doesn't have the below condition

(Author.Email = currentUser.Email)

 In this case, the admin will be listed with all the items which will increase the limit to more than 500. How can I come across this now?

[tchin-nin]

So do you have a variable specifying if the current user is an admin or not ?

Let say isAdmin:true/false, you can use a OR operator to perform that kind of logic :

SortByColumns(
        Filter(
            'Productapproval',
            ((GalleryFilter = "Complex" && FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
            || (GalleryFilter="Simple" && FirstApproval.Value = GalleryFilter && SecondApproval.Value = GalleryFilter))
            && (isAdmin || Author.Email = currentUser.Email))
        ),
        "Modified",
        sortModified
    )

Théo

[Naresh]

This formula didn't help me out

I m using the below formula

SortByColumns(
        Filter(
            Filter('Productapproval',Author.Email = currentUser.Email),
            (If(
                GalleryFilter = "Complex",
                (FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
                GalleryFilter in FirstApproval.Value && GalleryFilter in SecondApproval.Value
            ))
        ),
        "Modified",
        If(
            SortDescending1,
            Ascending,
            Descending
        )
    )

But I don't understand how to apply for the admin view

The formula I m using on the gallery is

gallery1.items

if(check the admin

,

above formula without the condition

Author.Email = currentUser.Email)

,

SortByColumns(
        Filter(
            Filter('Productapproval',Author.Email = currentUser.Email),
            (If(
                GalleryFilter = "Complex",
                (FirstApprval.Value = "Approved" && secondApproval.Value = "Pending") || (FirstApproval.Value = "Rejected" && SecondApproval.Value = "Pending") || (FirstApproval.Value = "Approved" && SecondApproval.Value = "Rejected"),
                GalleryFilter in FirstApproval.Value && GalleryFilter in SecondApproval.Value
            ))
        ),
        "Modified",
        If(
            SortDescending1,
            Ascending,
            Descending
        )
    )

 )

[tchin-nin]

Well the problem you'll have is that your admin view needs to retrieve more than 500 items. So without using filters options delegable to SharePoint you'll face an issue.

Otherwise you need to find a way to make sure even the admin view has no more than 500 items, by forcing filtering, for example by year and/or by any business data your items have.

Have you tried the formula that should avoid any delegation issue and keep your filter as they were ? 

Théo

[Naresh]

Hi @tchin-nin

yes, exactly the issue is with the admin filtering.

Tried all the ways I can. But I couldn't come out with the proper solution.

There is no chance for the forcing filtering. It should filter all the data.

Please assist me if you have any ideas.

[Naresh]

Hi @tchin-nin

Did you find any solution to restrict the delation issue for the admin view?

Thanks in  advance