cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tango
Helper III
Helper III

Sort and Filter Assistance

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

7 REPLIES 7
BCBuizer
Super User
Super User

Hi @Tango ,

 

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

Thanks for your message,

 

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

BCBuizer
Super User
Super User

Hi @Tango ,

 

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

 

BCBuizer_0-1653480340999.png

Can you please share these details?

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
Helper III
Helper III

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
Helper III
Helper III

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
Super User
Super User

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

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