Sort and Filter Assistance

[Tango]

Hi,

I am trying to follow along on this video where there is a need to get around delegation. My Sharepoint list has a Title, Account Type (Choice column) & Account Rating (Choice Column). I

My function was working fine before I tried to implement the Switch to address delegation.

Switch(
    varAccountsBrowseSortColumn,
    "Title",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
    ),
    "Title",
    If( varAccountsBrowseSortColumn = Ascending, Ascending,Descending)),
    "Account Rating"
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
    ),
    "Account Rating",
    If( varAccountsBrowseSortColumn = Ascending, Ascending,Descending)),
    "Account Type",
SortByColumns(
    Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
    ),
    "Account Type",
    If( varAccountsBrowseSortColumn = Ascending, Ascending,Descending))
)

Thanks in advance for any assistance.

Cheers

Todd

[BCBuizer]

Hi @Tango ,

What is it that is not working? From a syntax perspective all seems correct...

[Tango]

Thanks for your message,

I just have squiggly lines all over the place and a little icon that says there is an error

[BCBuizer]

Hi @Tango ,

If you click on the App Checker, the stethoscope icon on the top right, you can find more details on the error:

Can you please share these details?

[Tango]

There is 15 errors

Expected Operator. We expect an operator such as +,* or & at this point in the formula.

Unexpected Characters. The formula contains 'ParenClose' where 'Comma' is expected

Unexpected Characters. Characters are used in the formula in an expected way

I think these are then repeated as extra switch blocks are added

[Tango]

Here is the latest code

The Title block of the Switch is fine. No Squigglys. I am getting squigglys at the Sort By columns section under Account Rating"  when hovering i get message "The function 'SortByColumns' has some invalid arguments

Switch(
    varAccountsBrowseSortColumn,
    "Title",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Title",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    ),
    "Account Rating",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Account Rating",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    ),
    "Account Type",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Account Type",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    )
)

[Tango]

just worked this out i think.

it was because I was trying to sort non delegable fields. Account Type & Account Rating are Choice fields

[BCBuizer]

HI @Tango ,

The below code should fix the errors, but you'll still be left with delegation issues, depending on the size of your data source:

Switch(
    varAccountsBrowseSortColumn,
    "Title",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Title",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    ),
    "Account Rating",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Account Rating.Value",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    ),
    "Account Type",
    SortByColumns(
        Filter(
        'Accounts',
        StartsWith(
            Title,
            txtinpAccountsSearch.Text
        ) && ('Account Rating'.Value = drpAccountRating.Selected.Value ||  drpAccountRating.Selected.Value=Blank()) && ('Account Type'.Value = drpAccountType.Selected.Value ||  drpAccountType.Selected.Value=Blank())
        ),
        "Account Type.Value",
        If(
            varAccountsBrowseSortColumn = Ascending,
            Ascending,
            Descending
        )
    )
)