Filter a gallery based on combo box and text input

[GraemeNZ]

Hi. I have been learning Power Apps through some really good help topics and videos, with some directed help through this forum. Special callouts to @RezaDorrani , @WonderLaura and @vwyankee. vwyankee really helped me with how to structure my formula so that if the current user is on the Approver's list then they can see all items, and if the current user is on a Customer list then the gallery only shows their own items.

Now I want to add search inputs. Reza's and Laura's helpful videos were great with building my understanding, but I'm struggling with the syntax to join these new search parameters with the existing code for Approvers or Customers. I'm not sure if I've applied the formulas in the wrong order, or if I have a bracket or comma in the wrong place...

My screen looks like this:

I got the sort toggle working, and I also got the text input on ingredient name working, but I got unstuck when I tried to add in the customer combo box (this pulls from a Choice column in SharePoint). The latest insertion I made is highlighted in yellow. The red line indicates the error underline that PowerApps is giving me.

(The green highlight is the context variable from @WonderLaura which I can use for sorting different columns, but that wasn't working for Customer - is this because Customer is a choice field?)

I would also like to include that if the combo box is empty, the gallery shows all values that the user would be able to see, i.e. if Approver, then all items, or if Customer then all the items that customer can see.

Can anyone help me achieve this? Regards, GraemeNZ

[WarrenBelz]

Hi @GraemeNZ ,

Try the below - I think it is what you are after (also watch the spelling as I OCRd it - can you please remember to post in Text also)

SortByColumns(
   Search(
      Filter(
         'testRaw Materials List',
         (
            gvCurrentUser = "FIW Approver" ||
            Customer.Value = gvCustomer
         ) ||
         Customer.Value = cbCustFilter.Selected.Value
      ),
      TxtImgFilter.Text,
      "Title"
   ),
   "Title"
   If(
      SortDescending,
      Ascending,
      Descending
   )
)

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

[vwyankee]

@WarrenBelz beat me to it, but I'm not sure that is splitting the results based on the logged in user as it was before. You can also look at just using a compounded filter like in this thread instead of using search and filter.

Also, when you go back to Laura's instructions on the sort you'll see there are no complex columns. You can pull everything into a collection and use the "AddColumns" operator to create a new text value column from your complex columns (like choice columns) and then use the sort against that new collection column.

Also, as mentioned by WarrenBelz, it's helpful if you add your code to the post using the "insert/edit code sample" option in the ribbon. Much easier to grab that way if we need to throw it in one of our test apps. 😊

[GraemeNZ]

Hi @vwyankee and @WarrenBelz . Yes, I was also concerned that Warren's suggestion was going to change the result from which user is logged in.

I'll have a go at that compounded filter, I think this is where I was trying to go. Can I do AND and OR in this way so that it will filter on one value, or the other value or both values?
I got a bit overwhelmed with Laura's solution of pulling values into a collection, but maybe I need to persevere with that after all

Cheers, GraemeNZ

[WarrenBelz]

Hi @GraemeNZ ,

I was only combing your filters and did not really look at your logic, however this bit

gvCurrentUser = "FIW Approver" ||
Customer.Value = gvCustomer

I would have thought would either show all records if the Variable gvCurrentUser equals "FIW Approver" and if not filter the records where the Choice column Customer equals the Variable gvCustomer. I do not know your structure, so this may not be what you need.

[GraemeNZ]

Hi @WarrenBelz - yes there are lots of things going on with my app, and vwyankee has been helping with some of the earlier elements.

If you are interested in the back story (and I'd appreciate any help I can get), please refer to https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-set-a-pre-populated-default-value-of-... - page 5 through to page 7 is mostly about me 🤔

[vwyankee]

Collections can be confusing at first, but are way more flexible with manipulating the data since you then have it all virtually accessible in the app. If you have any columns you want to use the sort with that are not just text columns (in SP) you'll be better off jumping onto the collection train. 

I'm going to be out of pocket pretty much the remainder of the week... otherwise, I'd be getting some examples back to you here soon.

You can use AND and OR in various combinations. The structure of the solution in that other thread I linked shows that. The parenthesis are breaking it out. From that thread, this is saying "any of the blue conditions AND any of the green conditions":

I don't know if it was mentioned in one of your threads but "And" is the same as "&&", and "Or" is the same as "||" in the formulas. Hope that helps.

[GraemeNZ]

Thanks @vwyankee . Something urgent has come up at work myself, so I won't be able to get back to this for a few days (or maybe not until January).

Yes I knew about && and || thanks. 

Thanks also for the encouragement to persevere with collections - I'll gird my loins and fortify my bloodstream with caffeine, and learn how to do this when I get back to this app.

[GraemeNZ]

I was able to 'phone a friend' - our friendly IT professional. He was able to get everything working except the toggle sort button by doing this:

//If current user is approver show all
If(gvCurrentUser = "FIW Approver",
SortByColumns(Search(If(IsBlank(cbCustFilter.Selected.Value),'testRaw Materials List', Filter('testRaw Materials List',Customer.Value = cbCustFilter.Selected.Value)),TxtIngFilter.Text,"Title"),"Title",Ascending),
//Else filter the data based on the customer logged in
SortByColumns(Search(If(IsBlank(cbCustFilter.Selected.Value),'testRaw Materials List', Filter('testRaw Materials List',Customer.Value = cbCustFilter.Selected.Value)),TxtIngFilter.Text,"Title"),"Title",Ascending)

If we can filter by ingredient name and customer, this is all we will need, and we don't need the sort toggle. We will only have 3-4 customers and only about 20 ingredients per customer.

Thanks everyone for your ideas.

GraemeNZ