cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Combination of Filter and Sort function does not work

Hey there,

I created a small ticket system. I want to provide a list of tickets that can be sorted as well as flitered regarding status and issue. It doesn't seem to show any results, although both functions SEPERATELY work perfectly fine (tested that).

 I assume the sorting doesn't know how to work with the filtered list altogether.

 

(I used her tutorial fpr the sorting thing https://www.youtube.com/watch?time_continue=8&v=vyBsk7-1Mxk)

 

pa.PNG

 

Filter(Tickets; If(IsBlank(ListBox1_1.Selected.Value); true; 
_crbe6_status_label=ListBox1_1.Selected.Value) && If(IsBlank(ListBox1.Selected.Value); true;
_crbe6_artdesproblems_label=ListBox1.Selected.Value And

SortByColumns(Tickets;SortPriority;If(SortDescending;Ascending;Descending))))

any ideas?

 

Thanks! 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

Hi @Anonymous,

I am happy to help here 🙂

 

It sounds like you are trying to replicate a solution similar to what Laura Rogers did in her video. 

 

Below I've spaced out your formula a little more to understand it:

 

Filter(Tickets; 
	If(IsBlank(ListBox1_1.Selected.Value); true; _crbe6_status_label=ListBox1_1.Selected.Value) && 
	If(IsBlank(ListBox1.Selected.Value); true; _crbe6_artdesproblems_label=ListBox1.Selected.Value 
	And 
SortByColumns(Tickets;SortPriority;If(SortDescending;Ascending;Descending))))

 

From this, it sounds like you want to:

  • Filter the Tickets
  • Ignore a part of the filter if a control is blank
  • Sort the Tickets by the column SortPriority

I've color coded in red the parts that we can change to fix it.

Then in blue, I've coded parts we can change for best practices.

 

The way you wrote your formula helps me understand your thought process better. Semantically, it would make sense to sort after you filter, so I can see why you would place SortByColumns after the Filter formula.

 

The way PowerApps language works is that you read from inside outward. I'll illustrate with the formula as I've corrected it:

 

 

SortByColumns(
	Filter(Tickets; 
		Or(IsBlank(ListBox1_1.Selected.Value); _crbe6_status_label=ListBox1_1.Selected.Value);
		Or(IsBlank(ListBox1.Selected.Value); _crbe6_artdesproblems_label=ListBox1.Selected.Value)
	);
"SortPriority";If(SortDescending;Ascending;Descending)
)

 

Start inside with Filter()

This means,

  • Filter the Tickets to show records where the status label matches ListBox1_1 and artdesproblems matches ListBox1.
  • If either ListBox is blank for each condition, show everything for that condition in the filter (return true).
  • Outside: Take the result of that Filter and sort it by the column SortPriority.
  • If the SortDescending variable is true, sort ascending, else descending.

So you can think of this as an order of operations. SortByColumns needs a table in its first argument. You do not want to sort Tickets--you want to sort a Filter() on Tickets, so the Filter needs to go inside of SortByColumns.

 

Other notes:

  • Each column that you would like to sort by needs to be wrapped in quotes for SortByColumns. The Sort() function does not need quotes
  • SortByColumns can sort by multiple columns as well if you're interested in that

Let me know if this helps.

 

Mr. Dang

 

____________________

Microsoft Employee
@8bitclassroom

View solution in original post

2 REPLIES 2
Community Champion
Community Champion

Hi @Anonymous,

I am happy to help here 🙂

 

It sounds like you are trying to replicate a solution similar to what Laura Rogers did in her video. 

 

Below I've spaced out your formula a little more to understand it:

 

Filter(Tickets; 
	If(IsBlank(ListBox1_1.Selected.Value); true; _crbe6_status_label=ListBox1_1.Selected.Value) && 
	If(IsBlank(ListBox1.Selected.Value); true; _crbe6_artdesproblems_label=ListBox1.Selected.Value 
	And 
SortByColumns(Tickets;SortPriority;If(SortDescending;Ascending;Descending))))

 

From this, it sounds like you want to:

  • Filter the Tickets
  • Ignore a part of the filter if a control is blank
  • Sort the Tickets by the column SortPriority

I've color coded in red the parts that we can change to fix it.

Then in blue, I've coded parts we can change for best practices.

 

The way you wrote your formula helps me understand your thought process better. Semantically, it would make sense to sort after you filter, so I can see why you would place SortByColumns after the Filter formula.

 

The way PowerApps language works is that you read from inside outward. I'll illustrate with the formula as I've corrected it:

 

 

SortByColumns(
	Filter(Tickets; 
		Or(IsBlank(ListBox1_1.Selected.Value); _crbe6_status_label=ListBox1_1.Selected.Value);
		Or(IsBlank(ListBox1.Selected.Value); _crbe6_artdesproblems_label=ListBox1.Selected.Value)
	);
"SortPriority";If(SortDescending;Ascending;Descending)
)

 

Start inside with Filter()

This means,

  • Filter the Tickets to show records where the status label matches ListBox1_1 and artdesproblems matches ListBox1.
  • If either ListBox is blank for each condition, show everything for that condition in the filter (return true).
  • Outside: Take the result of that Filter and sort it by the column SortPriority.
  • If the SortDescending variable is true, sort ascending, else descending.

So you can think of this as an order of operations. SortByColumns needs a table in its first argument. You do not want to sort Tickets--you want to sort a Filter() on Tickets, so the Filter needs to go inside of SortByColumns.

 

Other notes:

  • Each column that you would like to sort by needs to be wrapped in quotes for SortByColumns. The Sort() function does not need quotes
  • SortByColumns can sort by multiple columns as well if you're interested in that

Let me know if this helps.

 

Mr. Dang

 

____________________

Microsoft Employee
@8bitclassroom

View solution in original post

This really helped me a lot. I was trying to filter based on 2 tables from a MySQL data source and I kept getting an error. I only just figured out that the sort function was causing the error, but by using SortByColumns, I was able to have my multiple cross table filters and sort functions work together. Thanks!

 

I'll include my code for others to reference:

 

SortByColumns(
Filter(
    colComp,
    StartsWith(name,computerSearchBox.Text)
    ||StartsWith(otherserial,computerSearchBox.Text)
    ||StartsWith(LookUp(colUsers,users_id = id,firstname),computerSearchBox.Text)
    ||StartsWith(LookUp(colUsers,users_id = id,realname),computerSearchBox.Text)
),
"name",
If(SortDescending1,Ascending,Descending))

This filters a gallery based on collections made when the application starts. The first two filters are in the gallery's linked data source while the second two filter based on the second collection made from a separate table, and now it's sorted too.

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (52,166)