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

Combine SortByColumns with hard-coded Filter

Hi - I'm building an App (first time - please be gentle, and apologies in advance if I use incorrect terminology). We have a SharePoint list with ingredient data on it. We have several different customers who will enter their own ingredient details into our list via the App. Customers have all been set up as external (guest) users, and have a separate SharePoint site which is connected to our Hub. Customers will access their shared site to open and use the the App. The shared sites have been in use for several months.

Each customer must only see and edit their own ingredients, so I will create separate apps for each customer, and load onto their separate Shared sites, but all linking to the same datasouce, i.e. our List 'testRaw Materials List'.

 

Therefore I'm writing a Spring Sheep App. I want to hard-code "Spring Sheep" into the galleries, so they only show Spring Sheep ingredients. I have written a filter to only display Spring's ingredients, which works fine. 

  • Filter('testRaw Materials List', Customer.Value = "Spring Sheep")

I want to add a sort button to the gallery, to sort alphabetically. I have a context variable on the button which works fine OnSelect:  

  • UpdateContext({SortDescending1: !SortDescending1})

I have written a SortByColumns based on the whole list (all ingredients) which works fine:

  • SortByColumns( 'testRaw Materials List', "Title",If(SortDescending1, Descending, Ascending))

But I cannot for the life of me work out how to combine these 3 elements so that a) only Spring Sheep ingredients show, and b) the sort button context variable works with c) the SortByColumns. I can't even get this to work without the button, just sorting by ascending: 

  • SortByColumns(Filter('testRaw Materials List',Customer.Value="Spring Sheep",Ascending))

Then, the next thing I want to do is add a text input box (TxtIngFilter), so the customer can search for part of the ingredient name. I got this to work on another screen which has all ingredients and I have a combo box (cbCustomerFilter) of customer name and a text input:

  • Search(Filter('testRaw Materials List',IsBlank(cbCustomerFilter.Selected.Value) || IsEmpty(cbCustomerFilter.SelectedItems.Value) || cbCustomerFilter.Selected.Value=Customer.Value),TxtIngFilter.Text,"Title")

How can I combine these arguments where the customer is hard-coded?

 

In the screenshot below, the "false" is the context variable toggling as I push the sort button:

 

GraemeNZ_2-1635893558101.png

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @WarrenBelz. Since I have hard-coded the filter to Spring Sheep, the combo box cbCustomerFilter is deleted, so I've removed this line. 

 

Net result is that only Spring Sheep ingredients show (good); columns aren't sorted (bad); sort button doesn't do anything (bad); clicking sort button changes the toggle true/false (good).

GraemeNZ_1-1636085716578.png

When I looked at the previous function that worked (except for the toggle button), it used SortByColumns instead of Sort, so I made this change and Voila! Everything works!

 

Thanks for your help, GraemeNZ

 

 

 

 

 

View solution in original post

Hi - sorry, this is the formula I used on the Gallery Items

GraemeNZ_0-1636322091329.png

This is the formula I used for the Sort button OnSelect

      UpdateContext({SortDescending1: !SortDescending1})

 

Together, it means my gallery only shows Spring Sheep ingredients, they are sorted alphabetically according to Title, and clicking the sort button changes A-Z / Z-A. In addition, I have a text input box and I can do a search on ingredient name.

GraemeNZ_1-1636322368491.png

 

 

View solution in original post

18 REPLIES 18
WarrenBelz
Super User
Super User

Hi @GraemeNZ ,

Try this

Search(
   SortByColumns(
      Filter(
         'testRaw Materials List',
         Len(cbCustomerFilter.Selected.Value) = 0  ||
         Customer.Value = vbCustomerFilter.Selected.Value
      ),
     "Title"
   ),
   TxtIngFilter.Text,
   "Title"
)

 

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.

Visit my blog Practical Power Apps

Hi Warren - I don't want to use the combo box. If the combo box is visible, a Spring user can use it to see another customers' ingredients. So I want the filter to be hard-coded.

@WarrenBelz This is what I get when I try and replace the combo box functions with my otherwise proven filter:

GraemeNZ_0-1635896664292.png

When I combine it this way, PowerApps says there's an error with my filter formula, but when I do the exact same thing standalone it works fine.

Hi @GraemeNZ ,

Going right back to basics here - what does this do and please confirm Customer is a Choice column.

Filter(
   'testRaw Materials List',
    Customer.Value = "Spring Sheep"
)

Also is this a Canvas app or SharePoint Integrated?

 

Hi - thanks for your reply.

 

Customer in SharePoint is a Choice column, and "Spring Sheep" is one of the choices:

GraemeNZ_0-1635898599863.png

In the test data, there are 4 Spring ingredients added:

GraemeNZ_1-1635898681362.png

In PowerApps, on the main data entry screen, I successfully use the filter command to only show Spring Sheep ingredients:

GraemeNZ_2-1635898744050.png

You can see that the 4 Spring ingredients aren't sorted alphabetically, which is why I want to add a sort button. The summary screen I shared in the earlier post will also have the sort button and also the text input. (The real list is likely to have 40-50 ingredients.)

 

Cheers, Graeme

 

 

 

Sorry @WarrenBelz I forgot to ping you...

GraemeNZ
Advocate II
Advocate II

Canvas app

Hi @GraemeNZ ,

You have a comma after "Title" (the first one) that is not needed. Also I need to note on the post that I assume all your customers have Office365 Licences or have been included in your Tenant.

 

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.

Visit my blog Practical Power Apps

Hi @WarrenBelz - Ah, the extra comma error. Thanks. That works for the hard-code filter. Now how do I combine this with the Sort button toggle?

 

In your suggestion earlier, you use Len - is this just a tidier way of combining IsBlank || IsEmpty?

 

And yes, all customers this has been rolled to have O365 licences.

Helpful resources

Announcements
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.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,345)