cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Sort Gallery based on calculation - add a condition

Hello everyone,

 

I have this code in my gallery:

 

Sort(
    AddColumns(
        Filter(
            'Stock Management',
            StartsWith(
                Title,
                TextSearchBox1.Text
            ),
            Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All"
        ),
        "SortRank",
        (Stock/Threshold)
    ),
    SortRank,
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

 

 

It puts the lowest value of the division's result of Stock/Threshold, on top which is exactly what I want.

 

I have just one exception I'm trying to make here... Sometimes I put the threshold value at 0, for a product that I don't order anymore... Because I don't care having a low stock alert on it... Hence threshold = 0 :).

 

In Sharepoint, these products are marked as "Retired" in the status  column".

 

I'm trying to achieve the following:

I just want all the products marked as retired (or those whose "threshold" value is at 0), not to appear first in the gallery but to be at the bottom... Like, excluded from the calculation Stock/Threshold...

 

Any ideas? 

 

Thanks guys in advance as always!

Anthony

Right now they are on top since let's say I've got 5 devices of a product that has 0 threshold, it will 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @AnthonyRegnier ,

Note that the ! Not() filter is not delegable, so I will try and avoid this. I have a similar issue with Status filters the the easiest way is to filter on the ones NOT retired. So assuming your other Status values are Pending and Active (these are just examples), you would use

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Title,
            TextSearchBox1.Text
         ) &&
         (
            Description = Dropdown1.Selected.Result || 
            Dropdown1.Selected.Result = "All"
         ) &&
         (
            Status = "Pending" || 
            Status = "Active"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

If you were doing this on a Collection, instead of 

(
   Status = "Pending" || 
   Status = "Active"
)

you could use

Status <> "Retired"

 

 

Spoiler

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

 

View solution in original post

Ok @AnthonyRegnier ,

What you need to do in PowerApps is have a text field in SharePoint that is updated whenever the values forming it are updated. You can hide the card on the form, simply set the Default to a Variable, set this Variable to the value you want and reset the card. So on the OnChange of the relevant controls (change to your value and control name - you will need If statements)

If(
   x=y    //whatever your test
   UpdateContext({vStatus:ValueYouWant})
);
Reset(StatusControlName)

On the Default of this control

If(
   !IsBlank(vStatus),
   vStatus,
   Parent.Default
)

 

 

View solution in original post

15 REPLIES 15
WarrenBelz
Super User
Super User

Hi @AnthonyRegnier ,

Assuming "Retired" is the last alphabetically in the Status list - try

Sort(
   Sort(
      AddColumns(
         Filter(
            'Stock Management',
             StartsWith(
                Title,
                TextSearchBox1.Text
             ),
             Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All"
          ),
         "SortRank",
         (Stock/Threshold)
      ),
      SortRank,
      If(
         SortDescending1,
         Descending,
         Ascending
      )
   ),
   Status,
   Ascending
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

v-xida-msft
Community Support
Community Support

Hi @AnthonyRegnier ,

Based on the needs that you mentioned, I afraid that there is no direct way to achieve your needs in PowerApps currently.

 

As an alternative solution, I think a collection could achieve your needs. You could consider add a Button (called "Filter") within your Gallery screen, set the OnSelect property to following:

ClearCollect(
            FinalCollection,
            Sort(
                 AddColumns(
                          Filter(
                                 'Stock Management',
                                 StartsWith(
                                             Title,
                                             TextSearchBox1.Text
                                  ),
                                   Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All",
                                   Status.Value <> "Retired"   // Add formula here
                           ),
                           "SortRank",
                            Stock/Threshold
                 ),
                 SortRank,
                 If(
                     SortDescending1,
                     Descending,
                     Ascending
                 )
            ),
            Sort(
                   Filter(
                                 'Stock Management',
                                 StartsWith(
                                             Title,
                                             TextSearchBox1.Text
                                  ),
                                   Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All",
                                   Status.Value = "Retired"   // Add formula here
                           ),
                           Threshold,
                           If(
                               SortDescending1,
                               Descending,
                               Ascending
                           )
            )
)

Set the Items property of the Gallery to following:

FinalCollection

After you selected these filter conditions within the Dropdown box or TextSearchBox, then click "Filter" button, then check if the collection data would be shown up in your Gallery.

 

You could also consider set the OnStart property of your App to following to initialize the FinalCollection when you load your app:

ClearCollect(
            FinalCollection,
            Sort(
                 AddColumns(
                          Filter(
                                 'Stock Management',
                                 StartsWith(
                                             Title,
                                             TextSearchBox1.Text
                                  ),
                                   Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All",
                                   Status.Value <> "Retired"   // Add formula here
                           ),
                           "SortRank",
                            Stock/Threshold
                 ),
                 SortRank,
                 If(
                     SortDescending1,
                     Descending,
                     Ascending
                 )
            ),
            Sort(
                   Filter(
                                 'Stock Management',
                                 StartsWith(
                                             Title,
                                             TextSearchBox1.Text
                                  ),
                                   Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All",
                                   Status.Value = "Retired"   // Add formula here
                           ),
                           Threshold,
                           If(
                               SortDescending1,
                               Descending,
                               Ascending
                           )
            )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @AnthonyRegnier ,

A couple of options here - please tag which ever is best for you to continue the thread.

Hello @WarrenBelz ! Thank you for sharing your code! I have tried it but unfortunately it doesn't work, it breaks my sorting from lowest item to highest. It places low items in various places... Maybe a little piece missing in the code?

 

@v-xida-msft , how are you? Thank you, I hope you didn't spend too much of your time here!

I'm trying your solution, I assume you meant the OnVisible property and not the OnSelect right?

I have just an error in your code with Status.Value = "Retired" where it tells my "invalid use of ".", and invalid argument type with the equal sign. I've tried few things like Status = "Retired", or Status.Text = "Retired" etc... But nothing seems to work...

 

Thank you so much guys for helping on that tricky one.

 

Anthony

@WarrenBelz , @v-xida-msft 

 

I'm thinking about something here...

Since these product are retired from my stock, (meaning we still have some of them but we don't order anymore this specific model).

 

How easy would it be to filter them out of the gallery, add a simple button called for example "Retired Products" and when cliking on it, it would take me to the another gallery that only contains them? 

 

Sounds like a good idea and easier to do no?

 

Thanks a lot!

Anthony

Hi @AnthonyRegnier ,

I was wondering how wrapping another sort around your existing code could possibly break something, however to answer your question, that is what I would do. If you do not want or need users to see items, then yes, filter them out.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Indeed @WarrenBelz ...  So, I have duplicated my main screen and called it ArchivedScreen. I will place a button on the MainScreen to access the ArchivedScreen.

 

The OnVisible property of ArchivedScreen remains my custom collection for the dropdown.

I guess the only thing I need to do is filter IN the Retired product and Filter OUT the others from this code (Archived Screen > BrowseGallery1 > Item property)

Sort(
    AddColumns(
        Filter(
            'Stock Management',
            StartsWith(
                Title,
                TextSearchBox1_1.Text
            ),
            Description = Dropdown1_1.Selected.Result || Dropdown1_1.Selected.Result = "All"
        ),
        "SortRank",
        (Stock/Threshold)
    ),
    SortRank,
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

 

 

And Filter OUT only the Retired product from this code (Main Screen > BrowseGallery > Item property)

 

Sort(
    AddColumns(
        Filter(
            'Stock Management',
            StartsWith(
                Title,
                TextSearchBox1.Text
            ),
            Description = Dropdown1.Selected.Result || Dropdown1.Selected.Result = "All"
        ),
        "SortRank",
        (Stock/Threshold)
    ),
    SortRank,
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

 

Do you know what to amend in the formula? Probably simple for you. I will try meanwhile :).

 

Thanks!

Anthony

WarrenBelz
Super User
Super User

Hi @AnthonyRegnier ,

Note that the ! Not() filter is not delegable, so I will try and avoid this. I have a similar issue with Status filters the the easiest way is to filter on the ones NOT retired. So assuming your other Status values are Pending and Active (these are just examples), you would use

Sort(
   AddColumns(
      Filter(
         'Stock Management',
         StartsWith(
            Title,
            TextSearchBox1.Text
         ) &&
         (
            Description = Dropdown1.Selected.Result || 
            Dropdown1.Selected.Result = "All"
         ) &&
         (
            Status = "Pending" || 
            Status = "Active"
         )
      ),
      "SortRank",
      (Stock/Threshold)
   ),
   SortRank,
   If(
      SortDescending1,
      Descending,
      Ascending
   )
)

If you were doing this on a Collection, instead of 

(
   Status = "Pending" || 
   Status = "Active"
)

you could use

Status <> "Retired"

 

 

Spoiler

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

 

You are awesome @WarrenBelz  ! Works like a charm!

Is it normal that I get the warning "Does not work on large data set" with Status= "x" ? 

 

Thank you so much!

Anthony

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,967)