Filtering and sorting a collection - How does it work together?

[PowerSäm]

Hello All,

I have created a gallery in my app with applications that are filtered by status. So if you click on "Genehmigt" only approved applications are displayed and for the other buttons it's the same principle (See images below). That works fine so far!

But now I want to sort by the title as well as filtering, how do I do that? Surely with the SortofColumns function, but how?

Thanks in advance

[timl]

Hi @PowerSäm 

You would wrap the call to SortByColumns over the call to Filter. Therefore, the formula would look like this:

SortByColumns( 
    Filter(colUrlaubReise;
           ......
    );
    "FieldToSortBy";
    SortOrder.Ascending
)

[PowerSäm]

Thank you for your quick reply, @timl 

I would now like to sort by status. This is also suggested to me but when I select it, the following error message appears: "Sorting by print type is not possible."

What could be the reason for this? If I sort it by Title or Country, it works. In the collection, the column "Status" is displayed as follows (see image below)

What can be the reason for this and how can I bring it to work?
Best regards

[timl]

Hi @PowerSäm 

I guess status is a lookup column?

If so, the answer would be to extract the status text into a new column by calling the AddColumns function.
This formula adds a column called StatusText. Where Status.Value appears, you would replace this by using IntelliSense to select the correct field name for your data.

SortByColumns( 
    Filter(AddColumns(
               colUrlaubReise;
               "StatusText";
               Status.Value
           );
           ......
    );
    "StatusText";
    SortOrder.Ascending
)

[PowerSäm]

Thanks for your reply, @timl 

Yes that works so far, but now it doesn't sort it the way I thought it would. It sorts even if I click on "Alle". But I want this to sort by status ONLY when I click on the "Nach 'Status" button, when clicking on the other buttons it doesn't need to be sorted. How can I change this? I have to enter somewhere that this doesn't always happen but only when I click on the "Nach Status" button, right? But how? (See pictures)

The code:

The Button I mean:

Thanks so much in advance!

[timl · Accepted solution]

Hi @PowerSäm 

To accomplish this, you can set a variable on the click of the "Nach Status" button.

UpdateContext({locSortbyStatus:true})

This sets the value of the locSortbyStatus variable to true. You would need to set this to false on the click of the other buttons.

You can then add an If condition to your formula so that it only calls SortByColumns when locSortbyStatus is true.

If(locSortbyStatus; 
   SortByColumns( 
       Filter(AddColumns(
                  colUrlaubReise;
                  "StatusText";
                  Status.Value
              );
              ......
       );
      "StatusText";
      SortOrder.Ascending
   );
   Filter(colUrlaubReise;......)
)

[PowerSäm]

Thank you very much, @timl, that worked so far, many thanks!

One last little thing... Now I would like to set up a similar condition for another button.

I would like, that if one clicks on the button "Nach Kategorie", the gallery sorts itself by category. I have it already written down.

My questions:

1.) How do I connect two if-queries? Only with &&? (it does not recognize it, see picture)

2.) Is the principle the same as sorting by status? Can I leave it like this? It doesn't sort me like that, can it be because it doesn't work with the "category" unit? What am I doing wrong?

My code for the "Category"-Button:

The button I mean:

The variable behind the Button is: UpdateContext({varSortbyCategory:true})

Thanks in advance!

[timl · Accepted solution]

Hi @PowerSäm 

In answer to the first question, I think the easier way to accomplish this is to store the name of the column that you want to sort by in a variable, rather than a different variable for each column.

//To sort by category
UpdateContext({varSortColumn:"Category"})

//To sort by status
UpdateContext({varSortColumn:"Status"})

You can then call the Switch function to specify the course calls to Filter/SortByColumns:

Switch(varSortColumn;
     "Status"; <SortByColumns/Filter Status Records>;
     "Category"; <SortByColumns/Filter Column Records>;
     <Return non filtered collection here>
)

In answer to the second question, if category is also a look up column, you would use the same principle as sorting by status. If you want to match those records where category text equals "Urlaub" or "Dienstreise", you would use the Or/|| operator, rather than specify ["Urlaub";"Dienstreise"]

Filter(AddColumns(....),
      CategoryText = "Urlaub" || CategoryText = "Dienstreise"
)

[PowerSäm]

Thanks a lot for your help, @timl , everything worked great! Tanky you very much!