cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndrewValenzuel
Advocate II
Advocate II

Where do I put the OR statement? SortByColumn with an Or statement and a sort acending/descending function.

The Code: 

SortByColumns(Filter(Tickets,StartsWith(Status,If(ddFilter.Selected.Value="All", "In Progress" ,ddFilter.Selected.Value))), "Title", If(SortDescending1,Descending,Ascending))

 

I have a gallery that lists tickets.  There's a dropdown that sorts the gallery by Status.  I would like the default view to list both tickets with a status of "open" and "in progress".  (the other selectable values are "closed" & "canceled".)

 

I want to change the code to something like this:

SortByColumns(Filter(Tickets,StartsWith(Status,If(ddFilter.Selected.Value="All", "In Progress" || "Open" ,ddFilter.Selected.Value))), "Title", If(SortDescending1,Descending,Ascending))

 

I don't know where to put the Or function in this code.  

 

To note, I have a workaround but it gives a delegation warning.  This is a sharepoint list.

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @AndrewValenzuel ,

 

To avoid delegation, put If function out of the sortbycolumn function, first to judge if the dropdown selected value is 'All'.

Please try this formula.

If(ddFilter.Selected.Value="All",SortByColumns(Filter(Tickets,Status.Value="In Progress" Or Status.Value="Open"), "Title",If(SortDescending1,Descending,Ascending)),SortByColumns(Filter(Tickets,Status.Value=ddFilter.Selected.Value), "Title",If(SortDescending1,Descending,Ascending)))

 Sik

View solution in original post

4 REPLIES 4
DeepakS
Super User
Super User

Andrew,

have you tried  something like this:

If(ddFilter.Selected.Value="In Progress" || ddFilter.Selected.Value="Open" ,ddFilter.Selected.Value....

 

 

mdevaney
Super User III
Super User III

@AndrewValenzuel 

Using an IF statement inside of a FILTER function will not allow it to be delegated.  Therefore we must try to remove the IF.

 

My understanding of your post is you would like to see "In Progess" or "Open" tickets when "All" is selected in the dropdown.  When another value is chosen in the dropdown you would only like to see that option ("In Progress","Open","Closed","Cancelled").

 

Here's the code I would try to use in your Gallery:

SortByColumns(
    Filter(
        Tickets,
        (ddFilter.Selected.Value = "All" And (Status = "In Progress" Or Status = "Open")) Or Status = ddFilter.Selected.Value
    ),
    "Title",
    If(SortDescending1,Descending,Ascending)
)

 

Only certain functions can be delegated in SharePoint.  The official documentation can be somewhat difficult to understand since it targets SharePoint, CDS and SQL all in the same article.  Here's something you might like better.

 

Link To SharePoint Delegation Article:

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

v-siky-msft
Community Support
Community Support

Hi @AndrewValenzuel ,

 

To avoid delegation, put If function out of the sortbycolumn function, first to judge if the dropdown selected value is 'All'.

Please try this formula.

If(ddFilter.Selected.Value="All",SortByColumns(Filter(Tickets,Status.Value="In Progress" Or Status.Value="Open"), "Title",If(SortDescending1,Descending,Ascending)),SortByColumns(Filter(Tickets,Status.Value=ddFilter.Selected.Value), "Title",If(SortDescending1,Descending,Ascending)))

 Sik

View solution in original post

This worked with one caveat.  I had to remove the ".value" from "Status."

 

If(ddFilter.Selected.Value="All",SortByColumns(Filter(Tickets,Status="In Progress" Or Status="Open"), "ID",If(SortDescending1,Ascending,Descending)),SortByColumns(Filter(Tickets,Status=ddFilter.Selected.Value), "ID",If(SortDescending1,Ascending,Descending)))

 

Could you explain this to me; it's been a huge source of confusion:

I see this alot in code.  And I've not been able to find the proper documentation that explains it.  What are the proper Sub-Functions (I don't even know the actual name) to use for each item type? 

 

For example, I made a Cascading dropdown list wherein the first DD was a ComboBox.  To get the next DD's value to depend on the first DD I had to use the following code: 

Filter(Machines2, Customer_Name = cbCustomerName_1.Selected.Result

 

The bold part of that code is the part that I don't understand.  It took me forever to figure it out.  Every other tutorial was say that I needed to use .Value, instead of .Result.

 

In this problem you helped me with "Status.Value" would not work but "Status" did work.  "Status" is the column name in my SP list.  

 

Do you know of documentation that explains this?  What sub-function to use for which type of data.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,007)