Combine SortByColumns with hard-coded Filter

[GraemeNZ]

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:

[WarrenBelz]

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

[GraemeNZ]

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.

[GraemeNZ]

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

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.

[WarrenBelz]

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?

[GraemeNZ]

Hi - thanks for your reply.

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

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

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

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

[GraemeNZ]

Sorry @WarrenBelz I forgot to ping you...

[GraemeNZ]

Canvas app

[WarrenBelz]

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

[GraemeNZ]

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.